Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to prevent a database from being restored with a DDL trigger or something?

The background is that I would like to prevent restoring a database on a test server by a colleague. So far I had a look a DDL trigger but didn't find the right event to react on the restore action.

share|improve this question
Is your colleague restoring the database as SA or is he\she using a different login? – joeqwerty May 19 '10 at 17:11
As this is just a test server everybody is using the SA account. So I can't manage this by setting up permissions for everyone/every DB. – André May 20 '10 at 7:08
If everyone uses the SA account won't your colleague just be able to undo anything you do? – Sean Howat May 20 '10 at 17:14

Training. Emails. Warnings. Yelling. Complaints to management. Fisticuffs.

Most of these can be part of a proper change-management process.

Address the root cause. Why would someone else be restoring something that you don't want to be restored. Do you need a separate copy of this DB? Does he need a separate copy?

share|improve this answer
I was thinking the same thing. Trying to apply obscure restrictions on the database isn't really fixing the issue, just preventing it from happening. – Sean Howat May 20 '10 at 17:59
+1 - if he is not supposed to restore, pulling him to his manager and HR is the only way to go. – TomTom May 20 '10 at 17:59
...and cattle prods. I always love the use of cattle prods in these kinds of discussions. :) – John Gardeniers May 21 '10 at 22:00

Have you thought about keeping constant connections to the database? It wouldn't prevent them, but it might slow them down....

What about something like keeping a database snapshot on the database in question? I'd guess you'd need to drop the snapshot before you can do the restore..... if not a snapshot, replication/log-shipping etc?

share|improve this answer

As they are logging in as sa this will be a futile effort. You could however fill up the disks with other data and log files. Terrible idea, but you asked...

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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