How to Copy Data From One Database to Another Database in SQL Server?

Published By Ashwani Tiwari

Approved By Aswin Vijayan

Published On July 19th, 2024

Reading Time 5 Minutes Reading

Category sql server

Microsoft SQL Server is one of the most used applications by many users around the world to manage their data in an organized manner. MDF & NDF are the database files of SQL Server which stores all data such as tables, views, functions, triggers, stored procedures, etc. Eventually, SQL users face a situation where they need to copy database from one database to another database in SQL Server.

Now the big question is – How to export data from one SQL database to another.

Hence, to give the answer to this question we are going to discuss the detailed solution in the upcoming section of this write-up. Just stick with us to know the accurate answer to your problem. Let’s find out the manual methods first-

Approach #1. Copy Data From One Database to Another Database Using Generate Scripts

Go through the following steps to generate a script in order to copy data:

1. Run SQL Server management studio on your computer system and right-click on the Source database, you want to copy then click on Tasks >> Generate Scripts.
2. On the Generate and Publish Scripts wizard click on Next button to proceed further
3. In the Choose objects Window, select the required SQL Database objects you want to Generate Script out and then hit the Next.
4. Now, in Set Scripting Options Window, select the desired path to save the generated script file and click the Next button.
5. Preview the summary after selecting all the options and then click on the Next button.
6. Finally, click on Finish to close the wizard.

Approach #2. Copy Tables From One Database to Another Database Using Export/Import Wizard

Another way to achieve this task is by using the Export and Import wizard that is available in SQL Server Management Studio (SSMS). To copy or move the database items in SSMS follow the below-given instructions carefully to copy data from one database to another database in SQL Server:

Step 1. In the very first step run SQL Server Management Studio on your system.

Step 2. After this, go to object explorer, and right-click on the Source database you want to copy then click on Task>>Export Data. 

Step 3. On the Import/Export Wizard click on the Next button.

Step 4. Here, you have to specify the Server Name, Authentication mode, Source database name, and then hit the Next button. Also specify the Destination Database Name, Server Name, Authentication method, and click on the Next button.

Step 5. In the next window select the Copy data from one or more tables or views option and click on Next button to proceed.

Step 7. Now select the Tables you want to move from source database to destination database, and click on Next.

Step 8. Here, the save and Run wizard will open, simply click on the Next button, and in the end Finally, press the Finish button.

Limitations of manual approaches 

1. More chances of data loss or database file corruption.
2. Required complete technical knowledge to follow these steps.
3. Risks of data loss make it more complicated to perform this technique.
4. Does not work if the database is corrupted.
5. Does not work if you want to move all database objects

To tackle all such limitations here we have the best solution for you. And it comes-

Also Read: How to Attach SQL Server Database Without Log File?

Best Automated Solution to Copy Data from One database to Another Database

When it comes to your precious data, don’t compromise with it by choosing any manual solution randomly. No doubt, you can export the manually but sometimes it is a risky approach. Hence, it is suggested to go with SQL Migration Assistance to transfer SQL sever database .

The software we have recommended here is a direct and error-free solution to migrate the database. Moreover, users can export the corrupt tables, views, indexes, stored procedures, functions, etc to another database in a single attempt with this application. In addition to this, MDF file created in any version of SQL Server is smoothly supported by this application and users can easily install this tool on any Windows platform.

Download Now Purchase Now

Now let’s see the tool’s working

1. Download and Run the SQL Recovery Tool in your Windows system and click on the Open button to add an MDF file.

2. Choose either the Online Mode if copying database from a live SQL server or else “Offline Mode” and select the database also.

3. Once the file is scanned successfully, preview the data and click on the Export button.

4. After that choose the SQL Server Database option and enter the required details.

5. Select the required export option from Export Window. Select either With only Schema or With Schema and Data option and finally, press the Export

Read More: How to Repair Corrupt SQL Database?

Wrapping up

From the above blog, now you know how to copy data from one database to another database in SQL Server. you can export data with the use of manual methods, but there are several limitations and risks to lose your important data. So, it is suggested to use the advanced professional best for a hassle-free and quick solution.