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.

  • have you looked at the import/export wizard or creating a custom SSIS package to do this for you?
    – user507
    Jan 23, 2012 at 18:46
  • I have tried the import/export wizard but it always fails with FK constraints and I can't find a way to disable these during insert (because they are valid once all other tables are inserted, just not at first). SSIS sounds interesting, but I don't even know where to start with it.
    – Nate
    Jan 23, 2012 at 18:53
  • I'm not at my test machine to play with it right now but I recall getting the FK error when I did the import on a database. I just can't recall what I did to get around it at the moment. Someone else might by the time I get back to my test machine.
    – user507
    Jan 23, 2012 at 19:45

3 Answers 3


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.

  • What I actually decided to do is restore the production backup to the dev server, then re-run all the change scripts. So far, its working well proving my backup works as expected, my change scripts work against the production database, and I have better piece of mind.
    – Nate
    Jan 23, 2012 at 23:04
  • I'm giving this a +1 vote for the #3 answer :-) Jan 24, 2012 at 9:56

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.

  • I think this is a better solution for the needs described in the question. SSIS might be the solution for the incremental data that Nate needs :-) Jan 24, 2012 at 9:59

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.