This post can help you to quickly find SQL Server Backup and Restore Reports from a given SQL Server instance. For a typical DBA a most common task is to check the latest backup and restore history for a database. We usually required this information while we are working on a RCA (Root Cause Analysis) to resolve a specific issue, to check the backup frequency or to check the last backup status details. Here are the quick T-SQL scripts which can help us in checking SQL Server Backup and Restore Reports.
SQL Server Backup History Report:
It captures the backup history for a given database (or all databases) based on the parameter @DBName. If @DBName is NULL then it get all databases backup history, if @DBName is assigned with a database name then it get the backup history for the given database.
/******************************************************************/
/***** Database Backup History Details ****************************/
/***** No Perameter Supplied - Get all details ********************/
/***** DBName- Retrieve Bkp info for given DB ***********/
/***** Uncomment Bkp_Type - Filter Backup History for given type **/
/******************************************************************/
DECLARE @DBName VARCHAR(200),
@Bkp_Type VARCHAR(20);
-- Give a DB name then it gets only the given DB backup history.
-- Ex: @DBName = 'Master'
SET @DBName = NULL
-- 'D' -- Full Backup; 'I' -- Differential Backup; 'L' -- Transaction Log Backup
-- Ex: @Bkp_Type = 'D'
SET @Bkp_Type = NULL;
SELECT DISTINCT
bs.database_name AS 'DB_Name'
, bs.backup_start_date AS 'Bkp_Started_At'
, bs.backup_finish_date AS 'Bkp_Finished_At'
, CONVERT(NVARCHAR(10),(bs.backup_finish_date-bs.backup_start_date),108) AS 'Bkp_duration'
, CASE
WHEN bmf.physical_device_name LIKE 'VN%' THEN 'TAPE DEVICE'
ELSE UPPER(bmf.physical_device_name)
END AS 'Bkp_location'
, bs.type AS 'Backup_Type'
, CASE
WHEN bs.type = 'D' THEN 'FULL'
WHEN bs.type = 'I' THEN 'Differential'
ELSE 'Transactional_Log'
END AS 'Backup_Type_Desc'
, CAST((bs.backup_size/(1024.00*1024.00)) AS DECIMAL(15,2)) AS 'Bkp_Size (MB)'
, CAST((bs.backup_size/(1024.00*1024.00*1024.00)) AS DECIMAL(15,2)) AS 'Bkp_Size (GB)'
, bms.software_name AS 'Backup_Software'
, CASE
WHEN bms.is_compressed = 1 THEN 'Yes'
WHEN bms.is_compressed = 0 THEN 'No'
END AS 'Is_Compressed'
,CASE
WHEN bms.is_password_protected = 1 THEN 'Yes'
WHEN bms.is_password_protected = 0 THEN 'No'
END AS 'Is_Password_Protected'
FROM msdb..backupset bs
JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
JOIN msdb..backupmediaset bms ON bms.media_set_id = bmf.media_set_id
WHERE bs.database_name = ISNULL(@DBName,bs.database_name) AND
bs.type = ISNULL(@Bkp_Type,bs.type)
ORDER BY bs.backup_start_date desc, bs.database_name;
SQL Server Backup Missing Report:
This is a handy script to quickly identify the missing backup details from a given SQL Server instance. With the default parameter settings the script can list the databases with no full backup from last 24 hours. We can customize this using the given parameters.
/************************************************************/
/*************** Missing Backup Report **********************/
/***** We can change parameters to customize the report *****/
/************************************************************/
DECLARE @Backup_Type VARCHAR(20),
@Days SMALLINT;
-- D - Full; I - Differential; L- Transaction Log
SET @Backup_Type = 'D';
-- Number of days
-- Ex: -1: Reports all DB without backup in last 24 hours
-- Ex: -30 - Reports all DB without any backup from last 30 Days
SET @Days = -1;
--Database with no Backup in the given period
SELECT
SERVERPROPERTY('Servername') AS 'Server_Name',
bs.database_name AS 'Database_Name',
MAX(bs.backup_finish_date) AS 'Last_Backup_Date',
DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) AS 'Backup_Age(Hrs)'
FROM msdb.dbo.backupset bs
WHERE bs.type = @Backup_Type
GROUP BY bs.database_name
HAVING (MAX(bs.backup_finish_date) < DATEADD(DD, @Days, GETDATE()))
UNION
-- Databases with no backup history
SELECT
SERVERPROPERTY('Servername') AS 'Server_Name',
sd.name AS 'Database_Name',
NULL AS 'Last_Backup_Date',
9999 AS 'Backup_Age(Hrs)'
FROM master.dbo.sysdatabases sd
LEFT JOIN msdb.dbo.backupset bs ON sd.name = bs.database_name
WHERE bs.database_name IS NULL AND sd.name <> 'tempdb'
ORDER BY bs.database_name;
SQL Server Restore History Report:
This script can quickly get the restore history from the given SQL Server instance. We can customize the script by passing the specific database name to the parameter @DBName. By default it captures restore information for all databases in the given instance.
/*****************************************************/
/*** Database Restore History Report *****************/
/*** By Default it captures all DB Restore Info ******/
/*** Give a DB name for the parameter @DBName ********/
/*****************************************************/
-- Give a database name to get restore info for a specific DB
DECLARE @DBName VARCHAR(200);
SET @DBName = NULL
SELECT
rh.destination_database_name AS 'Destination_DB_Name',
rh.restore_date AS 'Restore_Date',
rh.user_name AS 'DB_Restored_By',
CASE rh.restore_type
WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END AS 'Type_Of_Restore',
bs.server_name AS 'Baclup_Taken_From',
bs.user_name AS 'Backup_Taken_By',
bs.database_name AS 'Source_DB_Name',
bs.backup_start_date AS 'Backup_Date',
bmf.physical_device_name AS 'File_Path'
FROM msdb.dbo.restorehistory rh
INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE rh.destination_database_name = ISNULL(@DBName,rh.destination_database_name)
ORDER BY Restore_Date DESC;
SQL Server Backup and Restore Status Report:
This script is useful to get the estimated completion time and percentage completed time for currently running backup or restore operation on a given SQL Server instance.
/******************************************************************/
/**** Database Backup / Restore percentage Completion Report *****/
/******************************************************************/
USE MASTER
GO
SELECT
Command AS 'Command',
ds.Text AS 'Query',
start_time AS 'StartTime',
percent_complete AS 'Percentage_Completed',
CAST(((DATEDIFF(s,start_time,GetDate()))/3600)as varchar(10))+' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar(10))+'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60)as varchar(10))+' sec'
AS 'Running_Time',
CAST((estimated_completion_time/3600000)as varchar(10))+' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar(10))+'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar(10))+' sec'
AS 'Estimated_Time_To_Go',
DATEADD(second,estimated_completion_time/1000,getdate())
AS 'Estimated_Completion_Time'
FROM SYS.DM_EXEC_REQUESTS R
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) ds
WHERE
R.Command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG');
