Solved

xcopy

Posted on 2013-01-20
6
781 Views
Last Modified: 2013-01-22
Hello Experts,

I'm looking for a function that copy "quick&dirty" MDF, LDF Files (SQL 2008 Server, no DB User is known, no SQL Dump is available).

I find "xcopy /EXCLUDE:<a_file_list_to_exclude.txt>" which works fine. So I can exclude SQL Datafiles like *.MDF and *.LDF to be copied which means that SQL Server must not be shutdown-ed.
Second if I use xcopy to copy the "rest" of the file there are, as expected, many "share errors because SQL Server is Online and handles MDF/LDF file access.

I googled a similar solution where the author works with

for /f "delims=" %%f in ('dir /a-d /b /s "%dSource%\%fType%"') do (
    copy /V "%%f" "%dTarget%\" 2>nul

but that don't works with "xcopy" because xcopy must be used to create subfolders while copy progress which "copy" can't. If there are two similar filename.extension in different directories, the last file would be overwritten in the destination directory.

Summary (what I want to do)
1. xcopy all Files exclude SQL Server Files
2. Stop SQL Server
3. Copy only the in 1. not copied MDF and LDF Files
4. Start SQL Server

The Solution should be realized in Windows - Bat - CMD manner because there are legacy Windows 2003 Server without the chance to deploy i.g. Powershell

THX++
reredok
0
Comment
Question by:reredok
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 38799367
An alternative to xcopy is to use robocopy source destination /xf *.mdf /xf *.ldf /copy:dato
http://technet.microsoft.com/en-us/library/cc733145(v=ws.10).aspx
It is part of windows 2008.
On earlier version it is part of the support tools/resource kit has additional tools for earlier versions.
0
 
LVL 4

Author Comment

by:reredok
ID: 38800793
as I mentioned the Problem is that copy don't create folder 'n' subfolder while copying files.

the "googled" code was great but dosn't work with xcopy
 
[.....
set dSource=C:\Main directory\sub directory
set dTarget=D:\Documents
set fType=*.doc
for /f "delims=" %%f in ('dir /a-d /b /s "%dSource%\%fType%"') do (
    copy /V "%%f" "%dTarget%\" 2>nul
)
...]
0
 
LVL 77

Expert Comment

by:arnold
ID: 38801580
Robocopy using the /E flag will create all subfolders even empty ones.


Not sure why you do not use ssms/e.

There are powershell commandlets that can be used to trigger a backup of mssql.
Are you dealing with an ms I internal database? Using ssms you can access it using named pipes format only \\.\mssql\instancename.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Author Comment

by:reredok
ID: 38805658
The installed SQL 2008 R2 was a third party customized version without ssms.
There is only a "Manager" which can attach or de-attach, sql-dump (a proprietary format)  sql db's;
a database or db user password is not known.

So I can only "net start/stop" Services and use the bulitin cmd's to copy/backup the files.
0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 38806396
SSMSE can be downloaded.

Backing up the raw files (MDB and LDF is not a recommended approach) you should test it before trying to implement it.
i.e. stop the service, copy the files. start the service.
Then take the two files and attach them on a separate server.  The issue you will run into will deal with any users defined with security on the databases might not be present.

I think there was powershell/vbscript that is used by individuals with sql server express versions that will perform the backup within EE..

http://www.mssqltips.com/sqlservertip/1862/backup-sql-server-databases-with-a-windows-powershell-script/

http://technet.microsoft.com/en-us/library/cc281847%28v=sql.105%29.aspx

This way you would need to test and see whether the backup can occur while the sql service is operational.
One other thing you may want to do as part of this script is copy the SQL logons so that they can be recreated if needed. to maintain the database's security settings i.e. usera has read writes. userb has full write rights, etc.
0
 
LVL 4

Author Comment

by:reredok
ID: 38807773
net stop MSSQL$DATEV_DBENGINE /y

rem ping 127.0.0.1 -n 900 >nul

set dSource=d:\windvsw1\datev\daten
set dTarget=F:\backup
set fType=*.mdf

for /f "delims=" %%i in ('dir /a-d /b /s "%dSource%\%fType%"') do (
     xcopy "%%i" "%dTarget%\%%~pnxi*" /Y
)

set fType=*.ldf
for /f "delims=" %%i in ('dir /a-d /b /s "%dSource%\%fType%"') do (
     xcopy "%%i" "%dTarget%\%%~pnxi*" /Y
)

net start "MSSQL$DATEV_DBENGINE"
net start "SQLAgent$DATEV_DBENGINE"
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many times while working on a computer regardless of any Operating System, lag and crashes seem to creep in, hindering your working speed. Sometimes, it can also cause your work to be lost unexpectedly and as a result, you are unable to meet your de…
This is a little timesaver I have been using for setting up Microsoft Small Business Server (SBS) in the simplest possible way. It may not be appropriate for every customer. However, when you get a situation where the person who owns the server is i…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now