As a DBA, you might need to copy the objects and the content of specific tables from a database to another one in the same instance or in any different SQL instance. You might think of using Insert Into Select statement in MS SQL Server but it will not be useful in scenarios such as transferring few tables from a production database to a development one for testing or troubleshooting. Also, this depends on the number of tables, size, and available space in the database. If the total size of the tables is more than 50% of the total size of the database than the recommended method to use is the backup and restore the database.
To copy tables between databases in SQL Server by generating Scripts using SQL Server Management Studio follow the below steps –
- Connect the SQL Server instance, Open the Object Explorer and select the database.
- Right-click the database, select Tasks, and then click on Generate Scripts, Click on “Next”.
- On the Choose Object page, choose Script entire database and all database objects or Select specific database objects option and Click Next.
- For Set Scripting Options, Select the Output Type, choose the file destination and name an click Next.
- Now the Summary page details will appear for the entire process. Click Next.
- Now, the Save or Publish Scripts page shows the progress of the entire process as shown below, click Finish.My Personal Notes arrow_drop_up