Cool Tips‎ > ‎File System‎ > ‎

File System - Create Sub-Folder from TSQL

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        27 Oct 2010

Description

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

e.g.
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.

Code


-- Intended to be used for backup file paths
-- will work on a network share if the SQL Server Service account has
-- access rights to it

SET NOCOUNT ON 

-- 1 - Variable declaration
DECLARE        
      
@DBName             SYSNAME,
      
@ServerName         SYSNAME,
      
@BaseFolderPath     VARCHAR(100),
      
@FullPath           VARCHAR(200)

DECLARE @DirTree TABLE (subdirectory NVARCHAR(255), depth INT)

-- 2 - Initialize variables
SET @ServerName = @@SERVERNAME
SET @DBName = DB_NAME()
SET @BaseFolderPath = 'C:\Backup'

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC MASTER.sys.xp_dirtree @BaseFolderPath

-- 4 - Create the new folder using a concatenation of Base, Server and Database names
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
   AND NOT EXISTS (
SELECT 1 FROM @DirTree WHERE subdirectory = @ServerName)
BEGIN
       SELECT
@FullPath = @BaseFolderPath + '\' + @ServerName + '\' + @DBName
      
EXEC MASTER.dbo.xp_create_subdir @FullPath

END

SET NOCOUNT OFF


ċ
Create a subdir path if it doesnt exist.sql
(1k)
Andy Hughes,
Jul 5, 2012, 10:14 AM
Comments