Restore from Prod to Dev

  • I am looking a way to restore production data to our dev and QA environments for user database.  Instead of manual restores, I would automate using a sql Agent Job so that developers can run on demand. So grant permission to that particular agent job to run manually. The database should not lose user accounts or logins after the restore from prod backup. The backups would have data and time stamp so the script should pickup the new file every time the Job is run to restore. Has anyone implemented this? Do you see any issues with this solution? Thanks in advance!

  • yes... but....

    DO NOT restore directly from prod to any other environment. where you do need to have production data always setup a process on another server (restricted, to be treated as production, but with a developer license) where you have a process to replace and/or remove any PII/GDPR data.

    After you do the "cleaning" then you create backup files that can be made available for other environments to restore on demand.

    For the restore - how will the job know which db to restore to? although you could have a default job working like you said that always restores to the same DB the developer may wish to restore to another db name.

    one possible way, which I have implemented on my shop is to have a small web app which lists the available backups and available servers/disks/space available and allows the "user" to restore a particular backup onto a particular server/database.

  • Great. Curious how you implemented that? Would that fall into big project?

    On a side note, the Microsoft SQL Server database role SQLAgentUserRole apparently lets a member create/delete SQL Agent jobs along with viewing jobs, job steps, and results. I would just like to grant user a particular Agent job with execute rights only and not allow to view or execute any other jobs on that particular server. Is that possible?

  • Would not fall on a big project - small web page, 1 or 2 "pages" so it is something that even a junior Web dev can do.

     

    for starting the job - you can if you follow the instructions here 

    adapt to have a SP that starts the specific job and that job alone and it will work fine.

    there may be other ways but I'm not aware.

  • Yes, but several caveats. The problem with the msdb operator roles is that they grant rights to the user to do these things on ALL Agent Jobs, not only the ones you want them to be able to run.

    One way around this it to assign the user who will be running the job as Owner of the job, because the owner will be able to run the job regardless of whether they are in those groups, and will not be allowed to run jobs the user doesn't own.  This is fine if it will only be one individual, but if it is a group of people you cannot assign an AD Group as an owner.

  • Thanks Dan! So once i change the owner of the job to one individual, does that mean he can able to view and execute that particular Job only? However, i have group of people.

  • did you look at the instructions link I supplied? that would allow you to do it without granting permissions directly to the user

  • One last thing, you noted "The database should not lose user accounts or logins after the restore from prod backup". Logins are an instance (server) level object, so database restores outside Master don't affect these. However, users are database objects. When you restore a database, you get the users in the database. If you have dev/test users, they need to be reset/recreated and mapped to logins. You can do this in the stored procedure that runs the job.

     

  • Frederico, i looked at link and it worked for DBCC. However, when i try for restoring the database from backup file share location and i am using sys.xp_cmdshel and then trying to take the database to single user mode with Alter command when the database is in use and use master doesn't allow within the store proc.  So i am testing with sql account and i get below error. I am still working on it.

    The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

     

  • link is to allow you to give permissions to start a job - it has nothing to do with what the job does afterwards.

    so separating the 2 things - did you manage to successfully implement the instructions from the link as a way to allow a user to start a job that he does not own, and while he has to other permission giving him access to start jobs.

    for the job itself if it is failing you need to give a lot more details than that including posting the code.

  • I was thinking of requesting generic AD account for testing to run the restore Job with minimal rights instead of my own account, since i am part of the Sysadmin group and the person who will run this Job will not have sysadmin rights. So that way i can identify the errors and fix them as i see and test the process thoroughly. Is that sounds reasonable to you?

  • Yes, I'd certainly use a separate account for this. Should be able to be a backupadmin and then perhaps file system rights if there is a copy involved.

  • Thanks!So now I am trying to build the restore Job with minimum rights?Before restore trying to take db in single user in case any open connections.

    ALTER DATABASE DB name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    GO

    DROP DATABASE DB

    GO

    Then run the restore process which will use xp_cmdshell.

    However, now i have to grant Control database and excute on xp_cmdshell to the user. Do you see any issues doing this way?

  • you have not understood anything that was transmitted to you.

    you setup a job - that job restores a particular database from a particular location - if name and location is always the same then its even better.  Job is owned by the account that is the owner of the database to restore - using restore as it requires less permissions than drop/create database.

    you then, following the link I gave you, setup a proc that has the permissions to start this particular job - and you give permission to your user to execute this proc - no other permission is required.

    so when the user wishes to restore the database he just executes the proc - and all work is done under the high priv user.

    xp_cmdshell is not needed for anything at all - if you really need to access filesystem to list files then do that with a powershell step

    To access the filesystem you will need to setup a proxy account if you require use of powershell to list and/or copy backup files - but note that you can restore from remote network location

  • Thank you sir

Viewing 15 posts - 1 through 14 (of 14 total)

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