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