Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

Question

Is it possible to have SQL Server 2008 R2 Standard automatically execute a stored procedure in any given database that is restored or attachced to the instance?

I've got close to a solution by creating a server level trigger that executes a stored procedure in a given database after the DDL event CREATE_DATABASE or ALTER_DATABASE is fired. Unfortunately, this does not work for databack backup restores.

To elaborate, we have a 'clean up' stored procedure that exists in every database that we restore and I'm looking for a way to have this get executed automatically whenever a backup is restored to the instance.

Googling has pointed me to configuring either Audits or Policies in SQL Server to get this functionality, but these these features are quite overwhelming at first glance, so I can't tell if Audits or Polices are the avenue to start investigating.

Answer 1

Does it have to execute immediately after the restore finishes, or can it be briefly delayed? One idea I had is to have a job that runs every minute, and checks for the Audit Backup/Restore Event in the default trace.

DECLARE @fn VARCHAR(MAX);

SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) 
  + CHAR(92) + 'Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    DatabaseName,
    StartTime, 
    TextData
FROM sys.fn_trace_gettable(@fn, DEFAULT)  
WHERE EventClass = 115
AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

You could store the cleanups you've already done based on StartTime, and even limit the trace query that runs every minute (or whatever your acceptable delay threshold is) to only look at StartTime values greater than the last row you pulled, or the last time the job ran, whichever is less.

Answer 2

The database_started Extended Event is fired after a database is restored.

Create an event session that captures the database_id field with a predicate that looks for %RESTORE% in the sql_text field (note: I believe this is sufficient -- you'll want to test for yourself).

I'm not familiar enough with Extended Events to tell you the best way of how to respond to the event. It would be nice if you could fire a stored procedure directly; I don't know if that's possible, though. You can certainly poll the event buffer, however, and that is preferable to scanning the default trace -- not only for performance reasons, but if the server is very busy and the polling interval is too long, you could end up missing events outright. With the Extended Events method, missing an event would be very unlikely.

Answer 3

As you can see by the list of DDL events, a DDL trigger can't be explicit enough for your requirement.

I'd recommend writing a PowerShell script to do the restore or attach and then execute the stored procedure immediately following. Mind you, your shop will have to be instructed to use this instead of a typical restore or attach.

Unfortunately I don't believe there's a built-in way to accomplish this.

Answer 4

How are you running your backups? If you are just looking to run the SP after your backup you can set it up as another task in the job, if you are using them, or maintenance plan.

So you can set up Auditing to write to the event log and then create an alert that will execute a job. It seems convoluted but it will do what you are asking.

Take a look at the following code:

--Create the Server Audit
USE master
GO
CREATE SERVER AUDIT BackupTrap
TO APPLICATION_LOG
WITH (QUEUE_DELAY = 0, ON_FAILURE = CONTINUE)
GO

--Turn the Audit On
ALTER SERVER AUDIT BackupTrap
WITH (STATE = ON)
GO

--Create the Database Audit Specification
USE AdventureWorks2012
GO
CREATE DATABASE AUDIT SPECIFICATION BackupTrapAdventureWorks
FOR SERVER AUDIT BackupTrap
    ADD (BACKUP_RESTORE_GROUP)
WITH (STATE = ON)
GO

--Create the job to run
USE msdb
GO
EXECUTE dbo.sp_add_job
    @job_name = N'BackupAlertJob'
GO

EXECUTE dbo.sp_add_jobserver
    @job_name = N'BackupAlertJob'
GO

EXECUTE dbo.sp_add_jobstep
    @job_name = N'BackupAlertJob',
    @step_name = N'RunSP',
    @subsystem = N'TSQL',
    @command = N'EXECUTE dbo.MyStoredProcedure',
    @database_name = N'AdventureWorks2012'
GO

Answer 5

How are you running your backups? With Commvault you can set up an automated restore job and have it run pre and post restore scripts which can be SQL or powershell. I do this all the time to run DDL for version differences and to backup and restore permissions when restoring between environments.

I believe Netbackup can other products have similar functionality

If you use SQL, just add it as a step to the job that restores the database