QA environment data refresh strategy

Question

QA environment data refresh strategy

SD1999

Hall of Fame

Points: 3140
More actions
June 9, 2008 at 10:31 am

#121890

Hello everyone,
we have this monthly data refresh, when we copying data from production to our QA environment
it involves more than 40 databases and this number is growing
because it's only once a month, i don't want to use replications nor copy db wizard
are there any third party tools or you guys can suggest some technic to simplify this process?
we running windows 2003 ent, cluster, sql 2005 standard sp2
thanks,

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

The easiest way to do it is to write a detach/attach or backup/restore script for all databases and use it. That is what our DBA is doing. She spent not so much time for codding these scripts but it is easy to use.

To write the scripts for 40 databases is easy this way:

write a select statement that will generate the scripts.

Read RESTORE (T-SQL) article in BOL. It contains the example for database copy from Production to Development:

Step 1

BACKUP DATABASE AdventureWorks

TO DISK = 'C:\AdventureWorks.bak'

Step 2

RESTORE FILELISTONLY

FROM DISK = 'C:\AdventureWorks.bak'

Step 3

RESTORE DATABASE TestDB

FROM DISK = 'C:\AdventureWorks.bak'

WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',

MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'

GO

The following Select statement will produce the scripts for backing up all databases on your server (step 1)

select 'BACKUP DATABASE ' + name +' TO DISK = '

+'''' + 'C:\' + name + '.bak' + ''''

from sys.databases

Use something similar for steps 2 and 3. Four single quotes in Select will give you one single quote in the result where you have to have single quotes around file names.

Regards,Yelena Varsha

SD1999 Hall of Fame Points: 3140 More actions · Answer 2

Thank you(spasibo), Yelena 🙂

this is exactly what we are doing here....

i'm looking for tool that can do all this without running a t-sql scripts manually,

nice GUI interface

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 3

One question: if you use backup/restore from Production to QA, how do you keep the changes in QA? (I know the data is refreshed, but, what about the others?)

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Wildcat,

This is the problem!

The developers MUST have all their changes scripted and re-apply to the restored database

They have to have them scripted anyway to apply to Production at some point.

Slava,

You are welcome (Pozhaluysta)

Regards,Yelena Varsha

SD1999 Hall of Fame Points: 3140 More actions · Answer 5

Thank you, Yelena

what exactly is how YOU descibed it

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 6

We use SQL Compare and script out all the changes, and apply after the refreshment. 😀

SD1999 Hall of Fame Points: 3140 More actions · Answer 7

Actually, it's a great idea.

you see problem with that is that we have 60 databases, potentially 250

it's really time consuming and confusing to go over all those scripts and make sure that they look good

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

If you're willing to "pay" to not have to rewrite the change scripts - look into Team Suite for Database Developers. By setting up a "database project" - you can then connect to various servers, and run a schema compare, which would allow you to push the changes out.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Raghuram (AJ) Ten Centuries Points: 1214 More actions · Answer 9

If you are looking only for data differences, redgate's SQL Data Compare is an awesome tool for data compare & update.

The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner

Johan Bijnens SSC Guru Points: 135360 More actions · Answer 10

just my 2ct

be carefull when using the copy database wizard, because in some scenario it will detach your production database briefly !!

It is better to use Yelenas methode of backup/restore.

Don't forget to "resync" your sql-users after restore using :

sp_change_users_login @Action = 'Update_One',...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

SD1999 Hall of Fame Points: 3140 More actions · Answer 11

thank you gentlemen

i will look in to those two that you have mentioned

SD1999 Hall of Fame Points: 3140 More actions · Answer 12

ALZDBA, if you specify 'copy' not 'move' in the db copy wizard it will not detach the source database

Johan Bijnens SSC Guru Points: 135360 More actions · Answer 13

well, even with "copy" it gives you the choice :

1) use detach/attach mode

2) use SMO object mode

I just wanted to point to not using the first one.

btw I'm using a SQL2005 SP2 cu6 client.