Sign up ×
Server Fault is a question and answer site for 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


share|improve this question

3 Answers 3

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:

zip -rq d:\northwind northwind.BAK
REM - do the local copy to the NAS
move/y \\\sql_backup\yesterday\ \\\sql_backup\2DaysAgo\
move/y \\\sql_backup\ \\\sql_backup\yesterday\
copy \\\sql_backup\

(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
goto end
REM - error occured

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

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

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 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

Your Answer


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.