title: SQL Script to create Sub directories if they do not exist
body: <p>Below I have a test script to back up databases not excluded. I need to check if these directories exists in this path first then create them if they do not exist. Then the backup path would need to back up each file into its respective directory. I am kind of lost on how to do this, and per our environmental requirements I cannot use SSMS Maintenance Plan for this job. Any advice is appreciated. </p>
  
  <p>Thanks</p>
  
  <pre><code>    DECLARE @name VARCHAR(50) -- database name  
  DECLARE @path VARCHAR(256) -- path for backup files  
  DECLARE @fileName VARCHAR(256) -- filename for backup  
  DECLARE @fileDate VARCHAR(20) -- used for file name
  DECLARE @Datapath varchar(500)
  DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
  
  
  
  -- specify database backup directory
  SET @path = '\\nas01\Admin\bbelden\test\' 
  SET @Datapath = '\\nas01\Admin\bbelden\test\' + @name
  
  -- specify filename format
  SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
  
  INSERT INTO @DirTree(subdirectory, depth)
  EXEC master.sys.xp_dirtree @Path
  
  IF NOT EXISTS (SELECT * FROM @DirTree WHERE subdirectory = @Name)
  EXEC master.dbo.xp_create_subdir @DataPath
  
  DECLARE db_cursor CURSOR FOR  
  SELECT name 
  FROM master.dbo.sysdatabases 
  WHERE name NOT IN ('master',
                      'model',
                      'msdb',
                      'tempdb',
                      'PracticaSd-Dallasmetro')  -- exclude these databases
  
  
  
  OPEN db_cursor   
  FETCH NEXT FROM db_cursor INTO @name   
  
  
  WHILE @@FETCH_STATUS = 0   
  BEGIN   
         SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
         BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION
  
  
         FETCH NEXT FROM db_cursor INTO @name   
  END   
  
  
  
  CLOSE db_cursor   
  DEALLOCATE db_cursor
  
  RESTORE VERIFYONLY FROM DISK = @FileName 
  </code></pre>