Home > Backup > Database Recovery Status

Database Recovery Status

The stored procedure is written to check the recovery status of the Databases. With Full , Incremental and Log back sequences.

 

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 /****** Object: Table [dbo].[CommandLog] Script Date: 1/5/2015 12:05:17 AM ******/ 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 GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE 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 top 1 @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

Categories: Backup
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: