Moving data between different environments

See more:

SQL-Server

database

I am challenged with the following problem and in desperate need of someone who can assist me in finding a solution, that will work in my environment.

Problem description:

We have 4 environments (DEV, TEST, QA and Live) at my place of work, all MSSQL. We need to move data between these environments on a regular bases, as we need to have recent and relevant data available in order to develop, test or allow QA to take place.

At the moment we do not have a automated mechanism that can move the relevant tables on a regular (repeatable) basis. This is being done on a developer by developer basis. If we are able to automate this process over weekends, it would save our developers a huge amount of time and additionally alleviate pressure on resources during work hours (Network, servers).

What we do have in place currently:

We have an SDLC process that assists us in ensuring that only the required DB schema changes gets implemented on the relevant next DB in the SDLC process.
•We achieve this by making use of a tool called SQL Compare in conjunction with SVN. A comparison is done between SVN source (Completed database schema exists in SVN) end relevant DB
•DB schema comparison is done automatically. The output of this process is a DB script that contains the changes which should be applied to this DB.
•We have an internal QA policy to check the DB script in order to insure that the changes which will be implemented are in fact what we are expecting it to be. Thus applying the changes is a manual process.

Some of the main challenges/concerns:
•The Live (Source) DB is just under 1TB
•There are certain tables that is environment sensitive (Has paths and links to servers in the particular environment)
•Core tables that must to be copied are +- 250GB, in other words it is not necessary to copy all tables using the automated process.
•Stress on the hardware resources, Disk access, Network copy time, time that it takes in order to complete this process.

Current server\instance architecture:
•Live DB has a single instance and is hosted on one server
•DEV, TEST and QA are three separate instances hosted on one server

Required solution:
I need something that can automate the process mentioned above with as little to no manual intervention as possible.

Thanks