QA environment data refresh strategy

  • 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,

  • 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

  • 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

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

  • 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

  • Thank you, Yelena

    what exactly is how YOU descibed it

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

  • 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

  • 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?

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

  • thank you gentlemen

    i will look in to those two that you have mentioned

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

  • 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.

    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

  • Instead of using T-SQL you could use some of the reload.bat where you could pass your db names from predifined list of databases, copy and restore them (using some common restore logic as similar db names, location)

    (bez bazara)

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

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