EDIT: new version of stored procedure is located here. Use the new version for 2005/2008. Use the below version for 2000.
I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases. In this version, I fixed two bug and added a feature. Here are the changes:
- Fixed "2000 backupset" bug by qualifying the object to msdb.dbo.backupset
- Fixed the full backup check for SIMPLE recovery model databases (bug found by Eugene from PerfSpot.com)
- Added Red Gate SQL Backup functionality
If you have scheduled jobs that are using any of my previous versions, you might need to modify the job step so that it uses the new @bkpSwType input parameter rather than the @liteSpeed parameter.
You can download the revised stored procedure here.
In the download, I have provided a script, ModifyJobStep.sql, to update the job step by replacing @liteSpeed with @bkpSwType. This only needs to be run if the job step includes @liteSpeed. You may not have this if you were using native backups since "@liteSpeed = N" was the default. If the script fails, run it one more time. There is some sort of SQL Server bug that is encountered on some systems, but it doesn't appear if you run it a second time. It is not a bug with the script though.
Let me know if you encounter any issues with this version so that I can get them fixed.
In the next version, I will add support for SQL Server 2008. It is a rather quick modification, but I need to do some additional testing of it before I release it to the Internet. If you'd like the next version before I finish my testing, just send me an email.