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

Everyday SQL

Patrick Keisler is a MCTS and MCP. For over 12 years, he has been been a database administrator for a major investment bank, Wells Fargo Securities. During that time, he has gained considerable knowledge in Microsoft SQL Server by supporting hundreds of applications ranging from high-volume trading applications to massive data warehouses.

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 NEXTFROM 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.


Comments

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

Loading comments...