Aug 24, 2011
tom

Automatically Zip/Manage SQL Server 2005 backups

Question

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!

Answer

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 MSSQLBACKUP
zip -rq d:northwind northwind.BAK
REM - do the local copy to the NAS
move/y \10.0.0.1sql_backupyesterdaynorthwind.zip \10.0.0.1sql_backup2DaysAgonorthwind.zip
move/y \10.0.0.1sql_backupnorthwind.zip \10.0.0.1sql_backupyesterdaynorthwind.zip
copy northwind.zip \10.0.0.1sql_backupnorthwind.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:scriptssend_email_nas_copy_success.py
goto end
weakspace:
REM - error occured
d:scriptssend_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.

Related posts:

  1. Is it possible to configure SQL Server 2005 to auto-compress backups?
  2. SQL Server 2005 backups for previous 5 days on disk?
  3. How to remove database backups 2 days or older through SQL Server 2005 Integration Service?
  4. SQL 2005 Diff backups failing – no full backup exists?
  5. SQL Server 2005: How reliable are live SQL Server backups?

Leave a comment