UNDERCOVER TOOLBOX: Checking Your Backup Paths Exist and Automatically Creating Them if they Don’t

Where do you send your backup files?  Here at SQLUndercover, we like all our databases to have their own backup folder, it just makes it nice and easy to find things.

But what happens if someone creates a new database but doesn’t create a backup folder for that database?  Unless you’re doing some kind of check on the file path, the backup is likely to try writing the file to a path that doesn’t exist and will fail.

If you don’t care about the explanations and just want to get to the meat then scroll down the page for the script.

So what can we do about this?

UNDOCUMENTED PROCEDURE WARNING: We’re heading into the badlands of undocumented procedures, folks.  Being undocumented means that Microsoft can mess around with them whenever they like so be a little careful of using them in production.  

There are a couple of undocumented procedures that can help us here.

xp_fileexist

The first thing that we’re going to need to do is check that SQL Server can actually see the path that we want to write too.  To do that, we’re going to enlist the help of xp_fileexist.  Even though the proc is called, xp_fileexist it can also check that a path and even if the parent directory exists.

Lets see it in action…

We’ll have a go at passing in a file that we know exists…

EXEC xp_fileexist 'O:\SQLBackups\SQLUndercover\SQLUndercover_12092017.bak'

And there we can see from the result set, the string we passed in is actually a file.

OK how about checking if a directory exists, that’s what we’re interested in afterall…

EXEC xp_fileexist 'O:\SQLBackups\SQLUndercover'

Well, that’s cool!  We can see that the path does actually exist.  Just for the sake of argument, lets just pass in a non-existent path…

EXEC xp_fileexist 'O:\SQLBackups\SQLUndercover2'

xp_create_subdir

So what do we do if the directory doesn’t exist?  In that case we can call on another friend of ours, xp_create_subdir and he does exactly what it says on the tin.

Lets now see this one in action.  As you can see below, the only folder that we’ve got in O:\SQLBackups is SQLUndercover…

Lets now run xp_create_subdir to create a SQLUndercover2 folder…

EXEC xp_create_subdir 'O:\SQLBackups\SQLUndercover2'

And there we have it, that’s how you can create a new directory using SQL Server.  The nice thing about that proc is that it doesn’t just create a single folder but can also create a whole hirachy.

The Script

So now that we’ve got to know xp_fileexist and xp_create_subdir we can get to the bit that you’ve all been waiting for, the script that’s going to check if our backup file path actually exists and if it doesn’t then it’ll create it for you.

The script will first check for the existence of the path fed into @Path.  If that path exists then I’ll report so but if that path doesn’t exist then it’ll attempt to create it.

It should be easy enough to alter the script and incorporate it into your own backup routines.

--variable to hold directory to check
DECLARE @Path VARCHAR(50) = 'O:\SQLUndercover\Backups'

IF OBJECT_ID('tempdb..#xp_fileexist_Results') IS NOT NULL DROP TABLE #xp_fileexist_Results

CREATE TABLE #xp_fileexist_Results (
File_Exists int,
File_is_a_Directory int,
Parent_Directory_Exists int
)

--check if directory exists
INSERT INTO #xp_fileexist_Results
(File_Exists, file_is_a_directory, parent_directory_exists)
EXEC Master.dbo.xp_fileexist @Path

IF EXISTS (SELECT 1 FROM #xp_fileexist_Results WHERE File_is_a_Directory = 1) --if exists PRINT 'Directory Exists'
	PRINT 'Directory Exists'
ELSE  --if directory doesn't exist, attempt to create it
BEGIN
	EXEC xp_create_subdir @FullPath

	--perform another existance check to make sure that the directory was actually created
	TRUNCATE TABLE #xp_fileexist_Results

	INSERT INTO #xp_fileexist_Results
	(File_Exists, file_is_a_directory, parent_directory_exists)
	EXEC Master.dbo.xp_fileexist @FullPath

	IF EXISTS (SELECT 1 FROM #xp_fileexist_Results WHERE File_is_a_Directory = 1) --if new directory exists PRINT 'Directory Created'
		PRINT 'Directory Created'
	ELSE
		PRINT 'Error Creating Folder' --if new directory doesn't exist then there must have been a problem creating it
END

Thanks for reading 🙂