Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

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('\', REVERSE([path]))+1) 
  + '\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.

share|improve this answer
    
Very slick, good thinking. +1 –  Thomas Stringer Apr 26 '12 at 17:24
    
A slight delay would be acceptable. I'm going to try this out. –  Matthew Ruston Apr 26 '12 at 17:25
    
Note that I haven't confirmed that in all cases the event is actually finished before the trace row is written. I suspect that it is a post-event write but I am suspicious only because EndTime never seems to be populated. It may be the case that you should try to connect to the database and, if you can't, skip it and don't document this restore event, but try again the next time the job runs. I'd try and validate but on my local VMs I only have SSDs but they don't have the disk space to make a backup take long enough to observe the behavior accurately. :-) –  Aaron Bertrand Apr 26 '12 at 17:58
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
    
A restore job that captures this is a good idea. However, I wonder if that is a complete solution given the OP wants to capture "any" restore event. –  Nick Chammas Apr 26 '12 at 15:55
    
Being able to execute an arbitrary stored procedure after any restore event completes is really what we are after. A valid suggestion nonetheless. –  Matthew Ruston Apr 26 '12 at 16:41
    
I removed my second answer and edit the above to include the information. Thanks @Shark for the tip on that one. Totally didn't think about doing that. –  jgardner04 May 1 '12 at 19:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.