Backup SQL Server 2005 and 2008 Databases
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.
Legacy Comments
|
Adam Gojdas
2009-12-30 |
re: Backup SQL Server 2005 and 2008 Databases Just a thought: If you get rid of the GOTOs I think you might be able to just do the raiserror exactly where it is initially discovered. So in place of things like the following: IF @version NOT IN (9, 10) BEGIN SET @rc = 1 GOTO EXIT_ROUTINE END Just do this: IF @version NOT IN (9, 10) BEGIN RAISERROR('Version is not 2005 or 2008', 16, 1) END You can then get rid of the @rc since it really only will return 0 because when it isn't 0 the RAISERROR is always executed. Then you can eliminate the EXIT_ROUTINE pretty much, except for leaving this part of it: IF @bkpFailure IS NOT NULL BEGIN SET @bkpFailure = 'The following database(s) failed to backup: ' + @bkpFailure + '.' RAISERROR (@bkpFailure, 16, 1) END RETURN 0 |
|
Giammarco Schisani
2010-01-02 |
re: Backup SQL Server 2005 and 2008 Databases Hi Tara, thanks for the script. Are these the warnings you are talking about? Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.xp_backup_database'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists. Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.sqlbackup'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists. Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.sqlbackup'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists. Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'master.dbo.sqlbackup'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists. Thank you. |
|
Tara
2010-01-11 |
re: Backup SQL Server 2005 and 2008 Databases @Adam - Thank you for the suggestion. The reason I use the GOTOs is to keep the error list in one location. @Giammarco - Yes those are the warnings that should be ignored. |
|
Lars-UT
2010-02-16 |
re: Backup SQL Server 2005 and 2008 Databases Hi Tara, I love this script! One clarification: Shouldn't the DIR @cmd being built when determining files to delete that are past the retention time append /A-A to exclude files that have already been backed up? (i.e. the Archive bit has been cleared) /AA does the opposite unless I'm mistaken. The documentation in the stored procedure seems off as well, as the archive bit is disabled or cleared when files have been backed up. That's how it works with TSM's (RESETARCHIVEATTRIBUTE YES ) option. -- @archiveBit -- 0 - ignore the archive bit of the files -- 1 - archive bit must be enabled in order to delete the files past the retention period; -- archive bit gets enabled when files are backed up to tape |