Run a Query on a Database Restore

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Run a Query on a Database Restore

Run a Query on a Database Restore

Rate Topic

Display Mode

Topic Options

Author

Message

lisa_stlaurent

Posted Friday, November 30, 2012 6:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 6:33 PM
Points: 1, Visits: 10

Hi,

I'm looking for how I can have a query run against a database when it is restored. Basically, I have multiple copies of the same db structure with some default data that when restored, I would like to run some queries to adjust configuration. There must be a way to setup a trigger to run a Stored Procedure or a set query but I am having trouble finding documentation.

Can anyone point me in the right direction?

Thanks in advance!

Post #1391613

Grant Fritchey

Posted Monday, December 03, 2012 5:43 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:14 PM
Points: 13,381, Visits: 25,174

Can you just build the query into the process that does the restore?

If not, you'll need to have a T-SQL statement with a TRY/CATCH that you can run, probably on a schedule from SQL Agent. Then, if it can USE the database, it runs the query, if not, it errors out until next time. Not sophisticated, but it will probably work.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1391867

Lowell

Posted Monday, December 03, 2012 6:55 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738

I'm sure you have to use extended events, specifically on the
AUDIT_BACKUP_RESTORE_EVENT

I did something as a prototype that did a few things on my dev server;
if you restored a database, then it added roles if they did not exist, and another one that looked for and fixed orphaned users.

so when a database gets restored a stored procedure gets executed with my desired code, with references to the database in question.

this article By Jonathan Kehayias, 2009/12/22 was my starting point that i added code to.

http://www.sqlservercentral.com/articles/Event+Notifications/68831/

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1391918

Grant Fritchey

Posted Monday, December 03, 2012 7:04 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:14 PM
Points: 13,381, Visits: 25,174

Much better than my approach. I keep forgetting to check Extended Events for solutions to problems like these. Thanks for posting Lowell.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1391925

« Prev Topic | Next Topic »

Permissions