I'm writing a script that will create a new folder and backup all databases into that folder. The script will generate the new folder, but will not save the backups into that folder. I'm not sure what I'm missing. Thank you in advance.

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 @DirTree TABLE (subdirectory nvarchar(255), depth INT)


SET @path = '\\noc2-storage\IT_Backup\Weekly_SQL\CDADB1\' + @fileDate;

SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @path)
EXEC master.dbo.xp_create_subdir @path
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @filename  

       FETCH NEXT FROM db_cursor INTO @name   

CLOSE db_cursor   
DEALLOCATE db_cursor

Thank you in advance

  • @SQL_Underworld it seemed to be the most common thing to use to get each database
    – kevorski
    Dec 30 '16 at 18:19
  • The query will fail if any database is offline .. you need to take care of those cases as well.
    – Kin Shah
    Dec 30 '16 at 20:05

Why are you reinventing the wheel .. as there is a cost to reinvent it !

You are also using deprecated stuff e.g. sysdatabases .. instead use sys.databases.

You should use -

Both are free and are well tested by the community.


You have no information in your @DirTree table variable.

You declare the variable, but you never insert any information into it. Afterwards, you are doing an IF NOT EXISTS where you are trying to select something from the table variable and match it on the @path, but there are no records in that table based on the code you provided.

On a side note, Ola has fantastic backup scripts that can ease a lot of your pains and there are automated restore processes on the web that work off of Ola's solution.

Our lord and savior's website

  • I will look into Ola's solution. Thank you. I will be working on the DirTree variable. Thank you
    – kevorski
    Dec 30 '16 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.