T-SQL script to backup all user databases with time stamped backup file.

T-SQL script to backup all user databases with time stamped backup file.

Rate This
  • Comments 21

use master;

go

 

 

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

 

-- Selecting user database names.

select name as DatabaseName

from sys.sysdatabases

where ([dbid] > 4) and ([name] not like '$')

 

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @backupQuery varchar(500);

 

-- make sure that the below path exists

set @backupPath = 'c:\SQLBackupFolder\'

 

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

 

BEGIN

-- Backup SQL statement

set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak'''

 

-- Print SQL statement

print @backupQuery

 

-- Execute backup script

EXEC (@backupQuery)

 

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

 

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

 

The above script backs up all user databases, for each database the backup file is generated by appending database name to timestamp

P.S.

If you are interested in restore script let me know :)

Leave a Comment
  • Please add 2 and 2 and type the answer here:
  • Post
  • Nice, I have a powershell script that does the same.

  • Thanks Glenn, Are you sharing your script through your blog ?

  • Here is the link to the post, www.virtualrealm.com.au/.../Powershell-Script-to-Backup-all-SQL-Server-Databases-on-a-Server

  • I know that it is a little late , but , oh yes , i am interested by the restore script

    and thanks...

  • Can you please tell how to use the above script for any particular database or only 2-3 databases

  • You can filter user database by modifying below query

    select name as DatabaseName

    from sys.sysdatabases

    where ([dbid] > 4) and ([name] not like '$')

    and add in clause to specify certain database names

  • Thanks Ahmed, this was very handy, just one little suggestion: encapsulate the "@dbName" string in the backup statement in square brackets. The script failed for me as is when the DB has a dash in the name.

  • Thanks a lot Troy for the feedback :)

  • I am interested in the Restore Script

  • An Excellent script.

  • Thanks a lot LianVh for your feedback, I will be writing post one restore script soon :)

  • Thanks a lot LianVh for your feedback, I will be writing post one restore script soon :)

  • Hi, how can the script be edited to eliminate the space between the date and time?  I would like to replace the space with a underscore.  Thanks.

  • When building @backupquery, enclose the database name in square brackets in case it contains hyphens.

  • very help full script.

Page 1 of 2 (21 items) 12