DevPinoy.org
A Filipino Developers Community
   
How To: Create Dynamic File System Directory Inside 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. RECONFIGURE
  4. GO
  5. EXEC SP_CONFIGURE 'xp_cmdshell', 1
  6. GO
  7. RECONFIGURE
  8. 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. SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) -- gives us YYYYMMDD

  6. SET @path = N'C:\Backup\' + @folderdate + '\' -- Path as C:\Backup\YYYYMMDD

  7. SELECT @cmd = 'md "' + @path + '"'

  8. EXEC master..xp_cmdshell @cmd, no_output

  9. -- now I can direct all the backup file to the created subdirectory
  10. -- like,
  11. -- 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. RECONFIGURE
  4. GO
  5. EXEC SP_CONFIGURE 'show advanced options', 0
  6. GO
  7. RECONFIGURE
  8. GO

--Marlon Ribunal Please Help Me: Here's How <Link>

kick it on DotNetKicks.com

[As posted in my blog at http://dbalink.wordpress.com]


Posted 07-14-2008 9:24 AM by marl
Copyright DevPinoy 2005-2008