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!