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.