Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Run a Query on a Database Restore Expand / Collapse
Author
Message
Posted Friday, November 30, 2012 6:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, December 3, 2012 5:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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 Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1391867
Posted Monday, December 3, 2012 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:35 PM
Points: 12,962, Visits: 32,498
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
Posted Monday, December 3, 2012 7:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:22 AM
Points: 14,205, Visits: 28,534
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 Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1391925
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse