Backup SQL Server 2005 and 2008 Databases

<< SQL Server 2005/Windows 2008 cluster issue when physical server names are in lower case | Home | What three events brought you here? >>

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.

Feedback

# 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

12/30/2009 10:27 PM | Adam Gojdas

# 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.

1/2/2010 1:32 PM | Giammarco Schisani

# 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.

1/11/2010 1:28 PM | Tara

# 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

2/16/2010 5:07 PM | Lars-UT

Comments have been closed on this topic.

Ramblings of a DBA

My Links

Advertisement

News

Archives

Post Categories

Work