Post reply

Database refresh from Production DB to Test DB in Sql server 2014

  • January 11, 2019 at 9:01 pm

    #381181

    Hello,
    I would like to know the what's the best way i can do Database refresh from Production DB to Test DB in Sql server 2014 ?

  • January 12, 2019 at 4:33 am

    #2018747

    poratips - Friday, January 11, 2019 9:01 PM

    Hello,
    I would like to know the what's the best way i can do Database refresh from Production DB to Test DB in Sql server 2014 ?

    Backup a restore is the best way.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • January 12, 2019 at 9:08 am

    #2018760

    Thanks for that but i know it as restoring backup will do it as it's normal way we restore the db but we have few databases and lots of users so login will also issue, right?
    I am looking best practice for regular basis so development users don't have any issues and they perform jobs like as it is normal.

    Thanks

  • January 12, 2019 at 10:02 am

    #2018762

    poratips - Saturday, January 12, 2019 9:08 AM

    Thanks for that but i know it as restoring backup will do it as it's normal way we restore the db but we have few databases and lots of users so login will also issue, right?
    I am looking best practice for regular basis so development users don't have any issues and they perform jobs like as it is normal.

    Thanks

    Backup/Restore is till the best option...

    You need to script out the permissions for the developers - restore the copy from production - then apply the script to restore those permissions.  One way to insure that logins are not an issue is to use windows domain accounts (and security groups).  You can then add the windows user/group to the database in production, grant the necessary permissions - without creating a login in production.

    When the database is restored to development - the login on the development system will tie to the user in the restored database without having to run any scripts.

    If you need to synchronize SQL accounts...the best way to do that is to create the login in production, then create the login in development with the same SID from production.  This way - once the database is restored the login in development will tie with the user by SID - without having to run any post restore scripts.  If that SQL login in production should not be used, you can then disable or remove that login in production to prevent anyone from using that login to access the system.

    Remember - all of the users in the databases don't matter if there isn't an associated login.  Limit the logins on the development system to only those logins that are necessary...and if you have created the logins with the same SID as production then those logins will have the same privileges as they would in production.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 12, 2019 at 12:12 pm

    #2018768

    Thanks but we can't keep the same SID as production is not the same access.
    After i restore multiple DB, i have to run the script to remove orphan users as production SID will not match and before that i have to script out the create script for login for each DB, right?

  • January 12, 2019 at 11:09 pm

    #2018776

    poratips - Saturday, January 12, 2019 12:12 PM

    Thanks but we can't keep the same SID as production is not the same access.
    After i restore multiple DB, i have to run the script to remove orphan users as production SID will not match and before that i have to script out the create script for login for each DB, right?

    Yes, If you have a different permission levels and need the permission of roles. You can take a script of development permission and can apply after a refresh.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • January 13, 2019 at 9:29 am

    #2018789

    poratips - Saturday, January 12, 2019 12:12 PM

    Thanks but we can't keep the same SID as production is not the same access.
    After i restore multiple DB, i have to run the script to remove orphan users as production SID will not match and before that i have to script out the create script for login for each DB, right?

    Just to be clear - developers have access to production already with a set of permissions and you want those developers to have a different set of permissions in development?  If so, that does not change anything for the process - if you use the same SID the logins will tie to the users in the databases, eliminating any steps to synch the logins with the users.

    The only post restore processing you need to do then is to apply those different permissions to that set of users.  A script can be created very easily that applies the specific permissions that are needed - and since permissions are cumulative you don't need to remove previous permissions, just grant the new permissions (of course, that is unless they have higher permissions in production which wouldn't make sense).

    If you utilize AD security groups for permissions - then you only need to apply the permissions to that security group.  And since this is windows authentication - the logins/users will automatically tie without having to worry about SIDs.  If you are still using SQL logins for developers - I would highly recommend you stop doing that and move them over to AD domain accounts and AD security groups.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • January 13, 2019 at 10:52 am

    #2018791

    Thanks Jeffrey for details explanation.

    Please correct me if i am following steps and you think something not going to work.

    1. Take Backup from All the Production Databases and transfer to TEST server
    2. Generate User Login script from TEST which will create  "sp_help_revlogin" Stored procedure in Master DB
    3. Restore Production Database Backups for each Database backup respectively in TEST server and execute that procedure "sp_help_revlogin" so user can have original login after refresh and no Mis match SId's
    4. Run DB related Change script if any changes we need to run in TEST
      Do i need to do anything with SSIS or other jobs?

    Thank you!

  • January 13, 2019 at 12:56 pm

    #2018794

    You do step 2 once. Not every time.
    And you generate the login script from prod, not test. You want the logins on test to have the same sids as they have on prod.

    Then each time you want to refresh, you take a backup from prod and you restore it to test.  If you want the users to have different DB permissions you then need to run a script to change the permissions after a restore.
    SSIS and jobs are server components, not in a DB. Do you also want those on  test?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 13, 2019 at 1:00 pm

    #2018795

    GilaMonster - Sunday, January 13, 2019 12:56 PM

    You do step 2 once. Not every time.
    And you generate the login script from prod, not test. You want the logins on test to have the same sids as they have on prod.

    Then each time you want to refresh, you take a backup from prod and you restore it to test.  If you want the users to have different DB permissions you then need to run a script to change the permissions after a restore.
    SSIS and jobs are server components, not in a DB. Do you also want those on  test?

    We actually have the reverse requirement.  We have dev, staging, and prod webservers and their "service" logins are quite different and so we have to generate the login script on the lower environments and not prod.  I think the OP might be in a similar situation.

    --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)

  • January 13, 2019 at 1:05 pm

    #2018796

    Thanks Gail.
    Yes, you are right only once i need to do step 2.
    I don't want the login from production as i have to keep TEST login as it's as it has different permission.
    I just wanted to keep SSIS or other jobs to keep in test as it was so as you mentioned those jobs are server component so don't need to do anyhting.
    Just a question for some of the tables which we need to keep the development data so we just run the deployment script after production refresh, right?

  • January 13, 2019 at 2:35 pm

    #2018799

    Yes, you do want the logins from prod, or you'll have mismatched logins every single time that you do the restore to dev. That's what creating the logins from prod (with SIDs and password if applicable) gets you.

    If the logins have different server permissions on dev,  you'll set that once, after creating the login. If they have different database permissions, you'll have to set that every time, as the restore will overwrite it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 13, 2019 at 6:55 pm

    #2018800

    Thank you, It was really great help!

  • January 13, 2019 at 7:43 pm

    #2018801

    Thank for your input.
    I have quick question if we implement TDE in sql server 2014 for Database at Rest, During Refresh from PRODUCTION to TEST, what happened?
    How to take care of it?

    Thanks

  • January 13, 2019 at 10:43 pm

    #2018802

    You need the certificate to be installed in master on the test instance to be able to restore.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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