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
Script To Backup All The Databases
Script To Backup All The Databases

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.