Copy and archive SQL Server database backup
Though you are working as SQL Server DBA, you have to think, at least sometime, beyond relational. We can schedule different types of backup, ie: full, differential, transaction etc., from within SQL Server but what if you want to set archiving policy also? There are so many different ways to accomplish this task but I, personally, prefer to go for .BAT file which can be called from windows schedule task.
Suppose I want following policy (obviously one can change the policy given below as per their need).
- I want full/trn backup to be kept in same server where SQL Server is installed for one day
- Very next day that backup to be moved to network location
- Network location should keep backup for three days and older backups then three days should be removed
I already have maintenance plan running in SQL Server which keeps taking full backup everyday and transaction backup at every 15 minutes. I will handle copy/archive process from .BAT file and schedule this batch file to run at everyday 12:05AM to move previous days backup and remove older backup.
Here is the script I would have in my batch file:
Sqlcmd -E -S"RiteshPC\Dev2012" -dmsdb -Q"set nocount on ; SELECT DISTINCT bmf.Physical_device_name +’,’+’\\192.168.1.200\RiteshPCDev2012Backup\DatabaseName\’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,-24,GETDATE()) " -o "E:\Batchfiles\BackupFileDev.txt"
FOR /F "tokens=1,2 skip=2 delims=," %%G IN (E:\BatchFiles\BackupFileDev.txt) DO xcopy /Y %%G %%H
forfiles.exe /p E:\Database-Backup\Dev /s /m *.bak /d -1 /c "cmd /c del @file"
forfiles.exe /p E:\Database-Backup\Dev /s /m *.trn /d -1 /c "cmd /c del @file"
forfiles.exe /p \\192.168.1.200\RiteshPCDev2012Backup\DatabaseName /s /m *.bak /d -3 /c "cmd /c del @file"
forfiles.exe /p \\192.168.1.200\RiteshPCDev2012Backup\DatabaseName /s /m *.trn /d -3 /c "cmd /c del @file"
Herewith I want to list some points where you may need change while setting up batch file for your environment.
- “RiteshPC\Dev2012” is my SQL Server Instance name, you have to provide yours.
- I have used “-E” parameter in the first line of batch file as I want my SQL Server can be connected with windows authentication, if you want SQL Authentication, you have to provide SQL Server login/password which has permission to read system table in MSDB database
- I have used “DATEADD(HOUR,-24,GETDATE())” in SELECT query as I want to deal with all the backup comes in last 24 hours only.
- “\\192.168.1.200\RiteshPCDev2012Backup\DatabaseName\” is the network path where I want to put backup for three days.
- Select query ran in MSDB database will output results in text file at “E:\Batchfiles\BackupFileDev.txt”. That file will be read, copy the backup to network path.
- In first two “forfiles.exe” command, I am deleting .bak and .trn from the server which are older then 1 day (you can observe “-1” parameter value I gave after “/d” parameter)
- In 2nd and 3rd “ForFiles.exe” I ham deleting .bak and .trn files from the networks which are older then 3 days.
Do you have any other way to manage copy/archive of backup?
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.