Skip to content

How To: Create Dynamic File System Directory in TSQL

July 12, 2008

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  
kick it on DotNetKicks.com

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 29 other followers

    %d bloggers like this: