Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

QA environment data refresh strategy Expand / Collapse
Author
Message
Posted Monday, June 9, 2008 10:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:20 AM
Points: 201, Visits: 405
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,



Post #513840
Posted Tuesday, June 10, 2008 9:37 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
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

Post #514566
Posted Tuesday, June 10, 2008 9:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:20 AM
Points: 201, Visits: 405
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



Post #514580
Posted Tuesday, June 10, 2008 10:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
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?)
Post #514631
Posted Tuesday, June 10, 2008 11:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
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

Post #514642
Posted Tuesday, June 10, 2008 12:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:20 AM
Points: 201, Visits: 405
Thank you, Yelena

what exactly is how YOU descibed it



Post #514746
Posted Tuesday, June 10, 2008 1:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
We use SQL Compare and script out all the changes, and apply after the refreshment. :D
Post #514761
Posted Tuesday, June 10, 2008 1:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 8:20 AM
Points: 201, Visits: 405
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



Post #514764
Posted Tuesday, June 10, 2008 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 7,115, Visits: 14,983
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?
Post #514768
Posted Tuesday, June 10, 2008 1:25 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 15, 2012 7:53 PM
Points: 494, 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
Post #514774
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse