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
  • 3
  • 3
LVL 77

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 77

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.
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


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 77

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to have excel show file name on the title bar 4 48
CMD shell elevation.js 4 64
Windows Server 2012 Data Center Edition 11 109
file size 9 51
This article describes how to set permissions to allow a limited-permissions user to start and stop a particular System Service.   It is always best to give users only the permissions that they need to perform their job, so tweaking particular permi…
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
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…

770 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