Backup SQL Server Databases

Tue Aug 19, 2008 by Tara Kizer in sql-server-database-administration

EDIT: There is a new version of this stored procedure.

I've made several changes to my backup stored procedure, isp_Backup. I fixed a few things and added some much needed features. Here are all of my changes:

Fixed the retention code so that only those files that pertain to the type of backup are deleted. I had previously decided that I wanted all files older than @retention to be deleted, however I've changed my mind due to requests from my blog readers.
Added support for database names with spaces, dashes, single quotes, and other annoying, special characters.
Added support for British date format.
Added feature for SQL Server 2005 to run a full backup if the transaction log chain is broken. This starts the chain again. If you run a transaction log backup on a database whose chain is broken, those transaction log backups are useless until a full backup is run. I am unable to locate the necessary information in SQL Server 2000. I've been told to locate the information in the msdb backup history tables, however no rows get put into these tables when the chain is broken.
Added feature to run a full backup when a differential or transaction log backup is attempted but no full backup has ever been run. This situation throws an error, so it's better to just run the full backup rather than having failed jobs.
Added feature to run a full backup when last full backup is older than the create date or restore date of the database. This is important on SQL Server 2000 instances where restores happen and a database with the same name existed before. In that situation, no full backup has ever been made on the restored database yet the msdb backup history tables might contain rows pertaining to it due to using the same name.

The last 3 changes were made to prevent job failures and for recoverability reasons.

I have not gotten a chance to add code to check if a database is being log shipped by SQL LiteSpeed. I haven't received any requests for this code nor do I have a situation like this where I work, so I've been avoiding this code change.

Let me know if you'd like me to add any other features or if you find a bug. After careful consideration of each, I'll make a decision whether or not to include them in the next version.

In the past I've embedded the code in my blog posts, however I'm now going to provide the script instead so that you get the formatted code.

To download the stored procedure, click here.

If you don't have SQL LiteSpeed installed, ignore the warnings in the output when creating this stored procedure. They are warnings and not errors. The stored procedure works with or without SQL LiteSpeed installed.

Legacy Comments


Joe Webb 2008-08-20	re: Backup SQL Server Databases Awesome. Thanks for sharing, Tara! Joe

David 2008-08-20	re: Backup SQL Server Databases Great script, Tara. This and the other scripts you have posted will be a big help for me. I did notice a change you might make if you think it is worth your while. I was using this script today on my local machine and noticed that it backed up my Report Server Temp Database, which has a different name than what was in your script. It looks like the RS installer concatenated the instance name on the end of it. The database name is ReportServer$SQL2005TempDB. My instance name is SQL2005. Since all of the production servers do not use named instances I can't check to see if this a default behavior. Thanks.

Tara 2008-08-20	re: Backup SQL Server Databases David, You make an excellent point. In my next version, I'm not going to exclude ReportServerTempdb database. You don't need backups of it but since the name isn't constant across servers, I'm going to to just let my script back it up. I don't want to add code to isp_Backup to check if each database is an RS tempdb database. That would get too complex. Hopefully the database is small enough that those extra files won't matter on anyone's file system.

Chris 2008-08-25	re: Backup SQL Server Databases Tara, I've been using your script happily for a long time now. Thanks for writing it! I just upgraded my development server to SQL Server 2008. The script exits with the error: Version is not 2000 or 2005 [SQLSTATE 42000] (Error 50000). The step failed. I figured SQL Server 2008 should be able to do everything that 2005 can do, so I just changed all the checks for @version = '9' to: @version in ('9','1') (because it's version 10, and as a single character I guess it's '1'). As I was cutting and pasting I realized I could have just forced @version to '9' if it's '1' and I wouldn't have had to change all those other lines but I was almost done by then :) Anyway, it works pretty well like this. Do you know any major disasters I could run into off-hand by doing what I did (changing your script to make it work in 2008)? -Chris

Jeroen 2008-09-11	re: Backup SQL Server Databases It's a great backup script, thanks for sharing it with us. We had a full-text catalog on one of the databases that became corrupt and due to that backups fail. Instead of skipping that faulty database the whole job stops with an error. Would it be possible to continue the script with the other databases and only report about the faulty one instead of stopping it completely? On the other hand it's not really an issue for us atm, cause you can simply fix the error and rerun the job to let it complete for the other databases

Tara 2008-09-11	re: Backup SQL Server Databases Jeroen, I don't think I can control that as it encountered a fatal error and therefore got disconnected. I could be wrong though. If anyone has a query that I check beforehand, I'd be happy to add it to a future version.

Jason 2008-09-22	re: Backup SQL Server Databases I am wondering if an option to change the folder layout would be helpful to others. As is, each backup for a given database is dropped into the sub-folder with the name of the database. The implication is that the backups from Sunday, Monday, Tuesday, etc. are in the same folder. I have a server where I need to zip up the backups and ftp them to another server. If each day adds to the collective, my zip files would get larger and larger. I think an option to insert a datestamp folder in-between would be a nice option. So, instead of Backups\db1 and Backups\db2...you can have Backups\yyyyMMdd\db1 an Backups\yyyyMMdd\db2. Then all you need to do is zip up the Backups\yyyyMMdd folder. (Of course I can work around this by setting retension to 1 day but I think the feature would be helpful anyways.)

Jason 2008-09-22	re: Backup SQL Server Databases Bah...retension should be retention...too bad we cannot edit...

Jason 2008-09-22	re: Backup SQL Server Databases Another feature may adding the ability to kick off the compression (zipping) of the backup as part of the isp_Backup stored proc. Since we know the exact name of the file this may not be too difficult.

jeff 2008-10-08	Great script!! Hey Tara, Just wanted to say thanks for the fantastic code! I've built some additional stored procedures that dynamically create SQL Agent Jobs based on all server databases, which in turn call your backup proc. Makes for a very robust backup code set! let me know if you've any interest in the agent job creation stuff. I'd be more then happy to share! Thanks again! jeff

yohen 2008-10-30	re: Backup SQL Server Databases its working.. superb tara... thank u .. love ur sharing knowledgle things....

ANDREW 2008-11-13	re: Backup SQL Server Databases and Link Server Hey Tara fantastic code! Please .... I need make a backup for a database, but this database are in another server, maybe can I use linkserver ???... but not found.... Thank´s for you answer... Andrew.