Simple script to backup all SQL Server databases

Problem

Sometimes things that seem complicated can be easier than you think. This is true with the power of T-SQL for repetitive tasks. One of these repetitive tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases but can take time if you have hundreds of databases. You could use SQL Server Management Studio or Maintenance Plans, but using T-SQL can be a much simpler and faster approach.

Solution

With the use of T-SQL you can generate backup commands and with the use of cursors or a while loop you can iterate through all databases to back them up one by one. This is a very straight forward process, and you only need a handful of commands.

How to Backup All SQL Server Databases

  1. Specify the path to store database backups
  2. Specify the backup file name format
  3. Use SQL to select databases to backup
  4. Programmatically create database backup commands
  5. Loop through each of the databases

Database Backup Scripts

Let’s look at a couple of options.

Format DBname_YYYYMMDD_HHMMSS.BAK

Here is the SQL backup script that will allow you to backup each database within your instance of SQL Server. This will use the file name format of DBname_YYYYMMDD_HHMMSS.BAK.

You will need to change the @path to the appropriate backup directory.

-- source: https://www.MSSQLTips.com
-- https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/
DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\test\'  
 
-- specify YYYYMMDD_HHMMSS as filename format '
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
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  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Format DBname_YYYYMMDD.BAK

If you want just the date in the filename you can replace this line in the above script:

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')

with this line:

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)

Add Backup Compression Option

To add additional options, such as backup progress (stats) and compression, you can change this line in the script:

BACKUP DATABASE @name TO DISK = @fileName

with this line:

BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION

There are plenty of other backup options you can add.

Transaction Log Backup Script

The following will backup the transaction log for all databases that are in either FULL or BULK_LOGGED recovery.

-- source: https://www.MSSQLTips.com
-- https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/
DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = 'C:\test\'  
 
-- specify filename format YYYYMMDD_HHMMSS '
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
AND recovery_model_desc <> 'SIMPLE' -- only backup for full or bulk logged
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.TRN'  
   BACKUP LOG @name TO DISK = @fileName  
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

Differential Backups Script Options

If you want to do differential backups just change the following lines in the Database Backup script.

   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName

Change to:

   SET @fileName = @path + @name + '_' + @fileDate + '.DIF'  
   BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL

Notes

In this SQL Server backup script, we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Also, if you wanted to bypass some of your user databases include them in the NOT IN section of the code.

Next Steps

  • Add this script to your toolbox
  • Modify this script and make it a stored procedure to include one or many parameters
  • Enhance the script to use additional BACKUP options
  • Review all SQL Server Backup and Restore tips
  • Check out the following SQL Server Tutorials – Backup and Restore

Related Posts

33 Comments

  1. Great script, it’s working as expected.Could you also provide a script to take log backups for all SQL Server databases, excluding those with a Simple recovery model in the same manner..

    • You can use this script to do transaction log backups for all databases not in the SIMPLE recovery mode.

      — source: https://www.MSSQLTips.com
      — https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/
      DECLARE @name NVARCHAR(256) — database name
      DECLARE @path NVARCHAR(512) — path for backup files
      DECLARE @fileName NVARCHAR(512) — filename for backup
      DECLARE @fileDate NVARCHAR(40) — used for file name

      — specify database backup directory
      SET @path = ‘C:\test\’

      — specify filename format
      SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + ‘_’ + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),’:’,”)

      DECLARE db_cursor CURSOR READ_ONLY FOR
      SELECT name
      FROM master.sys.databases
      WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) — exclude these databases
      AND state = 0 — database is online
      AND is_in_standby = 0 — database is not read only for log shipping
      and recovery_model_desc ‘SIMPLE’

      OPEN db_cursor
      FETCH NEXT FROM db_cursor INTO @name

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.TRN’
      BACKUP LOG @name TO DISK = @fileName

      FETCH NEXT FROM db_cursor INTO @name
      END

      CLOSE db_cursor
      DEALLOCATE db_cursor

  2. I get an access denied error
    “Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 27 BACKUP DATABASE is terminating abnormally.”

    • Hi Divan, try to change the directory where you are creating the backups or make sure the SQL Server service account or SQL Agent account has access to that directory. SQL Server probably does not have access to write the directory you specified.

  4. Hi Anil,

    the change assumes that each folder already exists for each database. So if you create the folders ahead of time it should work.

    Also, make sure your @path variable has a \ like this “C:\SQL2022\Backup\”

    -Greg

  5. Hello Greg
    It worked for the first database, rest of them failed as create a directory with in the first on e.g. C:\SQL2022\BackupSQLDWDiagnostics\DWDiagnostics_20230925.BAK’ with error Operating system error 3(The system cannot find the path specified.
    I will work on the script may be i am not using my TSQL head too much.
    Many thanks
    Anil

  7. Hi Anil, if the folders are already created for each database and the folder name matches the database name you could modify this section

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
    BACKUP DATABASE @name TO DISK = @fileName

    FETCH NEXT FROM db_cursor INTO @name
    END

    to

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @path = @path + @name + ‘\’

    SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
    BACKUP DATABASE @name TO DISK = @fileName

    FETCH NEXT FROM db_cursor INTO @name
    END

  8. Hello
    Great script , I was wondering if there is way to back up multiple user databases in there own folder. I did use mkdir command but i think i am missing someting.
    Many thank in advance
    Anil

  9. Just a tip, use the FORMAT function whenever you need to format dates as strings:
    SELECT @fileDate = FORMAT(GETDATE(),’yyyyMMdd_HHmmss’)

  12. Hi Chandu,

    looks like you are using these scripts: https://ola.hallengren.com/sql-server-backup.html

    I am not sure why it is skipping databases based on what you are using. Are all of the databases you are trying to backup online?

    -Greg

  13. this was the job script of a scheduled job of my servers, with this in one server its not skipping in DBs but in one server it’s skipping few databases for full backup job, Greg Can you help me with this

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d tools -Q “execute [dbo].[DatabaseBackup] @Databases = ‘ALL_DATABASES’, @Directory = N’G:backuppath\Backup’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupMode = ‘BEFORE_BACKUP’, @CleanupTime = 70, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b

  14. Hi Greg
    It’s a job history, even I tried with the same full backup job which run in other server, scripted and executed it in current server which has full backup skipping issues for few database, still its skipping databases, I tried everything I can,
    The same script is working fine in other servers but when I execute it in backup issue server it’s not working properly, when I checked job history, it’s showed that job was successful

  15. Hi Chandu,

    Can you provide more info on what you mean by “it shows backup job is successful in the history”? Is this the job history or backup history? One other thing, some people suggested changing the cursor type to STATIC, because of this same issue.

    Thanks
    Greg

  16. I’ve a scheduled job for all Database, but still that job skips few DBs backup, even though it shows backup job is successful in the history,
    But when I retrigger the job manually “start job at the step” it backups perfectly, what could be the issue,

  18. Hi Andy,

    Good catch. It should be YYYYMMDD. I can’t believe this article has been out there since 2006 and you were the first to notice.

    I will update the article.

    -Greg

  19. Why, oh why, would anyone use the date format yyyyddmm? Are you trying to be as obtuse as possible? The iso standard date (yyyy-mm-dd) which avoids the American (mm-dd-yyyy) and British (dd-mm-yyyy) confusion, and you go an invent an American version of that. An additional benefit of yyyy-mm-dd is that the files named as such will be in date order, rather than a useless order.

  20. Hi Saiteja,

    Check out this article. https://www.mssqltips.com/sqlservertip/4353/export-sql-server-records-into-individual-text-files/

    You will need to make a few changes to do table by table, but this might help you.

    -Greg

  21. DECLARE @PATH nvarchar(128)='[Path]\’
    DECLARE @SERVERNAME nvarchar(128)=”
    DECLARE @USERNAME nvarchar(128)=”
    DECLARE @PASSWORD nvarchar(128)=”

    DECLARE @table_name nvarchar(128)
    DECLARE @database_name nvarchar(128)
    DECLARE @schema_name nvarchar(128)

    PRINT ‘REM Backup Script ‘ + CAST(GETDATE() AS nvarchar(30))

    DECLARE abc CURSOR FOR
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES I
    JOIN sys.tables T ON I.Table_Name = T.Name
    JOIN sys.dm_db_partition_stats S ON t.object_id = s.object_id
    AND t.type_desc = ‘USER_TABLE’ AND s.index_id = 1 AND s.row_count >0 ORDER BY t.name collate Latin1_general_BIN2
    OPEN abc;
    FETCH NEXT FROM abc INTO @database_name, @schema_name, @table_name
    WHILE (@@FETCH_STATUS = 0) BEGIN
    PRINT ‘BCP ‘ + @database_name + ‘.’ + @schema_name + ‘.’ + @table_name + ‘ OUT “‘ + @PATH + @table_name +’.dat” -S “‘ + @SERVERNAME + ‘” -U “‘ + @USERNAME + ‘” -P “‘ + @PASSWORD + ‘” -N’
    FETCH NEXT FROM abc INTO @database_name, @schema_name, @table_name;
    END;
    CLOSE abc;
    DEALLOCATE abc;
    GO

  25. While extracting DB i am getting error as BCP is not recognised as an internal or external command,operable program or batch file
    Could you please help me with this

  26. If you are just using the database name as the backup file, then each backup gets added to the same file. You will need to add FORMAT, INIT to the command as follows. This will initialize the file and overwrite the previous contents.

    BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, INIT

    You can check out this info for more backup options: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15

  27. if the backup filename is same(like just db name) when it is gettig backup if there is same name file, its not deleting old backup file, its stacking with older version.

    like you get a back up today and its 100mb then you get another tomorrow, you expect 100 or 101 but it gonna be 200 something. Because its not deleting older one, even the file names are same

  29. For instructional purposes this is a good exercise for explaining how to automate backups by T-SQL. However, since this was originally posted, Ola Hallengren’s maintenance scripts came on the scene (and dbaTools if you prefer Powershell) and has become near industry standard for automating production databases. Just thought I’d mention these other options that have since become available.

Leave a Reply

Your email address will not be published. Required fields are marked *