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

Alright, I found it, completely in visual studio. This can be from local (.mdf) files to server files and visa versa.

1. Go to Sql Server Object Explorer and find your source database. Or in Server Explorer, right click on your source database and click on Browse in Sql Server Object Explorer
2. When in Sql Server Object Explorer, right click on your source database and click on Data Comparison
3. A dialog will popup, showing you a Source Database and Target Database. Your Source Database should already be selected. Now select your Target Database, you probably have to add a new connection, just do it like you would usually do in Server Explorer. If you want the target database to be a local (.mdf) file and you cannot click on Change, than add it first to the server explorer and it will popup in the combobox.
4. Choose what you want to compare by checking the checkboxes and click next.
5. It will connect to the servers and show you another dialog what to compare, check the checkboxes and click on Next.
6. Here you will have an overview of your changes from source to target.

At this point, you will be able to kind of 'mirror' the source database with the target database. For instance: If you have added more records to the Target database then there are in the source database, then these records will be deleted once you click Update Target Take a close look at what will happen when you hit that button!

7. If you are satisfied with the result, click Update Target. If you are not satisfied, next to the button there is a little icon to export it to a .sql file, so you can modify what will happen to the target database.

Unfortunately, after writing this, I found out that this is only available in VS Ultimate and Premium, that is what they say on the following link about visual studio 2010, I have tested this in Visual Studio 2015 Enterprise. MSDN: https://msdn.microsoft.com/en-us/library/aa833428(v=vs.100).aspx

Answer 2

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 3

You can use SSIS import and export to do that. It asks for the source server name, source dbName and Destination server name, destination dbName.

OR

you can use the back-up and restore process.

But I always prefer SSIS.

Answer 4

I suggest you to take a look at "Visual Studio 2010 SQL Server Database Project". It provides you with great features to manage database team work. It allows you to create a local database and import the production database into the local database. From now on, you and your team can work on local database and when you are ready to deploy your changes to production database, Visual Studio 2010 synchronizes the local database with production database, So the production database will get the latest changes.

Answer 5

you can create a DTS job to copy database and its contents on timely basis.

http://msdn.microsoft.com/en-us/library/cc917688.aspx

Answer 6

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.....