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