How to backup all SQL Server Databases in SQL Server - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or Developer. You need to take backup of all the databases from SQL Server Instance.  The backups should be created with date_time.

The below script can be used to take full backup of all the databases from SQL Server Instance, Also you can change Select query which returns list of databases to include or exclude databases from backup.


 USE MASTER

GO
DECLARE @BackupPath varchar(100)
--Provide the backup path 
SET @BackupPath = 'C:\BackupFolderPath\'
DECLARE @DatabaseName AS varchar(128)

DECLARE Cur CURSOR FOR
--Change the select query for the DBs you like to backup
SELECT
  name
FROM sys.databases
WHERE name NOT IN ('TempDB', 'model')

OPEN Cur
FETCH NEXT FROM Cur INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @SQL varchar(max) = NULL
  DECLARE @DBNamewithDateTime varchar(128) = NULL
  SET @DBNamewithDateTime = @DatabaseName + '_' + REPLACE(CAST(GETDATE()

       AS date), '-', '') + '_' + REPLACE(CAST(CAST(GETDATE() AS time) 

       AS char(8)), ':', '')

  SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO  DISK = N''' +

              @BackupPath + '' + @DBNamewithDateTime + '.bak''
              WITH NOFORMAT, NOINIT,  NAME = N''' + @DBNamewithDateTime 

              + '-Full Database Backup'',
                 SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'

  PRINT @SQL
  EXEC (@SQL)
  FETCH NEXT FROM Cur INTO @DatabaseName
END
CLOSE Cur
DEALLOCATE Cur

I execute above script on my SQL Server Instance and it took the full backups of databases as shown below with datetime.

How to take full backup of all databases from SQL Server Instance by using TSQL