Restore from database snapshot not logged in msdb

Few days back I came across a very interesting scenario where an extremely large environment (many hundreds of SQL Services instances with thousands of databases) that is managed by a relatively small team of DBAs finds it very challanging to identify if a user database has been restored from database snapshot. There is no information logged anywhere in the msdb database about this dataabase restore activity and the only place where you will find this information is SQL Server errorlog. It is not uncommon for required backups to either be missed or to fail undetected. When a successful backup is not taken following certain events (i.e. a database restore or the breaking of a log chain) DBA's ability to meet point-in-time recoverability SLA could be compromised.

Moreover in case you have developed an automated process to detect such situations and to initiate the necessary backups to re-establish reliable recoverability. It's quite obvious, you may be defaulting all such forced backups to generate FULL database backups, where in some cases a DIFFERENTIAL would suffice. This is necessary because there is no information logged in msdb.dbo.restorehistory to detect restores from database snapshots. If you had a restore from snapshot record to rely on, you would then know when a differential backup would suffice. This is particularly important when dealing with large databases for which backup space and time are significant concerns. The only place you will find any indication of these restores occurring is within the SQL Server errorlog.

In short, the core requirement that comes up is how to log this information about database restore from database snapshot in msdb.dbo.restorehistory table

Cause: All the database restore information is by default logged in the msdb.dbo.restorehistory table except the restore from database snapshot. The reason being msdb.dbo.restorehistory table has a FOREIGN KEY relationship with msdb.dbo.backupset table which prevents the ability to write the restore from database snapshot record due to lack of backup.

Now the question is: Is there any way to write this information about database restore/revert from database snapshot into the some parallel/user table which doesnot contain this FOREIGN KEY dependency?


The answer is there is no straightforward way to log this information in any of the system tables in msdb database but in case you have any such requirement, you can follow the below workaround


Workaround: To workaround the problem to meet the core requirement to log the information of database restore/revert from database snapshot into a table in msdb database, follow the below steps:

a) Run the Below script from New Query Window in SSMS

<Sample Script Template>

-- ******** Sample Script Tempalte to track Database Revert/Restore from Snapshot  **********

-- Press CTRL+SHIFT+M to replace the template parameters
-- *** Kindly ensure that "Replace tokens for all job responses to alerts" must be enabled on SQL Server agent "Alert System" page ****

-- ******** ***************************** ***********

--  1.     Create a logging table in MSDB to store information when a snapshot restore is performed.

use [msdb]
go
CREATE TABLE [dbo].[<Tracking Table, sysname,>]
(
      [id] [int] IDENTITY(1,1) NOT NULL,
      [Date] [varchar](50) NOT NULL,
      [Time] [varchar](50) NOT NULL,
      [Server] [varchar](50) NULL,
      [Instance] [varchar](50) NULL,
      [Error] [int] NULL,
      [Severity] [int] NULL,
      [Message] [nvarchar](1000) NULL,
      [DatabaseContext] [varchar](50) NULL,
CONSTRAINT [PK_<Tracking Table, sysname,>] 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]

-- 2.     Create a SQL Agent job to execute when the alert is fired. This job uses SQL-Agent tokens to pull up the information from the ALTER to log into the table we created in MSDB.

USE [msdb]
GO
/****** Object:  Job [logsnaprestore]    Script Date: 01/07/2012 03:02:18 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/07/2012 03:02:18 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'<Enter Job Name, sysname,>',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=0,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'No description available.',
            @category_name=N'[Uncategorized (Local)]',
            @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [1-LogIntoMsdb]    Script Date: 01/07/2012 03:02:18 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1-LogIntoMsdb',

            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'INSERT INTO [msdb].[dbo].[<Tracking Table, sysname,>]
           ([Date]
           ,[Time]
           ,[Server]
           ,[Instance]
           ,[Error]
           ,[Severity]
           ,[Message]
           ,[DatabaseContext])

SELECT
''$(ESCAPE_SQUOTE(DATE))'',
''$(ESCAPE_SQUOTE(TIME))'',
''$(ESCAPE_SQUOTE(A-SVR))'',
''$(ESCAPE_SQUOTE(INST))'',
''$(ESCAPE_SQUOTE(A-ERR))'',
''$(ESCAPE_SQUOTE(A-SEV))'',
''$(ESCAPE_SQUOTE(A-MSG))'',
''$(ESCAPE_SQUOTE(A-DBN))''
GO
GO
GO',
           @database_name=N'msdb',
           @flags=4

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

-- 3. Create a SQL Agent Alert for MSG 1843.

USE [msdb]
GO
declare @jobid uniqueidentifier
select @jobid = job_id from msdb..sysjobs where name = N'<Enter Job Name, sysname,>'

EXEC msdb.dbo.sp_add_alert @name=N'<Alert Name, sysname,>',
            @message_id=1843,
            @severity=0,
            @enabled=1,
            @delay_between_responses=0,
            @include_event_description_in=0,
            @category_name=N'[Uncategorized]',
            @job_id=@jobid

</Sample Script Template>

b) In order to make use of tokens specified in the below script, Kindly ensure that “Replace tokens for all job responses to alerts” is enabled. To verify the same, right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens. For more details refer to http://msdn.microsoft.com/en-us/library/ms175575.aspx

Note: You need to restart the SQL Server agent service after enabling “Replace tokens for all job responses to alerts” on SQL Server agent “Alert System” page.

Now with above workaround in place, whenever you perform a restore/revert of your database from database snapshot, information will be logged in the RESTORESNAPSHOTHISTORY user table in MSDB database

I hope you could have found this interesting, please feel free to comment or share your thoughts..!

Gurwinderjit