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


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 🙂

Comments (23)

  1. Glenn Wilson says:

    Nice, I have a powershell script that does the same.

  2. Thanks Glenn, Are you sharing your script through your blog ?

  3. Papy Normand says:

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

    and thanks…

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

  5. 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

  6. Troy Hunt says:

    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.

  7. Thanks a lot Troy for the feedback 🙂

  8. I am interested in the Restore Script

  9. Thanks a lot LianVh for your feedback, I will be writing post one restore script soon 🙂

  10. Thanks a lot LianVh for your feedback, I will be writing post one restore script soon 🙂

  11. Chris says:

    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.

  12. Eric says:

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

  13. Nilesh Dubey says:

    very help full script.

  14. Roger says:

    Just what I was looking for today. Thanks!

  15. Aaron says:

    Good Stuff – just what i was looking for !

    I can't seem to find the restore script you said you would post; non-the-less thank you for sharing this script. 🙂

  16. Jim says:

    Hi Ahmed,

    Not sure if you're still monitoring this blog. I'm getting errors from a few of my databases during backup using your script above. The pattern seems to indicate it has to do with spaces or hyphens being in the database name. In the sample error below, the database is named 'Monster Club'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Club'.

    … or 'Financial Distribution':

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'Distribution'.

    I also received an error message on an offline 'tmpdb_preupgradecheck_[Long GUID]' database:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '-'.

    Thank you for your help,

    Jim

  17. Jim says:

    I changed this:

    set @backupQuery =  'backup database ' + @dbName + ' to disk =

    to this:

    set @backupQuery =  'backup database "' + @dbName + '" to disk =

    And it's working okay now. It'd be nice if the backups of the databases could run in parallel instead of serially.

  18. Gilliard says:

    Quero saber se existe algum script que faça backup de um servidor principal para um limpo levando todos os dados e usuários do mesmo.

  19. Alesya says:

    Nice! Thank you!

  20. William says:

    I am wondering how was the restore script looks like?

  21. Mubeen says:

    Thanks for the script.  Is it possible to add logic to check whether the Database is Primary as part of Availability Group Database?