How To: Create Dynamic File System Directory in TSQL

I have created a Backup Script to backup all the databases in our SQL Server 2005. I want to keep old backups for sometime for some reason; so I want to create subdirectory with the run date as the name of the folder to separate the set of backups by date.

I need to use DOS Command from within my TSQL Code to create the subdirectory. This feature is turned off by default. I am making sure that I am turning this off after I run the script:

1. EXEC SP_CONFIGURE ’show advanced options’, 1
2. GO
3.  
4. RECONFIGURE
5. GO
6.  
7. EXEC SP_CONFIGURE ‘xp_cmdshell’, 1
8. GO
9.  
10. RECONFIGURE
11. GO

Then here’s how I execute DOS Command from within my TSQL:

1. DECLARE @path VARCHAR (200) – Path of the Backup Files
2. DECLARE @folderdate VARCHAR (50) – The subdir for my backups with Format YYYYMMDD
3. DECLARE @cmd VARCHAR (4000) – The command to create Subdir
4. DECLARE – Other Variables for my backup scripts…
5.  
6. SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) – gives us YYYYMMDD
7.  
8. SET @path = N‘C:\Backup\’ + @folderdate + ‘\‘ — Path as C:\Backup\YYYYMMDD
9.  
10. SELECT @cmd = ‘md “‘ + @path + ‘”‘
11.  
12. EXEC  master..xp_cmdshell @cmd, no_output
13.  
14. – now I can direct all the backup file to the created subdirectory
15. – like,
16. – SET @filename = @path + [other_variable/s] + ‘.BAK‘

For security reason, I want to make sure that I turn my xp_cmdshell off. So at the very end of my script:

1. EXEC SP_CONFIGURE ‘xp_cmdshell’, 0
2. GO
3.  
4. RECONFIGURE
5. GO
6.  
7. EXEC SP_CONFIGURE ’show advanced options’, 0
8. GO
9.  
10. RECONFIGURE
11. GO

–Marlon Ribunal  

News-Worthy: Linked DBA Blog Featured in ITSpecialist.com: http://www.itspecialist.com/Developer/Microsoft/SQL/tabid/548/articleType/ArticleView/articleId/41/Linked-DBA-Group.aspx#Comments