Easy or Schedulable way to move data from Production to Test

Question

What I am looking to do, is schedule a copy of data from my production database to my dev/test database.

The dev/test database will be newer than the production database in terms of schema, but the production database has current data. I'm in a bit of a bind as I need to test my database changes against production size data, and it would be very helpful to have current data.

I'm on SQL Server 2008 R2 Standard for both production and development environments; is there a way I can create a job on my development server to "suck" data from my production database in a read-only fashion? I'd like a script that can copy table data wholesale, ignoring missing columns and ignoring any constraints in the target table. A script that does this for one table is all I really need, I can modify it to fit my tables and duplicate it to run for all my tables. The issues I'm having is finding anything even remotely similar to this.

This is a bit of a follow-up on my last question of the same nature; I have since got my database into source control, and I'm liking it much better. The problem is that I still face a stale data issue on my development server, so I'd like to find a way to schedule updates.

I'm open to alternate ways to achieve this, but it needs to be something I can run on a schedule and something I can build with notepad and/or SSMS or other tool available with Standard Edition SQL Server.

Answer 1

Here is what I would recommend:

1. If you do not already do this, create a regular backup process for your production database. To make life simple, make it a full backup and include all database objects (tables/schema/data/users). The Microsoft website has a lot of information on how to achieve this.

2. When you need to update your development environment, restore the production backup into development, then implement the migration (tables, procedures, data) you need to test using the method that is documented for the production change.

3. Test, knowing that you are not just testing your code changes, but also testing your migration strategy for when you roll out your change to the production environment. You are also testing your database backup and restore (DR) strategy as well.

Note, this may mean you have 2 development databases, one for actual development and one for integration/QA/UAT testing.

Answer 2

SSIS is probably your best tool for this situation. Create a package performing your copying and schedule it using SQL Agent. You can very easily copy data from one environment to another. You can set it to either redirect or ignore errors and inconsistencies in your copying operations, depending on your need.

I realize this isn't Notepad and SSMS, but SSIS is included with SQL 2008 R2 Standard and is (IMO) very worthwhile having at least a passing familiarity in.

Answer 3

The best method which I feel is:

1. Create a regular backup process for your production database. To make life simple, make it a full backup and include all database objects (tables/schema/data/users).
2. When you need to update your development / QA environment, restore the production backup into development / QA, then implement the migration (tables, procedures, and data).
3. Then depending upon the requirement you can set up the transactional replication, differential transaction or snapshot replication. For a reporting database, you could keep it relatively up to date with either log shipping, replication, or mirroring and creating snapshots depends on the requirement
4. And if you want to control the size of database on QA server then you can set up the purge job depending upon the requirement.