0

I have a SQL Database that is being restored every day from a Production SQL server.

The restore is being done by a third party application. Once the DB is restored I need to set some specific user permissions and also run other script and I would like to do that exactly after the restore process has been complete.

Is there a way I can execute a script after a DB has been restored?

Thanks

Improve this question
3
  • Is the restore scheduled in the third party app, completely independent of the SQL Agent?
    – LowlyDBA - John M
    Commented Oct 8, 2018 at 20:42
  • Yes. Thats correct
    – VAAA
    Commented Oct 8, 2018 at 21:13
  • @VAAA, which kind of 3rd party application we are speaking about? Did you check if application scheduler has a possibility to include a kind of postpone scripts as an additional step?
    – Mainecoon21
    Commented Jan 17, 2023 at 12:15

2 Answers 2

Reset to default
0

Looks like one cannot set up trigger on RESTORE DATABASE event

I can think of

  1. Set up a regular SQL job to check/add permissions if needed
  2. As this link, add a TRIGGER on [msdb].[dbo].[restorehistory]?
Improve this answer
0

You must be having a scheduled job which does database restoration from Production to your server meaning you will have specified time and you would also be knowing based on historical data that how much time does this process take. you may create another scheduled task inside SQL Server as SQL Job or Windows scheduled task which would include user permission script as well as other script.

You will have only one risk - if restoration fails from production then you can't set dependency to this job as the first one is triggered from third party application. In any case, granting permission has no consequence however don't know your script, it might have some consequences.

This would reduce overhead for you and it will become part of your scheduled job and when you start working, all is set for you every day. I hope this helps.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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