Post reply

Run a Query on a Database Restore

  • November 30, 2012 at 6:33 pm

    #271453

    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!

  • December 3, 2012 at 5:43 am

    #1564814

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 3, 2012 at 6:55 am

    #1564859

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 3, 2012 at 7:04 am

    #1564865

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply