SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


QA environment data refresh strategy


QA environment data refresh strategy

Author
Message
SD1999
SD1999
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 414
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,



Yelena Varshal
Yelena Varshal
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4572 Visits: 595
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 Varshal

SD1999
SD1999
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 414
Thank you(spasibo), Yelena Smile

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
Wildcat
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 1444
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
Yelena Varshal
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4572 Visits: 595
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 Varshal

SD1999
SD1999
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 414
Thank you, Yelena

what exactly is how YOU descibed it



Wildcat
Wildcat
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 1444
We use SQL Compare and script out all the changes, and apply after the refreshment. BigGrin
SD1999
SD1999
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 414
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)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12185 Visits: 18572
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)
Raghuram (AJ)
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 299
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search