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 Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds two MCITP certifications in SQL Server 2008. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.
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.