Copy DB from Prod to QA

Question

Copy DB from Prod to QA

Viewing 5 posts - 16 through 19 (of 19 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

New Born DBA (3/31/2014)
I did run into some problems and I am wondering if someone can tell me what to do.
I was unable to restore the DB because the DB we have in Prod is encrypted (TDE). So what would be step by step guide on how to restore the encrypted DB?

Thats a shame, instant file initialisation (IFI) is not available with TDE. It also means you get little backup compression as well so cannot speed up the copy between servers

@scott - as IFI is available to restores as well as create statements, and the first thing a restore does is create the files, I cannot see the advantage of creating an empty 'placeholder' then doing a restore on top of that. Also in QA, it is common the set the database to simple recovery mode and recover space by a one off shrink of the log (presuming space is an issue), so why create a log file just to (possibly) shrink it?

Of course after the first restore, the files will exist and subsequent restores can just use the 'replace' option.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 2

Ed Wagner (3/31/2014)
I've found the creation of a script and then restoring from a production backup to be very reliable.
ScottPletcher (3/31/2014)
Of course, if it's possible, it's much easier longer-term to just change the sid on qa to match what's on prod, then the restores will automatically re-sync those users.
How do you change the SID on the destination? I've never gotten it to work successfully, so I query for the mismatched SIDs and rebuild the users so the SIDs match the logins.

presuming these are SQL logins, you could recreate them from a script produced by sp_help_revlogin on the prod server, you might want to change the passwords though.

---------------------------------------------------------------------

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Ed Wagner (3/31/2014)
I've found the creation of a script and then restoring from a production backup to be very reliable.
ScottPletcher (3/31/2014)
Of course, if it's possible, it's much easier longer-term to just change the sid on qa to match what's on prod, then the restores will automatically re-sync those users.
How do you change the SID on the destination? I've never gotten it to work successfully, so I query for the mismatched SIDs and rebuild the users so the SIDs match the logins.

You have to delete the login and then re-add it with the appropriate sid. The typical steps are:

1) Capture/Script all server-level/non-databases permissions/uses of the login

2) Delete the existing login

3) Create the login with PASSWORD = <...>, SID = <sid_copied_from_prod_server>

4) Reassign all server-level/non-databases permissions/uses of the login

5) Run "sp_update_users_login" in every db in which that user appears to correct the sid in the user dbs; this only has to be done one time, of course.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

ScottPletcher (3/31/2014)
Ed Wagner (3/31/2014)
I've found the creation of a script and then restoring from a production backup to be very reliable.
ScottPletcher (3/31/2014)
Of course, if it's possible, it's much easier longer-term to just change the sid on qa to match what's on prod, then the restores will automatically re-sync those users.
How do you change the SID on the destination? I've never gotten it to work successfully, so I query for the mismatched SIDs and rebuild the users so the SIDs match the logins.
You have to delete the login and then re-add it with the appropriate sid. The typical steps are:
1) Capture/Script all server-level/non-databases permissions/uses of the login
2) Delete the existing login
3) Create the login with PASSWORD = <...>, SID = <sid_copied_from_prod_server>
4) Reassign all server-level/non-databases permissions/uses of the login
5) Run "sp_update_users_login" in every db in which that user appears to correct the sid in the user dbs; this only has to be done one time, of course.

Thanks. I've put that on my list of stuff to work on. Looks like a more efficient way of dealing with the mismatch than what I was using.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

New Born DBA (3/31/2014)
I did run into some problems and I am wondering if someone can tell me what to do.
I was unable to restore the DB because the DB we have in Prod is encrypted (TDE). So what would be step by step guide on how to restore the encrypted DB?

Just a word on that then... if you ever intend to pass an ISO, SOX, SOC 2 (simto old SAS 70), or other audit, your QA server is going to need to be locked down even tighter than the prod server. You also need to be prepared to prove that it's locked down tighter to keep both internal and external people that have no business seeing the data out of the database. You should do that even if you don't ever intend to survive an audit because not all data theft is through production systems.

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