Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



Automate XCOPY of Database Data & Log Files
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-04-13








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