Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Script to Create Windows Directories


By:   |   Read Comments (5)   |   Related Tips: More > Scripts

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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
Platform = SQL Server 2005
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



Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 06, 2013 - 5:05:26 AM - Johny Woller Skovdal Back To Top

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

 

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

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

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.


Learn more about SQL Server tools