Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

xcopy

Posted on 2013-01-20
6
Medium Priority
?
827 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
[X]
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
6 Comments
 
LVL 79

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 79

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 79

Accepted Solution

by:
arnold earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
YESTERDAY YESTERDAY.BAT is inspired by a previous article I wrote entitled: TOMORROW.BAT (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/MS_DOS/A_4196-Advanced-Batch-File-Programming-TOMORROW-BAT.html). The crux of this batch f…
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…
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…

670 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