Solved

xcopy

Posted on 2013-01-20
6
787 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

TOMORROW TOMORROW.BAT is inspired by a question I get asked over and over again; that is, "How can I use batch file commands to obtain tomorrow's date?" The crux of this batch file revolves around the XCOPY command - a technique I discovered w…
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…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
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…

828 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