Perform database backup using SQLCMD utility in SQL Server by serverku

I just went through the SQLCMD command line utility to perform database backup in SQL server where it does not have SQL agent. Below is the command which I placed in a batch file and schedule with windows task scheduler.

    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    

Above command perform a full database backup, but it does not quit from utility after performing it. So I have changed with slight change and placed -Q in place of -q for cmdline query. Otherwise next schedule will be skipped as of current schedule never come out from sqlcmd utility and keep running

    Sqlcmd -UUserName -PPassword -SServerName -dDatabaseName -Q"DECLARE @backupFilePath varchar(100); SET @backupFilePath = 'D:\BackupPath\DBName\'+'DBName_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') + '.bak'; Backup database DBName to disk = @backupFilePath "
    

This will perform backup and quit from the sqlcmd utility.

Archive old database backup files using forfiles.exe and FOR /F by serverku

Before so many posts published I posted to the old database backup files archive using TSQL and using SSIS. Hope you visit that post and you liked them. I am repeating the same thing here, but it will be with two different methods, the first one with FOR /F command which we saw last time to copy database backups to another location too, second one with forfiles.exe. Let us move on the first method.

FOR /F :  As we discussed earlier post, it used same to traverse rows from generated file from SQLCMD command. Here we do not have values with delims (,) and we have only one values to grab and assign in variables.

SQLCMD -Udba -Pdba@1234 -S"PARESH\MSSQLSERVER2012" -dmsdb -Q"set nocount on ; 
SELECT DISTINCT bmf.Physical_device_name 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(Day,-7,GETDATE()) " -o "C:\BackupFile.txt"

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

Please make sure SQLCMD and FOR /F should be in single line individualized. Running above code and captured snapshot as follows,

forfiles.exe : It is used to delete files in giving directories and subdirectories with a specified day or date. Syntax of it is following, Here /P is a directory, /m for search criteria, /S for subdirectories search, /d for days or date and /C command to fire. Please visit this site for more detail.

forfiles [/p <Path>] [/m <SearchMask>] [/s] [/c "<Command>"] [/d [{+|-}][{<Date>|<Days>}]]

Let us run it and capture snap again with the method,

Hope you have some other methods to archive old database backups. Request to share here!

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.