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
--Marlon Ribunal Please Help Me: Here's How <Link>
[As posted in my blog at http://dbalink.wordpress.com]
Posted
07-14-2008 9:24 AM
by
marl