Last time I dealt with the creation of subdirectories, in one or more root locations, to house my database backups. Today we’ll get into the meat of the database backup procedure itself. If you’ll recall, this procedure has to meet some specific criteria:
- It will need a minimum of tweaking to implement and maintain.
- It needs to be able to handle a single or multiple backup directories.
- It will need to create sub-directories for each database as needed, just as a normal maintenance plan would.
- It will handle both user and system databases.
- It will backup one or many databases, as dictated at run-time.
- It will handle full, differential, and transaction log backups.
Requirement 3 was met by the sp_create_backupdir procedure. With the help of that procedure and the BackupPaths table I created, today’s backup procedure will meet the rest. Without further ado, let’s jump right in.
Parameters and Variables
This new procedure, sp_backup_database, needs to be able to backup one database, many databases, even all of my databases. With that in mind, I’m using the same method for passing in the database name, the user can pass in a specific name or use wildcards to cover multiple databases. I’m using ‘%’ as the default, since more often than not I’ll be using this script to backup all of my databases.
My procedure also needs to be able to perform full, differential, and transaction log backups. I didn’t want to have a separate procedure for each type. So my second parameter defines the backup type, ‘F’ for full, ‘D’ for differential, and ‘L’ for log backups. The default is a full backup.
ALTER PROCEDURE [maint].[sp_backup_database] (@dbs VARCHAR(128) = '%', @backup_type char(1) = 'F') AS BEGIN DECLARE @path VARCHAR(256)-- path for backup files ,@pathnum tinyint-- the path ID for backup files ,@pathcount tinyint-- number of backup paths ,@subdir VARCHAR(256)-- path for backup files ,@fileName VARCHAR(256) -- filename for backup ,@fileDate CHAR(8)-- used for file name ,@fileTime char(4)-- used for file name ,@extension VARCHAR(10) -- used for file name ,@dbname varchar(128) ,@dbid smallint ,@sqlstmt nvarchar(max) ,@diff_dbid smallint-- we set this to 4 for diff backups to omit system databases ,@trn_rm tinyint-- we set this to 3 for transaction log backups to omit SIMPLE mode dbs
I want to take a moment and point out a couple of the key variables I’ve defined here. The first is @diff_dbid. It’s not possible to perform a differential backup on the master database. I also didn’t think it necessary to perform differentials on any of the other system databases. So if the backup type is ‘D’, I’ll set this variable to 4, which is the maximum database ID for system databases. Otherwise, I’ll set it to 0. I’ll then use this in my query to build my database list. Similarly, I can’t do a transaction log backup on databases in simple recovery mode. So if my backup type is ‘L’, I’ll set @trn_rm to 3, since the recovery model code for simple mode is 3 in sys.databases. For any other backup type, I’ll set this value to 4, which should cover all recovery model types. If this doesn’t make sense now, it will soon.
With that said, my next step is to initialize my variables.
-- Initialize our variables SELECT @fileDate = CONVERT(CHAR(8),GETDATE(),112) ,@fileTime = REPLACE(CONVERT(varchar(5), GETDATE(), 108) , ':', '') ,@extension = CASE@backup_type WHEN 'F' THEN '.bak' WHEN 'D' THEN '.diff' WHEN 'L' THEN '.trn' ELSE '.unknown' END ,@pathcount = COUNT(PathID) ,@diff_dbid = CASE @backup_type WHEN 'D' THEN 4 ELSE 0 END ,@trn_rm = CASE @backup_type WHEN 'L' THEN 3 ELSE 4 END FROM maint.BackupPaths
Building the backup statement
I’m ready to start building backup statements. I tried to avoid using cursors whenever possible, but here was another spot where I simply couldn’t find a way to avoid it. So I built a cursor for all online databases (state=0) other than tempdb, matching the name or wildcard I passed in. Here’s where those two variables also come into play, I’m only including databases with a database_id > @diff_dbid (4 to omit system databases, 0 for everything) and those with a recovery model < @trn_rm (3 to omit SIMPLE mode databases, 4 to include everything). But before I do any of that, I need to call my sp_create_backupdir so I have a place to put all my backup files.
-- Create the necessary subdirs in each backup path EXEC maint.sp_create_backupdir @dbs -- Build the backup statement(s) DECLARE getdbs CURSOR STATIC FOR SELECT d.database_id, d.name FROM sys.databases d WHERE d.name <> 'tempdb' AND d.state = 0 AND d.name LIKE @dbs AND d.database_id > @diff_dbid--no system dbs for diff backups AND d.recovery_model < @trn_rm--no simple mode dbs for log backups
Handling multiple files
Inside that getdbs cursor loop, I build my backup statements. Now remember, I need to accomodate backing up to multiple files, so within that outer cursor I’ll also need another, inner cursor to handle the DISK clause. As I loop through that inner cursor, I build the path and name of the backup file, including the date and time of the backup. To help me determine later (if a restore is needed, for example) whether this backup contains multiple files or not, I also append a “1of2″ notation to my files. If there’s only one file, it’s “1of1″.
When I first started developing this procedure, all databases were to be backed up the same way. Then I started thinking about master. Master is small enough, there’s no real need to split it out to multiple files. Master is also extremely critical. Did I want to take a chance on one of those files not being backed up to tape? No. So I incorporated a little extra logic so that all system databases are only backed up in a single file to the first backup path.
IF @backup_type IN ('F', 'D')
SET @sqlstmt = 'BACKUP DATABASE ['+@dbname+'] TO '
ELSE
SET @sqlstmt = 'BACKUP LOG ['+@dbname+'] TO '
DECLARE backupfiles CURSOR FOR
SELECT b.PathID,b.BackupPath
FROM sys.databases d
CROSS JOIN maint.BackupPaths b
WHERE d.name = @dbname
ORDER BY d.name, b.PathID
OPEN backupfiles
FETCH backupfiles INTO @pathnum, @subdir
-- build the file clause section
WHILE @@FETCH_STATUS = 0
BEGIN
SET @subdir = @subdir+'\'+@dbname
SET @fileName = @dbname+'_'+@fileDate+'_'+@fileTime+'_'
+CAST(@pathnum AS VARCHAR)+'of'
IF @dbid <= 4
SET @fileName = @fileName+CAST(@pathnum AS VARCHAR)+@extension
ELSE
SET @fileName = @fileName+CAST(@pathcount AS VARCHAR)+@extension
SET @sqlstmt = @sqlstmt + 'DISK = ''' +@subdir+'\'+@fileName+''''
IF @pathnum < @pathcount and @dbid > 4
SET @sqlstmt = @sqlstmt + ', '
IF @dbid <= 4
BREAK;
FETCH backupfiles INTO @pathnum, @subdir
END
CLOSE backupfiles
DEALLOCATE backupfiles
INIT and COMPRESSION
Finally, a few last options I wanted to set. First of all, I wanted to overwrite any existing backup with that name. That’s an option I always choose when doing backups. Secondly, if this is running on a SQL 2008 instance, I want to use backup compression. Chances are, if I’m backing up to multiple drives, these databases are pretty big to begin with, so why not same some space and time by compressing them? And the last thing I do is append the DIFFERENTIAL clause, if this is a differential backup.
-- Overwrite the backup file if it exists
SET @sqlstmt = @sqlstmt + ' WITH INIT'
-- Use backup compression in 2008+
IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS varchar),1, CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar))-1) > 9
SET @sqlstmt = @sqlstmt + ', COMPRESSION'
-- Is this a differential backup?
IF @backup_type = 'D'
SET @sqlstmt = @sqlstmt + ', DIFFERENTIAL'
EXEC (@sqlstmt)
--PRINT @sqlstmt
FETCH getdbs INTO @dbid, @dbname Final Procedure
As with the sp_create_backupdir procedure, my last step is to add in some error handling and I’m done. Here’s the final result.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [maint].[sp_backup_database] (@dbs VARCHAR(128) = '%', @backup_type char(1) = 'F')
AS
BEGIN
DECLARE
@path VARCHAR(256)-- path for backup files
,@pathnum tinyint-- the path ID for backup files
,@pathcount tinyint-- number of backup paths
,@subdir VARCHAR(256)-- path for backup files
,@fileName VARCHAR(256) -- filename for backup
,@fileDate CHAR(8)-- used for file name
,@fileTime char(4)-- used for file name
,@extension VARCHAR(10) -- used for file name
,@dbname varchar(128)
,@dbid smallint
,@sqlstmt nvarchar(max)
,@diff_dbid smallint-- we set this to 4 for diff backups to omit system databases
,@trn_rm tinyint-- we set this to 3 for transaction log backups to omit SIMPLE mode dbs
SET NOCOUNT ON
BEGIN TRY
IF @backup_type NOT IN ('F', 'D', 'L')
RAISERROR (
N'Invalid backup type. Please specify F (full), D (differential) or L (Log).'
,16
,1
);
-- Initialize our variables
SELECT @fileDate = CONVERT(CHAR(8),GETDATE(),112)
,@fileTime = REPLACE(CONVERT(varchar(5), GETDATE(), 108) , ':', '')
,@extension = CASE@backup_type
WHEN 'F' THEN '.bak'
WHEN 'D' THEN '.diff'
WHEN 'L' THEN '.trn'
ELSE '.unknown' END
,@pathcount = COUNT(PathID)
,@diff_dbid = CASE @backup_type WHEN 'D' THEN 4 ELSE 0 END
,@trn_rm = CASE @backup_type WHEN 'L' THEN 3 ELSE 4 END
FROM maint.BackupPaths
-- Create the necessary subdirs in each backup path
EXEC maint.sp_create_backupdir @dbs
-- Build the backup statement(s)
DECLARE getdbs CURSOR STATIC FOR
SELECT d.database_id, d.name FROM sys.databases d
WHERE d.name <> 'tempdb'
AND d.state = 0
AND d.name LIKE @dbs
AND d.database_id > @diff_dbid--no system dbs for diff backups
AND d.recovery_model < @trn_rm--no simple mode dbs for log backups
OPEN getdbs
FETCH getdbs INTO @dbid, @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @backup_type IN ('F', 'D')
SET @sqlstmt = 'BACKUP DATABASE ['+@dbname+'] TO '
ELSE
SET @sqlstmt = 'BACKUP LOG ['+@dbname+'] TO '
DECLARE backupfiles CURSOR FOR
SELECT b.PathID,b.BackupPath
FROM sys.databases d
CROSS JOIN maint.BackupPaths b
WHERE d.name = @dbname
ORDER BY d.name, b.PathID
OPEN backupfiles
FETCH backupfiles INTO @pathnum, @subdir
-- build the file clause section
WHILE @@FETCH_STATUS = 0
BEGIN
SET @subdir = @subdir+'\'+@dbname
SET @fileName = @dbname+'_'+@fileDate+'_'+@fileTime+'_'
+CAST(@pathnum AS VARCHAR)+'of'
IF @dbid <= 4
SET @fileName = @fileName+CAST(@pathnum AS VARCHAR)+@extension
ELSE
SET @fileName = @fileName+CAST(@pathcount AS VARCHAR)+@extension
SET @sqlstmt = @sqlstmt + 'DISK = ''' +@subdir+'\'+@fileName+''''
IF @pathnum < @pathcount and @dbid > 4
SET @sqlstmt = @sqlstmt + ', '
IF @dbid <= 4
BREAK;
FETCH backupfiles INTO @pathnum, @subdir
END
CLOSE backupfiles
DEALLOCATE backupfiles
-- Overwrite the backup file if it exists
SET @sqlstmt = @sqlstmt + ' WITH INIT'
-- Use backup compression in 2008+
IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS varchar),1, CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS varchar))-1) > 9
SET @sqlstmt = @sqlstmt + ', COMPRESSION'
-- Is this a differential backup?
IF @backup_type = 'D'
SET @sqlstmt = @sqlstmt + ', DIFFERENTIAL'
EXEC (@sqlstmt)
--PRINT @sqlstmt
FETCH getdbs INTO @dbid, @dbname
END
CLOSE getdbs
DEALLOCATE getdbs
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
GO With that, I have a backup procedure that meets all my requirements. The procedures themselves won’t require any modification based on the system, all I’ll need to do is add the proper location(s) to BackupPaths. It can handle one or more backup directories. It handles one or many databases, as well as system databases. And it does any type of backup.
Next step: Cleanup
Now, any good backup routine will also include some sort of cleanup. We’ll cover that next time.
