How do I automatically synch SQL dev tables with SQL prod tables

[ljsmith91]

I have several production tables that I would like to keep in synch(at least bi-weekly) with our dev tables. I was wondering if this could be automated but I am not sure exactly how to do so. By dev tables, its really just development for reporting. SQL query dev work is done on these tables before moving into production. So, I am looking to copy the data and the table schema every few weeks. If there are new columns in a production table, it will need to be copied into the dev tables too.

What is the best mechanism for doing this?

Would it be:
1)drop dev table
2)recreate the new dev table(is there a way of getting exact create statement from the same prod table?)
3)copy the data to the newly created dev table

Is this all doable and something that could be automated? Does anyone have a sample on how to accomplish the above ?

Thanks.

 

[RiverGuy]

Just restore a backup made from your prod server. Automate it in a SQL Agent job on the dev server.

 

[ljsmith91]

Is that a SQL backup/restore done via a SQL command or by 3rd party backup product? Sorry... I am not sure I am following. I certainly do not know all the capabilities of SQL.

 

[ljsmith91]

I see it is a SQL backup after doing some research but that is at the database level. I cannot restore the full database. I need to synchronize ONLY select tables in the database. So, that doesn't appear to be an option for me.

 

[djj55]

Look into replication. You would want one way.

May not be a good way but one way would be to create a job that truncates (delete) the dev table and then populate it from live. This has various things to look out for including size of table.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!