Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



T-SQL - Backup All Databases in an Instance and Return Backup History
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-10-06








T-SQL: Backup Databases

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
DecLare @server VARCHAR(150) --used for servername

--Specify Database Backup Directory
SET @path = 'C:\Downloads\DB_Backups\PC01\'

--Specify Filename Format
SET @fileDate = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120), ' ', '-'), ':', '-')

DECLARE db_cursor CURSOR FOR
SELECT UPPER([Name])
FROM
master.sys.databases
Where [Name] not in ('TempDB') and state_desc = 'online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '_FULL.BAK'
BACKUP DATABASE @name
TO DISK = @fileName
WITH NOFORMAT,
NOINIT,
SKIP,
NOREWIND,
NOUNLOAD,
--COMPRESSION,
STATS = 10

FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor



T-SQL: Return Backup History

;WITH backups_list AS
(
Select
ROW_NUMBER() Over (Partition By b.database_name, b.type Order By b.database_name ASC, b.backup_finish_date DESC) AS 'RowNumber',
d.name AS [database_name],
b.backup_set_id,
b.media_set_id,
b.type AS backup_type,
b.backup_finish_date,
b.backup_start_date,
b.backup_size,
bms.is_compressed,
bms.is_encrypted,
d.recovery_model_desc,
b.user_name,
b.machine_name,
b.server_name
From Master.sys.databases AS d
Left Join Msdb.dbo.[backupset] AS b on d.name = b.database_name
Left Join Msdb.dbo.backupmediaset bms on b.media_set_id = bms.media_set_id
Where d.name not in ('TEMPDB')
)
Select
UPPER(bl.database_name) as 'Database',
bl.recovery_model_desc as 'RecoveryModel',
COALESCE(CONVERT(varchar(25),bl.backup_finish_date),'-') as'BackupFinish',
CASE bl.backup_type WHEN 'D' THEN 'DATABASE' WHEN 'I' THEN 'DIFFERENTIAL' WHEN 'L' THEN 'LOG' ELSE '-' END AS 'BackupType',
bl.Backup_Start_Date,
bl.Backup_Finish_Date,
DateDiff(s,bl.backup_start_date, bl.backup_finish_date) BackupDuration_Sec,
CONVERT(Decimal(18,2),bl.backup_size/1024/1024) as 'BackupSize_MB',
bl.Is_Compressed,
ISNULL(bl.Is_Encrypted,0) as 'Is_Encrypted',
COALESCE(CONVERT(varchar(250),m.physical_device_name),'-') as 'PhysicalDeviceName',
COALESCE(CONVERT(varchar(10),(DATEDIFF(day,bl.backup_finish_date, GETDATE()))),'-') AS 'Last_Backup_Days',
COALESCE(CONVERT(varchar(10),(DATEDIFF(hour,bl.backup_finish_date, GETDATE()))),'-') AS 'Last_Backup_Hours',
COALESCE(CONVERT(varchar(10),(DATEDIFF(minute,bl.backup_finish_date,GETDATE()))),'-') AS 'Last_Backup_Mins',
COALESCE(CONVERT(varchar(25),bl.server_name),'-') as 'Server',
COALESCE(CONVERT(varchar(25),bl.user_name),'-') as 'UserName'
From backups_list bl
Left Join msdb.dbo.backupmediafamily m ON bl.media_set_id = m.media_set_id
Where (bl.RowNumber = 1 and m.family_sequence_number = 1) or bl.backup_set_id is null
Order By bl.backup_finish_date ASC, bl.database_name ASC