Copy database backup files using SQLCMD and FOR /F commands by serverku

Recently we have done with the conversion of the same topic to copy database backup files to an external drive using XCOPY only. That script copy database backup files created on current day from source to destination, So we can schedule that code to run one time only per day to avoid duplicate file copy, otherwise duplicate files may copy for further run.So I used another alternative solution which we can schedule recursive and no chance for duplicate file copy, So I am sharing the same here.

In this method I will use backupset and backupmediafamily system tables from msdb database to get database backup details for a particular period and and process them for a copy.

SQLCMD -Udba -Pdba@1234 -S"PARESH\MSSQLSERVER2012" -dmsdb 
-Q"set nocount on ; 
SELECT DISTINCT bmf.Physical_device_name +','+'\\ExternalDrivePath\DBBackup\'+
HOST_NAME()+'\'+ bs.Database_name + '\' +
 CASE WHEN BS.TYPE = 'D' then 'FULL' WHEN BS.TYPE = 'I' then 'DIFF' else 'TRN' End + '\'  as BackupFiles
FROM  msdb.dbo.backupset (nolock) bs 
INNER JOIN msdb.dbo.backupmediafamily (nolock) bmf 
on (bs.media_set_id = bmf.media_set_id) 
WHERE bs.backup_finish_date > DATEADD(HOUR,-6,GETDATE()) "
 -o "D:\Batchfiles\BackupFile.txt"

FOR /F "tokens=1,2 skip=2 delims=," %%G IN (C:\DatabaseBackup\BackupFile.txt) DO xcopy /Y %%G %%H

I have created above code in the batch, Please make sure SQLCMD and FOR /F should be in single line individualized.

SQLCMD will generate a text file having source full file path and destination path with comma separated. Destination path I made a dynamically with ServerName + DatabaseName + Backup Type for pattern. If the destination path doesn't exist, then it will be created by a process.

FOR /F will traverse each row in the generated text file and grab the values to process for copy. The options which I used are tokens to read first and second columns separated by comma, skip option to skip first two lines (skip header and line), delims (here comma) to separate values and variables to grab values. XCOPY used to copy files from source to destination. XCOPY will create the destination path if not exists dynamically, so I used XCOPY instead of the COPY command.

Let's run the batch file and checking for database backups created taken in last 6 hours, review generated text files and copy process,
Text file (BackupFile.txt)

BackupFiles
----------------------------------------------------------------------------------------------------------------
C:\DatabaseBackup\DemoDB\Full\DemoDB_20121019_234500.bak,\\ExternalDrivePath\DBBackup\PARESH\DemoDB\FULL\
C:\DatabaseBackup\SampleDB\Full\SampleDB_20121019_133000.bak,\\ExternalDrivePath\DBBackup\PARESH\SampleDB\FULL\
C:\DatabaseBackup\DemoDB\Trn\DemoDB_20121019_051500.trn,\\ExternalDrivePath\DBBackup\PARESH\DemoDB\TRN\

The above file is generated by the SQLCMD command with backup details which need to process through FOR /F command as a further step after it immediately. Which will skip first two lines and process rows one by one till the end of file, split values with comma, assign them to source and destination variables and process them for a copy.

This routine copy database all backup files created last in 6 hours, so we can schedule this routine to every 6 hours every day. I shared two methods here, first was in an earlier post and second on today. Let me know if we have another method. We can use xp_cmdshell command in SQL server for the same, we should avoid it for security.

Copy database backup files using XCOPY command by serverku

Recently, when I was working database backups copy to external drives or whatever destination, I tried it with new solution using XCOPY command. Before that solution I was using XP_CMDSHELL command in SQL Server to copy database backup file to an external drive, also we should avoid using XP_CMDSHELL for security concern. So out of SQL server script\query and another tool, the solution came with XCOPY command which copy latest files or as per date specified, so later database backup files or the files created on or after the date specified as input. You can visit XCOPY command here.
Lets first share me the command which I am using to copy latest database backup files. Here I am using some variables in the code, but let’s share me script first, then I will explain the variables.

SET dwMONTH=%DATE:~4,2%
SET dwDAY=%DATE:~7,2%
SET dwYEAR=%DATE:~10,4%
SET dwDate=%dwMONTH%-%dwDAY%-%dwYEAR%

SET source=C:\DatabaseBackup
SET destination=\\externaldrive\DatabaseBackup\
SET extension=*.bak

XCOPY /Y %source%\%extension% %destination% /s /i /D:%dwDate% 

You can see batch file created with the above code and the variables used inside and how they are integrated with final XCOPY command. After getting to run it will copy database backup files to an external drive or whatever destination using source, Destination, file extension (.bak\.trn here) and the date on or after the backup files created. Let’s implement and run it..
This code in batch files, copy all the files created Today with specified file type from source folders including sub folders to destination. So lets run batch and see what’s inside. Let's try with sample example and local drives,

Apart from XP_CMDSHELL command using in SQL Server, which methods you are using the database backup files to external drives? Please share here. I will post a further topic with a different method for the same. Hope you enjoyed it.