How to copy data and database tables from one database to another

Question

Is there any way that I can copy all the data and database tables (and other objects) from one server table to other server?

To make those things as automated. so that every time we used to get that production data to my local database.

Thanks

Answer 1

You could also try out ApexSQL Script, a database migration tool, to script objects and data from one instance and execute it on another instance.

Also you can use ApexSQL Diff and ApexSQL Data Diff, SQL schema and data compare tools, to copy structure and data from a source to destination.

Disclaimer: I work for ApexSQL

Hope this helps

Answer 2

You could:

• backup and restore the database
• detach and re-attach the database
• use tools like Red-Gate SQL Compare for structural and Red-Gate SQL Data Compare for data comparison and syncing
• you could roll your own, using SQL statements and/or SMO (SQL Server Management Objects)
• probably a gazillion other ways to do this.....