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
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8968 Visits: 600
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
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1251 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8968 Visits: 600
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
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

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

what exactly is how YOU descibed it



Wildcat
Wildcat
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1251 Visits: 1444
We use SQL Compare and script out all the changes, and apply after the refreshment. BigGrin
SD1999
SD1999
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 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)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29529 Visits: 19006
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)
Say Hey Kid
Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)

Group: General Forum Members
Points: 684 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