Database Recovery Status

Lets say we have a full backup on Sunday along with Incremental once in a day from Monday to Saturday and Log backup every 15 mins. The DR drill is scheduled for Thursday.The Stored Procedure will let us know what sequence will be required to perform a recovery  in case DR Test failed for any reason, this will also check if the backup file still exists or  not.

USE [master] 
GO
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO
 SET ANSI_PADDING ON 
 GO 
 CREATE TABLE [dbo].[CommandLog]
 (
 [ID] [int] IDENTITY(1,1) NOT NULL, [DatabaseName]    [sysname]  NULL,  [SchemaName]       [sysname]  NULL, 
 [ObjectName]      [sysname]  NULL, [ObjectType]      [char](2)  NULL,  [IndexName]        [sysname]  NULL, 
 [IndexType]       [tinyint]  NULL, [StatisticsName]  [sysname]  NULL,  [PartitionNumber]  [int]      NULL, 
 [ExtendedInfo] xml NULL, [Command] [nvarchar](max) NOT NULL, [CommandType] [nvarchar](60) NOT NULL,
 [StartTime] [datetime] NOT NULL, [EndTime] [datetime] NULL, [ErrorNumber] [int] NULL,
 [ErrorMessage] [nvarchar](max) NULL,
 CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 )WITH
 (
 PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
 SET ANSI_PADDING OFF
 GO

Stored Procedure  T-SQL

USE master -- Specify the database in which the objects will be created.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ('usp_DatabaseRecoveryStatusCheck') IS NULL
BEGIN

exec ('CREATE PROCEDURE usp_DatabaseRecoveryStatusCheck as begin select ''Object Not Deployed'' end ')
END
GO

ALTER PROCEDURE [dbo].[usp_DatabaseRecoveryStatusCheck]

@Databases sysname='USER_DATABASES',
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y',
@LoggingLevel int=0 --# 0=NONE, 1=Minimum , 2=FULL
AS
BEGIN

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @DbStartLoop int
DECLARE @DbENDLoop int
DECLARE @Database_Name sysname=NULL
DECLARE @ServerName sysname =@@ServerName
DECLARE @rowcount int

DECLARE @FullDatabaseCheckPointLSN numeric(25,0)
DECLARE @FullDatabaseLastLSN numeric(25,0)

DECLARE @FullDatabaseBackupDate datetime;
DECLARE @FullBackupFiles varchar(MAX);
DECLARE @CheckFileStart int
DECLARE @CheckFileEND int
DECLARE @BackupFileLocation nvarchar(500)
DECLARE @CheckFileExists int
DECLARE @DatabaseMessage nvarchar(MAX) =NULL

DECLARE @Command nvarchar(4000)

DECLARE @xml xml
DECLARE @delimiter varchar(MAX)

DECLARE @CurrentCommand01 nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)
DECLARE @PrintLog nvarchar(max)=''

CREATE TABLE #BackupHistory (database_name varchar(max),
physical_device_name varchar(max),
backup_start_date datetime,
first_lsn numeric(25,0),
last_lsn numeric(25,0),
checkpoint_lsn numeric(25,0),
database_backup_lsn numeric(25,0),
[type] char(1),
servername varchar(500),
RecoveryModel varchar(100),
rm int)

CREATE TABLE #user_database_backup_chain (id int identity(1,1),
dbname varchar(MAX),
file_location varchar(MAX),
backup_datetime datetime,
first_lsn numeric(25,0),
lsn_chain numeric(25,0),
checkpoint_lsn numeric(25,0),
database_backup_lsn numeric(25,0),
file_type char(1),
file_status varchar(10) NULL)
DECLARE @User_database_recovery TABLE (id int ,
dbname varchar(MAX),
file_location varchar(MAX),
backup_datetime datetime,
first_lsn numeric(25,0),
lsn_chain numeric(25,0),
checkpoint_lsn numeric(25,0),
database_backup_lsn numeric(25,0),
file_type char(1),
file_status varchar(10) NULL)

DECLARE @SELECTedDatabases TABLE (DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
SELECTed bit)
DECLARE @tmpDatabases TABLE (ID int IDENTITY,
DatabaseName nvarchar(max),
DatabaseNameFS nvarchar(max),
DatabaseType nvarchar(max),
SELECTed bit,
Completed bit,
PRIMARY KEY(SELECTed, Completed, ID))
----------------------------------------------------------------------------------------------------
--// SELECT databases //--
----------------------------------------------------------------------------------------------------

SET @Databases = REPLACE(@Databases, ', ', ',');
WITH Databases1 (StartPosition, ENDPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS ENDPosition,
SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem
WHERE @Databases IS NOT NULL
UNION ALL
SELECT CAST(ENDPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Databases, ENDPosition + 1), 0), LEN(@Databases) + 1) AS ENDPosition,
SUBSTRING(@Databases, ENDPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, ENDPosition + 1), 0), LEN(@Databases) + 1) - ENDPosition - 1) AS DatabaseItem
FROM Databases1
WHERE ENDPosition < LEN(@Databases) + 1
),
Databases2 (DatabaseItem, SELECTed) AS
(
SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS SELECTed
FROM Databases1
),
Databases3 (DatabaseItem, DatabaseType, SELECTed) AS
(
SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
SELECTed
FROM Databases2
),
Databases4 (DatabaseName, DatabaseType, SELECTed) AS
(
SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
DatabaseType,
SELECTed
FROM Databases3
)
INSERT INTO @SELECTedDatabases (DatabaseName, DatabaseType, SELECTed)
SELECT DatabaseName,
DatabaseType,
SELECTed
FROM Databases4
OPTION (MAXRECURSION 0)

INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, SELECTed, Completed)
SELECT [name] AS DatabaseName,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,
CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
0 AS SELECTed,
0 AS Completed
FROM sys.databases
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
ORDER BY [name] ASC

UPDATE tmpDatabases
SET tmpDatabases.SELECTed = SELECTedDatabases.SELECTed
FROM @tmpDatabases tmpDatabases
INNER JOIN @SELECTedDatabases SELECTedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SELECTedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SELECTedDatabases.DatabaseType OR SELECTedDatabases.DatabaseType IS NULL)
WHERE SELECTedDatabases.SELECTed = 1

UPDATE tmpDatabases
SET tmpDatabases.SELECTed = SELECTedDatabases.SELECTed
FROM @tmpDatabases tmpDatabases
INNER JOIN @SELECTedDatabases SELECTedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SELECTedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SELECTedDatabases.DatabaseType OR SELECTedDatabases.DatabaseType IS NULL)
WHERE SELECTedDatabases.SELECTed = 0

IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SELECTedDatabases) OR EXISTS(SELECT * FROM @SELECTedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')
BEGIN
SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
-- SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Get and loop each database //--
----------------------------------------------------------------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE SELECTed = 1 AND Completed = 0)
BEGIN
SELECT TOP 1 @Database_Name=DatabaseName FROM @tmpDatabases WHERE SELECTed = 1 AND Completed = 0 ORDER BY ID ASC

--Print 'Database :'+@Database_Name

SET @DatabaseMessage = ''
SET @DatabaseMessage =CHAr(13)+ 'Database :'+@Database_Name +CHAR (13)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Populate #BackupInfoHistory Temp Table with Backup Information for each database //--
----------------------------------------------------------------------------------------------------
SET @CurrentCommand01=''
SET @CurrentCommand01='SELECT s.database_name,m.physical_device_name, s.backup_start_date,
s.first_lsn, s.last_lsn,s.checkpoint_lsn,s.database_backup_lsn,
s.[type],s.server_name,s.recovery_model
,row_number() Over(Partition by s.[type] Order by s.backup_finish_date DESC ) rn
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name ='''+ @Database_Name + ''' AND server_name='''+@ServerName+''''
IF (@Execute='Y' OR @Execute='N')
BEGIN
INSERT INTO #BackupHistory
EXEC(@CurrentCommand01)
END

IF @LogToTable='Y'
BEGIN
INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage)
VALUES (@Database_Name,@CurrentCommand01,'SELECT',GETDATE(),GETDATE(),0,'NONE')
END
----------------------------------------------------------------------------------------------------
--// Populate Database Backup History Chain FROM last full backup //--
----------------------------------------------------------------------------------------------------
IF (@Execute='Y' OR @Execute='N')
BEGIN
insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type)
SELECT @Database_Name,physical_device_name,backup_start_date,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,[type]
FROM #BackupHistory
WHERE backup_start_date>=(SELECT top 1 s.backup_start_date FROM msdb.dbo.backupset s Where s.[type]='D' and
s.server_name=@ServerName AND s.database_name = @Database_Name Order by s.backup_finish_date DESC)
and [type]='D'
END

IF @LogToTable='Y'
BEGIN
INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage)
VALUES (@Database_Name,@CurrentCommand01,'SELECT FULL',GETDATE(),GETDATE(),0,'NONE')
END
----------------------------------------------------------------------------------------------------
--// Insert [NULL] values for databases never backedup //--
----------------------------------------------------------------------------------------------------

IF (SELECT COUNT(*) FROM #BackupHistory WHERE database_name=@Database_Name and [type]='D') < 1
BEGIN
insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type) values (@Database_Name,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END
----------------------------------------------------------------------------------------------------
--// Populate Database Incremental Backup History Chain FROM last full backup //--
----------------------------------------------------------------------------------------------------

SELECT @FullDatabaseCheckPointLSN=checkpoint_lsn FROM #user_database_backup_chain
IF (@Execute='Y'OR @Execute='N')
BEGIN
insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type)
SELECT @Database_Name,physical_device_name,backup_start_date backup_start_date,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,[type]
FROM #BackupHistory
WHERE
database_backup_lsn=@FullDatabaseCheckPointLSN and [type]='I'
SET @rowcount=@@RowCount
END

IF @LogToTable='Y'
BEGIN
IF (@rowcount >= 1)
BEGIN
INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage)
VALUES (@Database_Name,@CurrentCommand01,'SELECT DIFF',GETDATE(),GETDATE(),0,'NONE')
END
END
----------------------------------------------------------------------------------------------------
--// Populate Database Log Backup History Chain FROM last full backup //--
----------------------------------------------------------------------------------------------------
SELECT @FullDatabaseCheckPointLSN=MAX(checkpoint_lsn) FROM #user_database_backup_chain
IF (@Execute='Y' OR @Execute='N')
BEGIN
insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type)
SELECT @Database_Name,physical_device_name,backup_start_date,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,[type]
FROM #BackupHistory
WHERE checkpoint_lsn>=@FullDatabaseCheckPointLSN and [type]='L' order by backup_start_date ASC
SET @rowcount=@@RowCount
END

IF @LogToTable='Y'
BEGIN
IF (@rowcount >= 1)
BEGIN
INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage)
VALUES (@Database_Name,@CurrentCommand01,'SELECT LOG',GETDATE(),GETDATE(),0,'NONE')
END
END

----------------------------------------------------------------------------------------------------
--// Check backup files status //--
----------------------------------------------------------------------------------------------------

SET @CheckFileStart=1
SELECT @CheckFileEND=count(*) FROM #user_database_backup_chain
WHILE @CheckFileStart <= @CheckFileEND
BEGIN
SET @CurrentCommand01=''
SELECT @BackupFileLocation=file_location ,@Database_Name=dbname FROM #user_database_backup_chain where id=@CheckFileStart
--print @filepath
--SET @cmd='RESTORE VERIFYONLY FROM DISK='+''''+@filepath+''''
--exec (@cmd)
SET @CurrentCommand01='File status check :'+' EXEC Master.dbo.xp_fileexist '+@BackupFileLocation+' , '+'@CheckFileExists OUT '

SET @DatabaseMessage = ''
SET @DatabaseMessage =CHAr(13)+ISNULL(@CurrentCommand01,'Status unknown') +CHAR (13)

IF (@Execute='Y')
BEGIN
EXEC master.dbo.xp_fileexist @BackupFileLocation , @CheckFileExists OUT
END

IF @LoggingLevel=1 or @LoggingLevel=2 or @Execute='N'
BEGIN
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
END

IF @LogToTable='Y'
BEGIN
INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage)
VALUES (@Database_Name,ISNULL(@CurrentCommand01,'Missing File'),'FILECHECK',GETDATE(),GETDATE(),0,'NONE')
END
IF @CheckFileExists =1
BEGIN
update #user_database_backup_chain set file_status='Exists' where id=@CheckFileStart
END
ELSE
BEGIN
update #user_database_backup_chain set file_status='Missing' where id=@CheckFileStart
END
SET @CheckFileStart=@CheckFileStart + 1
END
-- SELECT * FROM #user_database_backup_chain
insert into @User_database_recovery SELECT * FROM #user_database_backup_chain
truncate table #user_database_backup_chain

SET @CheckFileStart=1

-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE SELECTed = 1
AND Completed = 0
AND DatabaseName = @Database_Name

END

----------------------------------------------------------------------------------------------------
--// Drop Temp Tables //--
----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects where name like '#BackupHistory%')
BEGIN
DROP TABLE #BackupHistory
END
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects where name like '#user_database_backup_chain%')
BEGIN
DROP TABLE #user_database_backup_chain
END
----------------------------------------------------------------------------------------------------
--// Show Status //--
----------------------------------------------------------------------------------------------------
IF @Execute='Y'
BEGIN
IF EXISTS (SELECT dbname ,'No Recovery Possible.No Backup found.' FROM @User_database_recovery where file_location IS NULL)
BEGIN
SELECT DISTINCT dbname ,'No Recovery Possible.No Backup found.' FROM @User_database_recovery where file_location IS NULL
END
IF EXISTS (SELECT dbname , 'Point in time Recovery not possible.Backup file missing.' RecoveryStatus FROM @User_database_recovery where file_location IS NOT NULL and file_status='Missing')
BEGIN
SELECT DISTINCT dbname , 'Point in time Recovery not possible.Backup file missing.' RecoveryStatus FROM @User_database_recovery where file_location IS NOT NULL and file_status='Missing'
END
END
----------------------------------------------------------------------------------------------------
--// Log information //--
----------------------------------------------------------------------------------------------------
IF @LoggingLevel>=0
BEGIN
SELECT id,dbname,file_location,backup_datetime,file_type,file_status FROM @User_database_recovery
END

SET NOCOUNT OFF
END
--------------------------------------------------------------------------------------------------

image

Leave a Reply

Your email address will not be published. Required fields are marked *