Problem
Most SQL Server Database Administrators have specific standards for storing their database files on the disk drives. In our environment many of our instances host multiple databases, and though database names must be unique on a Microsoft SQL Server instance, I do not like to drop all of the database and/or log files into the default data and log directories on my SQL Server instances. To make file management easier, I create a subdirectory for each database in the default data and log paths. I was curious if I could create the database file folders dynamically, without needing to open an Windows Explorer session in order to create the new database folder each time I created a database. This would also facilitate consistency in database creation for those rare times someone else had to occupy the DBA chair while I was sneaking off on vacation. Do you have a script of something that I could use?
Solution
Absolutely! By making use of two undocumented extended stored procedures (master.sys.xp_dirtree and master.sys.xp_create_subdir) you can execute the following code to create folders from within your T-SQL code. The T-SQL code will expect values for the database name, full data path, and full log path.
-- Windows File System Directory Creation Script
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName
-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath
-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath
-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree
-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath
-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath
SET NOCOUNT OFF
GO
Brief Code Overview
The core functionality in this script is based on two extended system stored procedures: master.sys.xp_dirtree and master.sys.xp_create_subdir. Let’s take a look at each, individually:
- master.sys.xp_dirtree – This extended stored procedure returns all the folders within the folder that is passed into it as a parameter. It also returns the nested level of each folder found. By inserting the values returned from xp_dirtree into the temp table you can then query against it to test the existence of the folder you are attempting to create.
- master.sys.xp_create_subdir – Use this stored procedure to create a folder on either a local server or network share.
Next Steps
- Whether you have a need to create directories for new databases, backups or custom ETL processes, consider this script as a new tool in your toolbox.
- If you have a consistent set of processes you need to perform, consider converting the code into a stored procedure by changing the variables in the code to parameters that can be passed into the stored procedure. The code can also be easily customized to meet your needs.
- Check out the other extended stored procedure related tips:
