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:
-
EXEC SP_CONFIGURE ‘show advanced options’, 1
-
GO
-
-
RECONFIGURE
-
GO
-
-
EXEC SP_CONFIGURE ‘xp_cmdshell’, 1
-
GO
-
-
RECONFIGURE
-
GO
Then here’s how I execute DOS Command from within my TSQL:
-
DECLARE @path VARCHAR (200) — Path of the Backup Files
-
DECLARE @folderdate VARCHAR (50) — The subdir for my backups with Format YYYYMMDD
-
DECLARE @cmd VARCHAR (4000) — The command to create Subdir
-
DECLARE — Other Variables for my backup scripts…
-
-
SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) — gives us YYYYMMDD
-
-
SET @path = N‘C:\Backup\’ + @folderdate + ‘\‘ — Path as C:\Backup\YYYYMMDD
-
-
SELECT @cmd = ‘md “‘ + @path + ‘”‘
-
-
EXEC master..xp_cmdshell @cmd, no_output
-
-
— now I can direct all the backup file to the created subdirectory
-
— like,
-
— 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:
-
EXEC SP_CONFIGURE ‘xp_cmdshell’, 0
-
GO
-
-
RECONFIGURE
-
GO
-
-
EXEC SP_CONFIGURE ‘show advanced options’, 0
-
GO
-
-
RECONFIGURE
-
GO
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
Trackbacks