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.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

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

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