I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server 2005 and 2008 databases. This new version includes one new feature, which I think is critical to SQL instances with multiple databases on them.
In the old version, when a backup failure was encountered such as due to lack of free disk space, the stored procedure would end without proceeding with the other databases. Since the stored procedure backs up databases alphabetically, you could end up with many missed backups. To be more clear, let’s say we have a SQL instance with three databases on it: ABC, JKL, and XYZ. If we encounter a backup failure on ABC, JKL and XYZ will not be backed up until ABC finally succeeds at a later run.
In the new version, I use Try/Catch logic when performing the backups. When a backup failure is encountered, I capture the database name into an “array” and then proceed with the next backup. At the end, I raise an error (RAISERROR) if there were any backup failures and in that error show which databases failed to backup. By using the RAISERROR statement, our SQL job will still fail so that we alert the DBA to a failed backup.
You can download the new version here.