I am using Tara’s excellent MsSQL Backup script to backup my SQL databases. This is an excellent way to backup SQL DB’s if you have an SQL Express version installed. Heck, I even use this when higher SQL versions are installed (with maintenance plan possibilities and stuff).
On top of that, I needed a script which was able to:
– Move my MsSQL BAK files from a local disk to a network path. This is because the backup script does not allow to backup to UNC paths.
– Clean up older backups on the destination directory.
I found (actually, I already knew) that Robocopy was a very nifty tool to do things like this.
My Howto:
Prepare your destination (backup) drive on your network by creating 2 ‘dummy dir’s:
- 1 actual backup destination folder on your remote fileserver (eg \\bck_svr\SQL_Backup)
- 1 directory to move your older files too ( eg \\bck_svr\delete)
- 1 always directory that acts as a ‘delete mirror’ ( eg \\bck_svr\EMPTY)
After that, just download and install the Resource Kit Tools, and create a BAT script that looks like:
c: cd "C:\Program Files (x86)\Windows Resource Kits\Tools" REM Move files older than 4 days to a Delete dir, but create them as empty files which fastens everything up (and also directly clears the diskspace :)) robocopy.exe \\bck_svr\\SQLBackup \\bck_svr\Delete /e /CREATE /MOVE /MINAGE:4 REM Delete the contents of the 'Delete' dir. robocopy.exe \\bck_svr\EMPTY \\bck_svr\Delete /MIR /E REM Make a new copy of the Dirictoy to backup. robocopy "d:\sql_backup" \\bck_svr\SQL_Backup /mov /s