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



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\northwind.zip \\\sql_backup\2DaysAgo\northwind.zip
move/y \\\sql_backup\northwind.zip \\\sql_backup\yesterday\northwind.zip
copy northwind.zip \\\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
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.

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

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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