Limit database restores to specific drive locations

Question

Limit database restores to specific drive locations

DrJogalog

SSC Veteran

Points: 283
More actions
May 14, 2021 at 1:12 pm

#3883441

As there isn't an option to use a DDL trigger on a RESTORE query, is anyone aware of how I can limit restoration of databases where the MDF and LDF files are forced to a specific set of directories (or prevent restoration from specific directories)? The requirement is force the database files into C:\folder1\subfolder1\ or C:\folder1\subfolder2\ and not allow a restore into C:\folder1\. I was hoping I could prevent the restore into the parent directory (which is the default data and log directory for the SQL Server).
Any suggestions or advice would be greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 1

Not sure if C: is the correct location to put SQL files as you are then sharing the disk I/O with the OS.

BUT one way you could do this would be with the windows file permissions. You can allow read/write operations on subfolder1 and subfolder2, but only allow read operations on folder1. This way, if someone tried to restore a database to folder1, they would get a permission denied error. This would affect ALL files in folder1 though, not just newly created ones. So if you had master (for example) in folder1, you would likely get errors starting the instance.

Apart from that, I am not aware of any way to restrict it.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

If a RESTORE DATABASE triggers a "CREATE DATABASE" event, and I'm not sure about that although it seems logical, then you could use a DDL trigger to check for the path in the TSQL used to create the db, and cancel the command if it had invalid path(s).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

DrJogalog SSC Veteran Points: 283 More actions · Answer 3

Thanks for the reply, Brian. The paths used are purely as an example and not used in production.

Yes, folder permissions are possibly an option, but I wanted to try and control it through a cleaner fashion using SQL and return a RAISEERROR/THROW message with some context if possible. I am sort of resigned to the fact it may have to be done with some extended security on the directories but thought I'd throw it out there to see if anyone else had a solution.

Thanks again.

DrJogalog SSC Veteran Points: 283 More actions · Answer 4

I don't believe it does but I'll test and profile just in case. Thanks.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 5

Rats! Apparently RESTORE does not trigger a CREATE DATABASE event and there is no RESTORE DATABASE event, at least from what I've found so far.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 6

I guess you have to fall back on querying the msdb.dbo.restorehistory table every nn minutes and looking up the details on all newly restored dbs, since the last check nn minutes ago. Then, if a db doesn't follow the rules, DROP the db. Much less clean.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

I don't suppose removing privs from users and forcing them to use a stored procedure to do restores is an option?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 8

I would lean towards Jeff's idea here if possible. There is an audit event on restore start, but that won't stop anything. At best, you'll get a chance to kill it.

Mostly I'd handle this administratively. Everyone use a proc /PoSh script/etc. that doesn't do anything we don't want. If you can't do that, we remove rights from you and you get to go ask someone else to help you.

https://stackoverflow.com/questions/952611/is-it-possible-to-have-a-database-trigger-on-restore-in-sql-2005-or-2008