Backup SQL Server 2005 and 2008 Databases

Tue Sep 8, 2009 by Tara Kizer in sql-server-database-administration

I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases. This new version includes one bug fix and three new features:

Bug Fix – removed ReportServerTempdb from exclusion list
Bug Fix – fixed file retention code to handle database names with spaces
Feature – support for SQL Server 2008 including compression
Feature – archive bit option
Feature – COPY_ONLY option

I decided that excluding the ReportServerTempdb from the backups was unnecessary. This database, used by Reporting Services, is in FULL recovery model by default, so this exclusion could have caused you to run out of space in its transaction log.

Bryan Conlon, one of my blog readers, noticed that the file retention code was not working when a database name had spaces in it. It took me a while to come up with a solution for this one, but I came up with a solution that involves an additional temporary table that stores just the file names and then synchronizes with the original temporary table that handles the dates. If you are wondering why I don’t just use the FORFILES command along with the DEL command which probably wouldn’t have this bug, it is because you can’t use UNC paths with FORFILES. I didn’t want to limit storage of backups to local or mapped drives. To see a file retention solution that uses FORFILES with DEL, check out this blog.

The archive bit option was added in case you don’t want to delete files that haven’t been backed up to tape. If you aren’t backing up your files to tape, then do not use this feature unless you are setting this option to true on the files. The default value of this new input parameter is false, so you don’t need to do anything if you don’t want to use it.

The COPY_ONLY option was added due to a feature request from one of my blog readers, “The Baking DBA”. This option allows you to perform a backup without impacting the normal sequence of backups. For more details on this, please check out the COPY_ONLY option in the BACKUP DATABASE topic in SQL Server Books Online.

I also dropped support for SQL Server 2000, so this new version only supports SQL Server 2005 and SQL Server 2008. I decided to drop SQL Server 2000 support from this new version, and all future versions, as keeping it in there was making it harder to release new versions. If you still need SQL Server 2000 support, like I do on a legacy system, then do not deploy this new version to those systems, simply use the last version.

You can download the new version here.

Thank you to Bryan Conlon for also testing and finding bugs in the “beta” version of the stored procedure. All bugs that were found have been fixed. Thanks Bryan!

Legacy Comments


philhege 2009-10-05	re: Backup SQL Server 2005 and 2008 Databases Great stuff, Tara. When I get some time, I'll bake a version that accepts wildcard database names, which is useful for packaged apps that preface many of their databases with the same string.

Tara 2009-10-05	re: Backup SQL Server 2005 and 2008 Databases Philhege, IMO, that type of custom code should be done outside of isp_Backup. It should be done when you call the object, so for instance in the job step. Just repeatedly call isp_Backup for whatever wildcard string you want.

Jeff 2009-11-17	re: Backup SQL Server 2005 and 2008 Databases Great work Tara. A couple of questions, if you please: Is there a way to automate this to run at a specific time or would that be better done from a .Net program? Do all connections to the database need to be closed before the backup is started?

Tara 2009-11-17	re: Backup SQL Server 2005 and 2008 Databases Jeff, We automate our SQL backups via SQL jobs. I would not recommend doing it from a .NET program as SQL jobs are there for this type of task. SQL backups are an online operation so no one has to be disconnected during the backup. In fact, that would be a major flaw in the engine if that were the case. I've got backups that take 2-3 hours on some systems and the service level agreement that we have with our customers states the database has to be available 24x7 99.999% of the time.