Simple script to backup all SQL Server databases

By: Greg Robidoux   |   Updated: 2022-07-06   |   Comments (281)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Backup

Problem

Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these 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 I have seen several servers where there are 100+ databases on the same instance of SQL Server. You could use SQL Server Management Studio to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Solution

With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one. You could also use a while loop if you prefer not to use a cursor. This is a very straight forward process and you only need a handful of commands to do this.

How to Backup All SQL Server Databases

1. Specify path to store database backups
2. Specify backup file name format
3. Select list of databases to backup
4. Loop through databases
5. Programmatically create database backup command using database name, path and file name format
6. See attached SQL Server backup script

Database Backup File Name Format DBname_YYYYMMDD.BAK

Here is the script that will allow you to backup each database within your instance of SQL Server. 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 filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
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

Database Backup File Name Format DBname_YYYYMMDD_HHMMSS.BAK

If you want to also include the time in the filename you can replace this line in the above script:

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

with this line:

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

Adding Additional Items to Backup Command

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.

Notes

In this 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 you can include them in the NOT IN section as well.

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

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

View all my tips