How To Backup All Databases In SQL Server

When we have several databases on a Server to backup, it is very time-consuming to backup all databases manually. In such case, we can use T-SQL to automate these tasks. We can use SQL Server Management Studio to back up the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach. 

 

Using T-SQL, we can generate backup commands. With the use of cursors, we can cursor through all of the databases to back them up one by one. 

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'C:\SQL_Server_Backup\'

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

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

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

The above T-SQL will back up each database within the instance of SQL Server. Note that the @path is to be replaced by the backup directory. In my case, the backup directory is ‘C:\SQL_Server_Backup\’.

File Naming Format

DBname_YYYYDDMM.BAK

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

DBname_YYYYDDMM_HHMMSS.BAK

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

SCREENSHOT

 

All databases are backed up with single T-SQL script.


This script will allow you to back up all the databases on the fly. The script looks for the .mdf file in your SQL instance and backs all of the files it finds except the system database.