Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to firstname.lastname@example.org.
Contact the author
Archives for this blog
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.
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:
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.
-- Only one input parameter needed from the user.
SET@BaseFolder = 'D:\Backup';
DECLARE Db CURSOR FOR
SELECT name FROM sys.databases WHERE name <> 'tempdb';
FETCH NEXT FROM Db INTO @DbName;
WHILE @@FETCH_STATUS = 0
-- Dynamically create a folder for each database.
SET @DatabaseFolder = @BaseFolder + '\'
+ CONVERT(nvarchar,SERVERPROPERTY('MachineName')) + '\'
+ '\'+ @DbName;
EXEC master.sys.xp_create_subdir @DatabaseFolder;
FETCH NEXTFROM Db INTO@DbName;
-- Clean up
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.