SQL Server Script to Create Windows Directories

By:   |   Comments (6)   |   Related: More > Scripts


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, November 15, 2022 - 8:18:09 AM - jim Back To Top (90686)
hello sir,
what if I want to create a subfolder inside a nfc folder ?

Wednesday, March 6, 2013 - 5:05:26 AM - Johny Woller Skovdal Back To Top (22586)

Great post and thank you for your input Tim. I ended up doing the "create if exists" part like this:

 

DECLARE @BackupDestination nvarchar(500) = N'C:\foo'; -- NOTE: The path must NOT include a trailing backslash

 

SET NOCOUNT ON;

DECLARE @DirectoryExists int;

EXEC master.dbo.xp_fileexist @BackupDestination, @DirectoryExists OUT;

IF @DirectoryExists = 0

EXEC master.sys.xp_create_subdir @BackupDestination;

SET NOCOUNT OFF;


Saturday, February 23, 2013 - 3:51:49 AM - Ruud Back To Top (22370)

 

Tim,

Thanks for this great post. It has been very helpful! I did notice however that the

lines:

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree@DataPath

and

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree@LogPath

never inserts a record in @Dirtree, so the EXEC master.dbo.xp_create_subdir is always executed. That in itself does seem to be a problem. Apparently the extended procedure tests if the dir exists or catches the error and runs without throwing an error.

It took me a while to figure it out but the reason is that @Datapath and @Logpath are empty after they are created. There for no dir tree.

If you change the lines to:

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree'S:\zTest1\'

and

INSERT INTO @DirTree(subdirectory, depth)

EXEC master.sys.xp_dirtree'S:\zTest2\'

Then @Dirtree will have 'Foo' in the list and master.dbo.xp_create_subdir will not be executed.

Alternatively you could simple use another extended procedure.

EXEC Master.dbo.xp_fileexist.  http://www.mssqltips.com/sqlservertip/1272/file-validation-in-sql-server-with-xpfileexist-stored-procedure/


Tuesday, July 17, 2012 - 8:30:09 AM - Luciana Back To Top (18571)
I want to see results in table so i m perfirmong following stepsafter clicking on View >Options >data grids -visual it shows me message as follows The following files specified on the executables tab could not be foundTNS PINGPing. i m using toad 7.3.0.0

Friday, April 18, 2008 - 5:32:06 PM - timmer26 Back To Top (892)

Jeff, can you tell me a little about your environment?  Version of SQL, data type of the field in question, etc.?  I'll do what I can to help.


Wednesday, April 16, 2008 - 9:22:46 AM - Jeff Roughgarden Back To Top (877)

This is the first time I've been on this site. It's very nice. Thanks for doing it.

 I have a question somewhat related to this script for creating Windows folders.

 The conventional wisdom is that relatively static media resources like images, flash, and movies for the web should be stored in the file system and not in SQL Server databases. This is to improve performance and reduce maintenance hassles associated with a very large database. SQL Server should store only the path to the resource.

I've inherited a database that violates this principle and I'd like fix it by exporting the resources (stored as data type image) to files in a folder structure. Your script will help me create the folders (one for the owner of each set of images) but I don't know how to actually write the image data to files from within SQL. I'm working on doing it from C# but my approach is awkward since part is SQL and part is C# and there is no convenient coordination between the two.

I've Googled this repeatedly, but found nothing on how to export binary data from SQL to the file system.















get free sql tips
agree to terms