Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cleveland DBA

Colleen Morrow is a SQL Server Consultant at UpSearch, a provider of DBA resources in northeastern Ohio. She has worked in the IT industry for 20 years, doing everything from technical support to development to database administration on Informix, Oracle, and SQL Server platforms.

RYO Maintenance Plan – Database Backups

Remember kids...

Anyone who knows me knows that I’m a big proponent of automating routine tasks. If I have to do something more than twice, it should be scripted (if not fully automated). I’ve also made it fairly clear that I’m no fan of SSIS. So with all that in mind, it should be no surprise when I say: I hate maintenance plans. Every time I set up a new SQL Server instance, this is the part I can’t stand. Point and click. Always the same, and yet not scripted. And they’re not very robust either. Try backing up a large database to multiple files using the Backup Database task. Can’t do it. It’s enough to drive a girl to drink.

And it should wash my car…

So to satisfy my need to automate everything, I set out to fully script our typical database maintenance routine. And since it’s the number one priority of any maintenance plan, I started with backups. I came up with a set of specs I wanted the solution to meet:

  1. It will need a minimum of tweaking to implement and maintain.
  2. It needs to be able to handle a single or multiple backup directories.
  3. It will need to create sub-directories for each database as needed, just as a normal maintenance plan would.
  4. It will handle both user and system databases.
  5. It will backup one or many databases, as dictated at run-time.
  6. It will handle full, differential, and transaction log backups.

Like any good little DBA (or developer, for that matter), the first thing I did was a Google search to see if someone else had already done my work for me. Now, I can already hear you hollering “Ola! Ola!!”, and trust me, I looked to Ola Hallengren’s solution first. But, please correct me if I’m wrong, even Ola’s solution won’t handle backing up a database to multiple files in multiple locations. And that was the biggest shortcoming of all of the scripts I found out there, not just Ola’s. Was that one little requirement so obscure?  In the end, I decided I’d just have to do it myself. (I say that like it’s some kind of burden. Truthfully, I love this stuff.)

Make it low maintenance

The first thing I needed to do was decide how to handle requirement #1. I didn’t want to have to modify the code every time I implemented this on a different server. And what if I add an additional backup location to an existing server? No, the backup location(s) needed to be separate from the code. So I created a simple table to hold them.

USE [master]
GO
CREATE SCHEMA maint
GO
CREATE TABLE [maint].[BackupPaths](
	[PathID] [tinyint] NOT NULL,
	[BackupPath] [varchar](128) NOT NULL,
 CONSTRAINT [PK_BackupPaths_PathID] PRIMARY KEY CLUSTERED ([PathID] ASC)
)
GO
INSERT INTO maint.BackupPaths VALUES (1, 'D:\Backup1'), (2, 'D:\Backup2');
GO

Creating subfolders

Now that I had my backup location(s) in a central place, I could start on the backup procedures themselves. The first thing my backup process would need to do would be to create a subdirectory off of each of these locations for the backup files. Technically, I could have just dumped all the files in one folder, but personally I like things organized (Yes, I *do* have file folders that contain a single sheet of paper. So?). I’ll be using a couple of undocumented stored procedures to accomplish this.

xp_subdirs will give you a list of all of the subfolders inside the folder specified in the input parameter. So, for example, if I wanted to know what folders already exist under D:\Backup1, I would use

EXEC master.sys.xp_subdirs 'D:\Backup1' 

Using this output I can determine which folders already exist and which ones I’ll need to create for my backup files. That’s where the next undocumented procedure comes into play.

xp_create_subdir will create the folder specified in the parameter passed in. If I wanted to create a DDL_Audit folder under d:\Backup1, I would execute

EXEC master.sys.xp_create_subdir 'D:\Backup1\DDL_Audit'
GO
EXEC master.sys.xp_subdirs 'D:\Backup1' 

Alright, we’ve got our tools, now it’s time to get into the procedure itself. Rather than incorporate the subfolder creation logic into the backup procedure, I opted to break it out into its own stored procedure, just in case I ever needed to call it from yet another procedure. My procedure will take one parameter, the name of the database(s) being backed up. I’m using LIKE logic within the procedure so I can allow the use of wildcard characters. The default value, ‘%’ will create a subdirectory for each database on my instance.

CREATE PROCEDURE [maint].[sp_create_backupdir] (@dbname VARCHAR(128) = '%')
AS
BEGIN
DECLARE

		 @path VARCHAR(256) -- path for backup file
		,@subdir VARCHAR(256) -- path for backup files

	DECLARE @DirTree TABLE (rootpath varchar(128), subdirectory nvarchar(255)) 

The first variable I declare, @path, will store the paths I’ve defined in the BackupPaths table. @subdir will hold the complete subdirectory path, the one to be created. And the table variable, @DirTree, will be used to determine what paths already exist. With that done, the next order of business is to find out what folders already exist in the backup paths.

 		DECLARE paths CURSOR FOR SELECT BackupPath FROM maint.BackupPaths
		OPEN paths
		FETCH paths INTO @path
		WHILE @@FETCH_STATUS = 0
		BEGIN

			INSERT INTO @DirTree(subdirectory)
				  EXEC master.sys.xp_subdirs @path
			UPDATE @DirTree SET rootpath = @path WHERE rootpath IS NULL

			FETCH paths INTO @path

		END
		CLOSE paths
		DEALLOCATE paths 

This part of my procedure loops through all of my BackupPath records and for each one executes xp_subdirs to get a list of existing folders. These folders are inserted into @DirTree and then the rootpath of @DirTree is updated with the current BackupPath value. So at the end of this cursor run, the data in @DirTree will look like this

The next step in the procedure will use the @DirTree table to create those folders that don’t exist.

 		DECLARE paths2 CURSOR FOR
			SELECT b.BackupPath+'\'+d.name
			FROM sys.databases d
			CROSS JOIN maint.BackupPaths b
			WHERE d.name <> 'tempdb'
			AND d.state = 0
			AND d.name like @dbname
			AND NOT EXISTS (SELECT 1 FROM @DirTree dt WHERE dt.rootpath = b.BackupPath AND dt.subdirectory = d.name)
		OPEN paths2
		FETCH paths2 into @subdir

		WHILE @@FETCH_STATUS = 0
		BEGIN
			--PRINT 'CREATING SUBDIR '+@subdir
			EXEC master.dbo.xp_create_subdir @subdir
			FETCH paths2 INTO @subdir
		END

		CLOSE paths2
		DEALLOCATE paths2 

Here I’m doing a cross join to get a record for every database (based on my input parameter) and every BackupPath where that combination doesn’t already exist in @DirTree. And for each of those records, I create the directory using xp_create_subdir.

The last thing I do is toss in some error handling and we have the final procedure:

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [maint].[sp_create_backupdir] (@dbname VARCHAR(128) = '%')
AS
BEGIN

	DECLARE

		 @path VARCHAR(256) -- path for backup file
		,@subdir VARCHAR(256) -- path for backup files

	DECLARE @DirTree TABLE (rootpath varchar(128), subdirectory nvarchar(255))

	BEGIN TRY

		SET NOCOUNT ON

		DECLARE paths CURSOR FOR SELECT BackupPath FROM maint.BackupPaths
		OPEN paths
		FETCH paths INTO @path
		WHILE @@FETCH_STATUS = 0
		BEGIN

			INSERT INTO @DirTree(subdirectory)
				  EXEC master.sys.xp_subdirs @path
			UPDATE @DirTree SET rootpath = @path WHERE rootpath IS NULL

			FETCH paths INTO @path

		END
		CLOSE paths
		DEALLOCATE paths

		DECLARE paths2 CURSOR FOR
			SELECT b.BackupPath+'\'+d.name
			FROM sys.databases d
			CROSS JOIN maint.BackupPaths b
			WHERE d.name <> 'tempdb'
			AND d.state = 0
			AND d.name like @dbname
			AND NOT EXISTS (SELECT 1 FROM @DirTree dt WHERE dt.rootpath = b.BackupPath AND dt.subdirectory = d.name)
		OPEN paths2
		FETCH paths2 into @subdir

		WHILE @@FETCH_STATUS = 0
		BEGIN
			--PRINT 'CREATING SUBDIR '+@subdir
			EXEC master.dbo.xp_create_subdir @subdir
			FETCH paths2 INTO @subdir
		END

		CLOSE paths2
		DEALLOCATE paths2

	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 

Next up: Backing up the databases

And with that the filesystem is prepped for backup files. I’ll cover that procedure next time, so stay tuned. Same Bat-time, same Bat-channel…

Comments

Leave a comment on the original post [colleenmorrow.com, opens in a new window]

Loading comments...