Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
have you looked at the import/export wizard or creating a custom SSIS package to do this for you? –  Shawn Melton Jan 23 '12 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 '12 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. –  Shawn Melton Jan 23 '12 at 19:45
add comment

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
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 '12 at 23:04
    
I'm giving this a +1 vote for the #3 answer :-) –  MarlonRibunal Jan 24 '12 at 9:56
add comment

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.

share|improve this answer
    
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 :-) –  MarlonRibunal Jan 24 '12 at 9:59
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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