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.
Source Click Here.
Post a Comment