Script To Backup All The Databases In SQL Server

If you are maintaining a production SQL Server, then you might be using a Maintenance Plan or an SQL Server Agent Job to automate the regular database backup process. However, once in a while, you might need to backup all the databases in the server for some reason like an unplanned maintenance task. In such situations, a Transact SQL script to backup all the databases will come in handy. Here is the TSQL script I use:

The input for this script are:

• Path to store the DB backups.
• List of databases to be excluded.

Script To Backup All The Databases

DECLARE @bk_path VARCHAR(256);
DECLARE @db_name VARCHAR(50);
DECLARE @bk_file_date VARCHAR(20);
DECLARE @bk_file_name VARCHAR(256);
DECLARE @i INT = 1;

/* Specify database backup directory. Change this as needed. */
SET @bk_path = 'C:\Backup\';

/* Backup file format DBname_YYYYMMDD_HHMMSS.BAK. Change this as needed. */
SELECT @bk_file_date = FORMAT(GETDATE(), 'yyyyMMdd_hhmmss');

DECLARE @db_names TABLE (
   id INT IDENTITY(1,1) PRIMARY KEY, 
   db_name VARCHAR(50) NOT NULL );

INSERT INTO @db_names
   SELECT name 
   FROM master.sys.databases 
   WHERE name NOT IN ('master','model','msdb','tempdb')  /* Databases which needs to be excluded */
      AND state = 0 /* Include only the database which are online */
      AND is_in_standby = 0; /* database is not read only for log shipping */

WHILE EXISTS (SELECT 1 from @db_names WHERE Id = @i)
BEGIN
   
   SELECT @db_name = db_name from @db_names WHERE Id = @i;
   PRINT 'Backup Started: ' + @db_name
   
   SET @bk_file_name = @bk_path + @db_name + '_' + @bk_file_date + '.BAK';

   BACKUP DATABASE @db_name TO DISK = @bk_file_name WITH STATS;

   SET @i = @i + 1;
   
END
GO

Reference

• More about BACKUP command at Microsoft Docs.