Sql-server – copy tables and contents of database

sql server

I have created a database on my remote hosting server. It has the same name as my local database on my laptop. I don't know how to create a script or transaction to copy all tables and tables' contents to my new remote database. This script or transaction should not create a new database, just create the tables and tables' contents the same as the local database. I am using SQL Server 2012.

Best Answer

If this is not needed to perform on each and every day but sometimes, I suggest the following procedure:

  1. Open the source server in Management Studio. Right click the database. Choose "Tasks" and "Generate Scripts". In the wizard choose either "entire database" or "specific objects" (and choose your tables).

  2. Click Next.

  3. Click Advanced.

  4. At the property "Check for existence" choose "TRUE".

  5. At the property "Types of data to script" choose "SCHEMA AND DATA".
  6. At the property "Script Drop and Create" choose "Script Drop and Create" (this (re-)creates the (all|selected) tables with the current structure).

  7. Click OK.

  8. Decide if you want a file created or just the script opened in a new window.
  9. Click OK / OK.

  10. Run the script in your destination server and you are done.