Checking if database restore completed

Question

We got a nightly backup/restore managed by another source. And we need to execute an SSIS package right after restore.

At the moment, all the processes are executed in a timely fashion by estimating how long every step can take which is very error prone.

Question is, how can i check if restore finished and execute the SSIS package?

NOTE: backup/restore is executed cannot be done with SSIS.

Answer 1

Well I don't know of a perfect solution but you could try the following.

SELECT DATABASEPROPERTYEX ('DB_NAME', 'STATUS')

That'll give you the status of the database, "RESTORING" if it is still restoring. You could create a recurring job in the Job Agent, which is set to run, after the restoration has started. When the database is done restoring the status will be "ONLINE" and you can execute the SSIS package from SQL, and the Job ends.

I don't know of an elegant solution but hope it helped.