Posted on 2013-01-20
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

Question by:reredok
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 79

Expert Comment

ID: 38799367
An alternative to xcopy is to use robocopy source destination /xf *.mdf /xf *.ldf /copy:dato
It is part of windows 2008.
On earlier version it is part of the support tools/resource kit has additional tools for earlier versions.

Author Comment

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
LVL 79

Expert Comment

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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

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.
LVL 79

Accepted Solution

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..

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.

Author Comment

ID: 38807773

rem ping -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 "SQLAgent$DATEV_DBENGINE"

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

630 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