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.