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.

3
  • 2
    Is your colleague restoring the database as SA or is he\she using a different login?
    – joeqwerty
    May 19, 2010 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, 2010 at 7:08
  • 1
    If everyone uses the SA account won't your colleague just be able to undo anything you do?
    – Shane
    May 20, 2010 at 17:14

3 Answers 3

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?

3
  • 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, 2010 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, 2010 at 17:59
  • 1
    ...and cattle prods. I always love the use of cattle prods in these kinds of discussions. :) May 21, 2010 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?

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .