Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Nice and simple - i like :) –  Pure.Krome Aug 19 '09 at 1:43
add comment

I wrote automssqlbackup which creates backups, rotates, zips, and sends email. Powershell and free.

share|improve this answer
    
Cool, thanks for sharing it. –  splattne Jun 30 '09 at 13:07
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.