Is there a nice way to have SQL Server 2005 run nightly backups that are automatically zipped with only the last n backups kept?
I know the agent can perform nightly backups, but I think the tricky part of this question would be the zipping up and removing very old backups
Cheers!
After the SQL backup we run another task to run a batch file to zip the files and copy these onto a NAS. We keep the last 3 backups - the basic script looks something like:
d:
cd \MSSQL\BACKUP
zip -rq d:\northwind northwind.BAK
REM - do the local copy to the NAS
move/y \\10.0.0.1\sql_backup\yesterday\northwind.zip \\10.0.0.1\sql_backup\2DaysAgo\northwind.zip
move/y \\10.0.0.1\sql_backup\northwind.zip \\10.0.0.1\sql_backup\yesterday\northwind.zip
copy northwind.zip \\10.0.0.1\sql_backup\northwind.zip
(I've edited the script to only show 1 database). At the end of the script we call a Python script to send an email that tells us how big the backup files were and date they were created. You could easily use a WSH script instead. I think that the "if errorlevel..." instruction might have been added because the SQL agent doesn't always detect if the batch file completed or not?
if errorlevel 1 goto weakspace
d:\scripts\send_email_nas_copy_success.py
goto end
weakspace:
REM - error occured
d:\scripts\send_email_nas_copy_fail.py
end:
exit
One of our servers uses winrar instead of zip to compress the files (it has a command prompt). Once a month last night's backups are copied into an archive that's kept for six months, but you could easily create another batch file to run once a week to maintain backups of last Sunday, 2 Sundays ago and 3 Sundays ago.
I wrote automssqlbackup which creates backups, rotates, zips, and sends email. Powershell and free.
There is a software: SqlBackupAndFTP. There is a free and a "Premium" version.
The free version includes the "Schedule Backup, Zip" option. Here is a review on a SQL Authority.com blog, which also explains how to use the tool.
Alternatively you could call a command line zip (winzip/wzzip) from the SQL Server extended stored procedure xp_cmdshell. You could use robocopy the resulting files to a safe place.
