0

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.

André
  • 123
  • 4
  • 2
    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 07:08
  • 1
    If everyone uses the SA account won't your colleague just be able to undo anything you do? – Shane May 20 '10 at 17:14

3 Answers3

4

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?

mfinni
  • 36,144
  • 4
  • 53
  • 86
  • 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. – Shane May 20 '10 at 17:59
  • 2
    +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
  • 1
    ...and cattle prods. I always love the use of cattle prods in these kinds of discussions. :) – John Gardeniers May 21 '10 at 22:00
0

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?

Peter Schofield
  • 1,639
  • 9
  • 11
0

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...

doug_w
  • 236
  • 1
  • 4