Limit database restores to specific drive locations

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

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

  • 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 give you the shirt off *someone else's* back.

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

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

  • 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 give you the shirt off *someone else's* back.

  • 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 give you the shirt off *someone else's* back.

  • 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)
    Intro to Tally Tables and Functions

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

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

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