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

  • Is the restore scheduled in the third party app, completely independent of the SQL Agent? – LowlyDBA Oct 8 '18 at 20:42
  • Yes. Thats correct – VAAA Oct 8 '18 at 21:13
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]?
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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