September 19, 2008 at 7:57 am
I have a database I would like to prevent sysadmins from restoring to. The database should never be restored on this paticular server and some DBAs with SA permissions have accidentally overwritten the database. I know there are some DML triggers you can use to prevent a drop table, etc.
Anyone out there know of a clever way to prevent a restore of a database?
September 19, 2008 at 8:34 am
"accidentaly overwritten the database"... I love the way you phrased it. 😀
Well... that's why I keep in my desk a bottle of brandy and my old-n-good original point-and-click interfase(1); you have to apply them in the right order and the accident would never happen again. 😎
(1) Colt 45: the original point-and-click interfase
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2008 at 8:38 am
Luckily this is a test server, but it does cause others quite a bit of work if it is overwritten.
September 19, 2008 at 8:41 am
You cannot restore to a database if there is an open connection to it. You could simply create a job that executes when the SQL Agent starts. Put in a single job step that loops endlessly and calls WAITFOR
September 19, 2008 at 8:45 am
Let me quote Mr. Ron White when he says "you can't fix stupid"; I would add you can't prevent stupidity because there is not such a thing as a vaccine for it. The bottom line is, if you have a stupid around sooner or later he/she would manage to destroy the environment... accidentaly.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2008 at 8:56 am
I'm going to use a SQL job to do that loop and keep a connection open and an alter database trigger that will prevent them from putting the database in single_user mode for a restore.
Thanks for the advice.
Can't fix stupid, but you can attempt to mitigate it!
September 19, 2008 at 9:04 am
It's pretty amazing how often an idiot will find a clever way around your safeguards.
The other result will probably be you spending four hours wondering why you cannot restore the database after you have forgotten about creating this job six months from now.
September 19, 2008 at 9:10 am
Indeed, however, they all know not to restore this database and hopefully this will remind them. I'm also adding text to the alter database trigger to remind us that we also have the job running to prevent the restore. Problem is out of about 20 instances, 3 need to have this protection, that is why these accidents happen.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply