Dynamically Create a Folder for Each Database

If you're a DBA like me then you're probably pretty detail-oriented and like to keep things very organized.  For example, when doing database backups, I like to have a specific folder structure to hold the backup files for each database.  See below:

D:\Backup\Server\Instance\MyDb1
D:\Backup\Server\Instance\MyDb2
D:\Backup\Server\Instance\MyDb3

In order to make this happen automatically, my TSQL backup jobs need to have code that will create folders when a new database is added or when the jobs are initially setup.  SQL offers an undocumented extended stored procedures that allow us to create them on the fly; master.sys.xp_create_subdir.

Master.sys.xp_create_subdir will create whatever folder listing you pass it.  For example, if you pass it 'D:\Backup\Server\Instance\MyDb1', it will create each of those folders.  If any of those folders already exist, then it will ignore it and move on to the next one.

So if we need to create a folder for each database before running our backup job, then we can execute a block of code such as this.

DECLARE

       @BaseFolder nvarchar(128)

      ,@DatabaseFolder nvarchar(256)

      ,@DbName sysname

     

-- Only one input parameter needed from the user.

SET @BaseFolder = 'D:\Backup';

DECLARE Db CURSOR FOR

SELECT name FROM sys.databases WHERE name <> 'tempdb';

OPEN Db;

FETCH NEXT FROM Db INTO @DbName;

WHILE @@FETCH_STATUS = 0

BEGIN

      -- Dynamically create a folder for each database.

      SET @DatabaseFolder = @BaseFolder + '\'

      + CONVERT(nvarchar,SERVERPROPERTY('MachineName')) + '\'

      + ISNULL(CONVERT(nvarchar,SERVERPROPERTY('InstanceName')),'DEFAULT')

      + '\' + @DbName;

      EXEC master.sys.xp_create_subdir @DatabaseFolder;

      FETCH NEXT FROM Db INTO @DbName;

END

-- Clean up

CLOSE Db;

DEALLOCATE Db;

GO

On our test server we have three user databases plus the usual system databases, so we end up with a folder structure like this.

This example creates folders for backups; however, the same code can be adapted for any number of uses, and you can even pass UNC paths (\\Server\Folder\) so you can create network folders as well.