How to acknowledge DB has been restore?

Question

How to acknowledge DB has been restore?

SQL_Comp

SSChasing Mays

Points: 646
More actions
March 6, 2012 at 1:00 pm

#258132

Hi All,
Is there any way to identified SQL server database has been refresh ? Without running query on restorehistory table or scheduling the SQL agent job?
As i know we can't create trigger on system objects....Is there any internal tool or command to find Database has been refresh...:-)
🙂 🙂 🙂
Amey ghag
<a href='http://' class='authorlink' target='_blank'></a>

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

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

Ant-Green SSC Guru Points: 113509 More actions · Answer 1

are you restoring through script? If so do you have operators and dbmail configured? You could but a send mail or notify operator tax command after the restore to email you

restore database DB1 from disk = 'c:\db1bak'

go

exec msdb.dbo.sp_send_dbmail

or

exec msdb.dbo.sp_notify_operator

go

SQL_Comp SSChasing Mays Points: 646 More actions · Answer 2

Hi Anthony,

I agree we can configure to send mail using operator, but Internally like (sys.dm_exec_requests) when percentage_complete = 100 we need to wait for 10 second to verifying the database and intimidate we have to process some task on the database w/o users connect to DB.

🙂 🙂 🙂
Amey ghag
<a href='http://' class='authorlink' target='_blank'></a>

Lowell SSC Guru Points: 323518 More actions · Answer 3

It depends on what you want to do;

it sounds like creating a event notification for AUDIT_BACKUP_RESTORE_EVENT might work, depending on what you are doing.

I've played with that for automatically running a procedure which runs an orphan-repair script, and also for adding a default set of roles to a database that was restored, neither of those needed exclusive database access though.

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!

Ant-Green SSC Guru Points: 113509 More actions · Answer 4

you could set the database to off-line / read-only after the restore which will stop the users logging in.

then set the database to single user, then read-write and run in your extra processes

if your watching the restore in ssms you could add the with stats clause which will print a message to the window at whatever stat interval you specify