Moving data from one SQL server to another is easier than copying data from one server to another. It is usually suggested to restore the database from the backup to the new database and then utilize the best techniques to migrate data from one SQL server to another to the target database to reduce the workload on the production database. Again, this is determined by the number of tables, size, and available space. If the table(s) size exceeds half of the overall size of the database, the backup-and-restore approach is advised.
Before we get started, let’s go over the article’s objectives. Then we go through each module or method in detail. In this post, we will explore some features of SQL Server’s built-in choices and demonstrate how PowerShell and third-party SQL Database Recovery Tool allow users to move data from one SQL Server to another. It can be used to replicate SQL tables across databases and instances. I’ve provided you with enough information for the next modules at the start of each method. This module is followed by numerous others, each dedicated to a distinct approach.
Numerous approaches for copying tables.
- Using PowerShell. Net class library to replicate tables.
- Move data from one SQL server to another by using copying Database Wizard.
- Copy SQL database to another server using Backup and Restore
- By Import and Export Wizard.
- By Professional tool
Approach 1: Using PowerShell. Net class library to replicate tables
PowerShell becomes the first choice for any administrative task. Net offers the SqlBulkCopy class library to bulk load the table or tables into the database.
PowerShell Script
The below PoSH script generates a function named Get-SQL Table
Image insert six
The $tables array variable is used to assign the list of the table(s) to be copied to the resultant database
[string [ ] ] $tables = @( ‘dbo.OPERATION’ , ‘dbo.OPERATION_DETAIL’ )
Let’s call on the Get-SQLTable function with the below mentioned parameters to copy the tables from Adventureworks2016 database on ‘HQDBT01’ to Adventureworks2012 database on hqdbt01/sql2017’ instance.
Get SQLTable-SourceSQLInstance hqdbt 01 – SourceDatabase AdventureWorks2016 TargetSQLInstance hqdb01\sq12017 – TargetDatabase AdventureWorks2012 -Tables tables-BulkcopybatchSize 5000
The results show the tables OPERATION and OPERATION_DETAIL copied to the target instance.
Approach 2: Move data from one SQL server to another by using copying Database Wizard.
SQL Server Management Studio includes the Copy Database Wizard. Suppose you have two servers and want to move a database from Server A to Server B while also being a server expert or administrator. In that case, using the inherent database migration capability is a good place to start.
Pre-requisites:
- The Copy Database Wizard should not be in the Express edition.
- The database did not move to an earlier version of SQL Server.
- The Copy Database Wizard failed to move system-marked inaccessible, Suspected databases in Emergency Mode, Offline Recovering, or log files stored in Microsoft Azure storage.
Steps to Move data from one SQL server to another by using copying Database Wizard.
Follow the steps:
- Open SQL Server > Tasks >Copy Database…> Next,
- Next, the Wizard of Select a Source Server appears.
- Enter the Source server number and select Windows authentication or SQL Server authentication (if you chose SQL Server authentication, then you need to enter the Username and Password) > Next
- In Next Wizard, enter the destination server name and select the authentication information(Windows/ SQL server) > Next.
- In the next wizard, select the transfer method and choose SQL Management Object Method >Next.
- Now select the database that you would like to move or copy. Check Copy Box if you wish to copy database SQL server to the original server Or Check Move Box if you wish to move SQL database to another server.
- Now check the MDF and log file path on the destination machine and follow the instructions.
- After following the instructions, reach the destination to check are you successful in move table from one server to another server in SQL?
Approach 3: Copy SQL database to another server using Backup and Restore
SQL Server Management Studio is a Server Management Tool that works as an allrounder. You can also copy or move data from one SQL Server to Another to transfer the database by backup and restore option.
Follow the steps to create a backup Of the SQL Server.
- Open SQL Server > Tasks >Backup…> Next,
- Select Backup type is Full > Add.
- After creating a full backup, Copy the created Backup file to another PC or hard drive.
- Now open SQL Server Management that you have chosen to restore your data. Here, select RestoreDatabase…
- Select Device and you will choose the backup files on the source location and restore them to the destination on the new system.
Approach 4: Copy Data in SQL Wizard by using Import Export Wizard.
Follow the instructions to Copy Data in SQL Wizard by Import Export.
- Open SQL > Task> Export Data…
- On the next Wizard, enter the details of the Data source and Server name > Next.
- Now, select the destination where you want to move your data, then Next.
Approach 5: Move Data from One SQL Server to Another by Professional Tool
Although we have discussed several manual methods to copy data from one server to another, not everyone is techy and not successfully able to execute all the steps. In this case, we suggest you take the help of a professional master SQL Recovery Software free full version. It not only helps you move data from one SQL server to another but also repairs your corrupted MDF and NDF file extension and helps you restore your data in the SQL Server base.
Benefits of Sysinfo SQL Database Recovery Tool
- Repairs your corrupted MDF and NDF files and restores your data in the SQL Server database.
- It recovers your deleted and advanced deleted records from the SQL Server database.
- It allows you to preview the database content before saving it to the chosen destination folder.
- During the restoration, the program maintains the original data structure and attributes.
After knowing the benefits of this tool, let’s learn about How To Operate this tool. Follow the simple steps:
- Start the Sysinfo Database Recovery Tool and run it as an administrator.
- Now Browse the SQL database files to add MS SQL database files( MDF & NDF).
- Select the recovery mode, and manage your deleted records.
- Select your choice Auto- detect or manual with the Collation Option and destination for your file, then OK. Your scanning process starts.
- Select the specific table/folders from the tree structure.
- Click the Save SQL Data button and select SQL Server Database or SQL Server compatible SQL Scripts > OK.
- The saving process starts.
Protecting Against the Root Cause of SQL Server Data Corruption – It’s Not Always A Developer Error!
Conclusion:
We have described manual and professional approaches to move data from one SQL Server to another. Although the manual approach is techy, not everyone can execute it successfully. So we suggest you choose a professional tool to make your task easier.
