Automate XCOPY of Database Data & Log Files
/*
In a New Query Window, switch to Text Mode (Results to Text)
XCOPY command examples, options, switches: https://www.lifewire.com/xcopy-command-2618103
*/
Step 1: Generate XCOPY Select for all Databases
Note: Add filtering on specific databases if required
Declare @SourcePath sysname = 'Server.Domain.com'
Declare @Output varchar(1000)
Set @Output =
'Select + ''--'' + UPPER(d.name) + CHAR(13) + ''XCOPY '' + ''"'' + ''\\' + @SourcePath + '\$\''' + ' + mf.physical_name + ''" '' + mf.physical_name + '' /s'' + CHAR(13)' + '
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
ORDER BY d.name'
Select @Output
Step 2: Execute T-SQL to extract the XCOPY code
Select + '--' + UPPER(d.name) + CHAR(13) + 'XCOPY ' + '"' + '\\Server.Domain.com\$\' + mf.physical_name + '" ' + mf.physical_name + ' /s' + CHAR(13)
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
ORDER BY d.name
Step 3: Place XCOPY code in a batch file and output to a Log File
--SALES
XCOPY "\\Server.Domain.com\$\G:\Data\sales.mdf" G:\Data\sales.mdf /s
--SALES
XCOPY "\\Server.Domain.com\$\G:\Log\sales.ldf" G:\Log\sales.ldf /s
IF need to move Data and Log files to different drives:
Select 'XCOPY ' + '"' + '\\Server.Domain.com\' + mf.physical_name + '" "'
+
case mf.type when 0 then replace(LEFT(mf.physical_name,LEN(mf.physical_name) - charindex('\',reverse(mf.physical_name),1) + 1) ,'G:\','F:\')
when 1 then replace(LEFT(mf.physical_name,LEN(mf.physical_name) - charindex('\',reverse(mf.physical_name),1) + 1) ,'G:\','E:\')
else mf.physical_name end + '" /s' + CHAR(13)
FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id ORDER BY d.name
|
|
|
|
|
|