Create Sub-Folder from TSQL
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 27 Oct 2010
This piece of code illustrates the use of the xp_DirTree and xp_CreateSubFolder extended stored procedures.
It will create a subfolder structure representing the current server and database names
For a base path of C:\Backup on server MyServer in AdventureWorksDW database
the folder structure C:\Backup\MyServer\AdventureWorksDW will be created
I originally used the technique for dynamically creating backup folders, but it could be easily adapted for just about any code requiring the creation of folders
NOTE: The Base Path folder will also be created if it does not exist.