SQL Server – Get Database Backup History for Crash Recovery

It is not easy to find out right backups for restore activity when people are watching you during database crash situation. So I have created below TSQL script that would give backup history for single or multiple database(s) to perform latest database restore, or Point-In-Time recovery.

/*	Name:			Ajay Dwivedi
	Created Date:	18-Apr-2018
	Purpose:		Script to provide backup for Database Restore
*/
SET NOCOUNT ON;
DECLARE @dbName VARCHAR(125),
		@backupStartDate datetime,
		@stopAtTime datetime;
DECLARE @SQLString nvarchar(2000);  
DECLARE @ParmDefinition nvarchar(500);  
SET @dbName = 'Cosmo';
--SET @stopAtTime = '2018-04-16 23:40:00'

IF OBJECT_ID('tempdb..#BackupHistory') IS NOT NULL
	DROP TABLE #BackupHistory;
CREATE TABLE #BackupHistory
(
	[BackupFile] [nvarchar](260) NULL,
	[BackupTypeDescription] [varchar](21) NULL,
	[ServerName] [char](100) NULL,
	[UserName] [nvarchar](128) NULL,
	[database_name] [nvarchar](128) NULL,
	[DatabaseCreationDate] [datetime] NULL,
	[BackupSize] [numeric](20, 0) NULL,
	[FirstLSN] [numeric](25, 0) NULL,
	[LastLSN] [numeric](25, 0) NULL,
	[CheckpointLSN] [numeric](25, 0) NULL,
	[DatabaseBackupLSN] [numeric](25, 0) NULL,
	[BackupStartDate] [datetime] NULL,
	[BackupFinishDate] [datetime] NULL,
	[CompatibilityLevel] [tinyint] NULL,
	[Collation] [nvarchar](128) NULL,
	[IsCopyOnly] [bit] NULL,
	[RecoveryModel] [nvarchar](60) NULL
) ;

/* Build the SQL string to get all latest backups for database. */  
SET @SQLString =  
     N'SELECT	BackupFile = bmf.physical_device_name,
		CASE bs.type WHEN ''D'' THEN ''Database'' WHEN ''I'' THEN ''Differential database'' WHEN ''L'' THEN ''Log'' ELSE NULL END as BackupTypeDescription,
		LTRIM(RTRIM(CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')))) as ServerName,
		UserName = bs.user_name,
		bs.database_name,
		DatabaseCreationDate = bs.database_creation_date,
		BackupSize = bs.backup_size,
		FirstLSN = bs.first_lsn, 
		LastLSN = bs.last_lsn, 
		CheckpointLSN = bs.checkpoint_lsn,
		DatabaseBackupLSN = bs.database_backup_lsn,
		BackupStartDate = bs.backup_start_date,
		BackupFinishDate = bs.backup_finish_date,
		CompatibilityLevel = bs.compatibility_level,
		Collation = bs.collation_name,
		IsCopyOnly = bs.is_copy_only,
		RecoveryModel = bs.recovery_model
FROM	msdb.dbo.backupmediafamily AS bmf
INNER JOIN msdb.dbo.backupset AS bs ON bmf.media_set_id = bs.media_set_id
WHERE	database_name = @q_dbName
	AND	bs.backup_start_date >= @q_backupStartDate
AND		(	@q_stopAtDate IS NULL OR bs.backup_start_date < @q_stopAtDate -- All the TLogbackups will be covered
			OR 
			(	bs.backup_start_date = (	SELECT MIN(i.backup_start_date) as LastTLogToRestore 
											FROM msdb..backupset as i WHERE i.database_name = bs.database_name AND i.type = ''L'' 
											AND i.backup_start_date >= @q_stopAtDate 
										)
			)
		)';  

SET @ParmDefinition = N'@q_dbName varchar(125), @q_backupStartDate datetime, @q_stopAtDate datetime'; 
  
DECLARE databases_cursor CURSOR LOCAL FORWARD_ONLY FOR 
		--	Find latest Full backup for each database
		SELECT MAX(bs.backup_start_date) AS Latest_FullBackupDate, database_name
		FROM msdb.dbo.backupmediafamily AS bmf INNER JOIN msdb.dbo.backupset AS bs 
		ON bmf.media_set_id = bs.media_set_id WHERE bs.type='D' and is_copy_only = 0
		AND (@dbName IS NULL OR LTRIM(RTRIM(@dbName)) = '' OR @dbName = database_name)
		AND (@stopAtTime IS NULL OR LTRIM(RTRIM(@stopAtTime)) = '' OR CAST(bs.backup_start_date as datetime)< @stopAtTime)
		GROUP BY database_name;

OPEN databases_cursor
FETCH NEXT FROM databases_cursor INTO @backupStartDate, @dbName;

WHILE @@FETCH_STATUS = 0 
BEGIN
	BEGIN TRY
		--SELECT	[@backupStartDate] = @backupStartDate, [@stopAtTime] = @stopAtTime;
		--PRINT	@SQLString;
		--	Find latest backups
		INSERT #BackupHistory
		EXECUTE sp_executesql @SQLString, @ParmDefinition,  
							  @q_dbName = @dbName,
							  @q_backupStartDate = @backupStartDate,
							  @q_stopAtDate = @stopAtTime; 
	END TRY
	BEGIN CATCH
		PRINT ' -- ---------------------------------------------------------';
		PRINT ERROR_MESSAGE();
		PRINT ' -- ---------------------------------------------------------';
	END CATCH
		
	FETCH NEXT FROM databases_cursor INTO @backupStartDate, @dbName;
END

CLOSE databases_cursor;
DEALLOCATE databases_cursor ;

IF EXISTS (SELECT * FROM #BackupHistory)
BEGIN
	SELECT	[@stopAtTime] = ISNULL(@stopAtTime,GETDATE()), *
	FROM #BackupHistory as bh
	ORDER BY BackupStartDate;
END
ELSE
	SELECT 'Check if wrong database name is provided' as [Error Message]

Below is the sample output:-

Friends! Please feel free to correct me by comments. If you liked the article, do Like, Share & Comment. Happy Coding 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.