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…