Sql-server – Automatically Execute Stored Procedure After Any RESTORE DATABASE Event

sql serversql-server-2008-r2

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.

Best Answer

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.

Related Question