Migrate SQL Server To New Server

DBAs, architects, and developers often need to upgrade their SQL Server databases from an older version to a new one. Every couple of years or so, a new version of SQL Server is available with the betterment of technology with improved security, functions, performance, speed and other benefits. However, migrating an existing SQL Server to a new SQL Server version may not be easy without detailed planning, knowledge, and experience.

This article provides top 4 ways to migrate SQL Server to new SQL Server manually. These all methods are free and recommended. I believe that these methods will help you migrate your existing SQL Server database to another version of SQL Server in easy steps.

Method 1. Migrate SQL Server Database to New SQL Server using Backup and Restore

The Backup & Restoring Technique using the SQL Server Management Studio (SSMS) is our first method. As we don’t have any direct solution available in the manual method, here two separate phases are present. Let’s Quickly learn this solution with the steps:

Backup & Restoring Technique

Phase 1: Taking Full Backup of the SQL Server Database

Launch the SSMS in your system >>Click on the Object Explorer here.
Click on the Databases button >> Right-click the desired Database now.
Click on the Tasks option here >> Now, Hit the Backup button to proceed.
Select the backup type as Full & Destination as Disk in the dialog box.
ClickAdd button >>EnterDestination Path>> Click the Ok button.

Phase 2: Restore the Backup to Migrate SQL Server to New Server

Start the SSMS & Connect to your Database in the new server.
Right-click Database >> ClickTask >> Restore >> Database.
Select Device under Source in Restore Section to proceed.
Click on Browse (...) >> Now, Add your Backup File here.
Set Backup Media as File under Specify Backup Wizard.
Hit the Add button >> Select Backup File >> Click Ok.
In Restore Database Wizard, ClickSelect a Page panel options.
Specify Restore Options & Hit the Ok button to finish the task here.

Method 2. Migrate SQL Server Database to Another SQL Server using Copy Database

Now, the second method is the simple copy wizard method. In this, users can simply copy the database files & then move them to the destination in a smart way. This method is really quick to move SQL server to new server by copying method.

Launch SSMS in your system & then Go to the Object Explorer option.
Users need to make sure that SSMS is Connected with Source server.
Right-click the Database & Choose Desired Database & continue.
Click on Task >> Copy Database >> Click the Next button here.
Type Source Name>> Complete Server Authentication now.
Click on Next to set a connection with the new server as per need.

copy wizard method to migrate SQL server to new server

As this method is quite fast, it skips a lot of crucial things. For this reason, experts usually advise not to go with this one to migrate SQL server to new server. However, it still helps in some cases.

Method 3. Execute SQL Migration Task Manually

Using can move SQL server to the new server with the help of Import & Export Wizard of SSMS utility. There are only five simple steps involved in this method for users. Let’s go through them to know the in & out of this method.

Launch the SSMS in your system & Select Database & Task option.
ClickTriangleIcon & Select Data source, Server name, and Database.
Now, simply Select the Destination Server& Database as per your need.
Select Tables & Views from the Source server & then Click on the Next button.
The app will migrate SQL server to new server. Just check if your data files are there.

import export wizard

This method was mostly used between 20012 & 2016 by users of SQL server databases worldwide. This really helped users to get the desired solution they required.

Method 4. Migrate SQL Server to New Server using Generate Scripts

The final method is of generating scripts that are SQL-compatible. Here, users just generate SQL scripts to transfer their database to the destination where they want it to be. However, it’s a little complex but it works pretty fine though.

Launch the SQL Server Management Studio in your system to begin.
Right-click Databases >> Click Tasks >> Generate Scripts >> Next button.
ClickChoose Objects option & Hit the Select Specific Database Objects option.
Under the Set Scripting section, SelectOutput Type & Click the Advanced button.
Click the Save button on the new query window to proceed further in the operation.
ClickSchema& data from the drop-down menu in scripting options & Click on Ok.

Migrate SQL Server to New Server - Top Four Solutions

After this, users will get the database in script form to migrate SQL server database to another server. Then users can run this script in the SQL server destination database & automatically all the data files will reflect there.

Most Common Loopholes of All These Solutions

Now, as we learned all these four manual solutions, they seems easy but can be complicated. If not planned and developer/DBA does not have experience, these solutons could present problems. Here are some of the loopholes that you need to be aware of.

SSMS Application

As we can see, we are using the SSMS application in almost all the methods. This is why if users do not have the SQL Server Management Studio in their system, they can’t move SQL server to new server with these four techniques. To avoid that they can opt for the SysTools SQL Migrator tool. This is an automated solution allowing database migration as per user needs selectively without any errors.

No Selective Mode

The manual methods do not allow users to migrate their database selectively. More or less, they have to migrate the entire database with unnecessary database objects as well. This is a little disappointing for users to migrate SQL server database to another server.

Corruption in Database Objects

Another issue that can be a trouble for users is the corruption in database objects. To solve this, users need to be aware of the repair query which is DBCC CHECKDB to check & repair the database. Otherwise, database migration with corrupted objects is more likely to fail.

Conclusion

We advise users to select any of these methods to get the best results for their database migration task. Just being aware of the loopholes can help users to migrate SQL server to new server without any issues. Also, selecting the automated solution is another recommendation from experts that users are following nowadays. It’s significant for users to select the way that’s best suited to them

3 Comments

Join the conversation! Your thoughts help the community grow.

Eric Sidharta3y ago
Hi Anurag, what are the pros and cons of each of the methods?
- Anurag Sharma2y ago
  Hi there, Eric Sidharta. We have advantages & disadvantages of all the solutions. Have a look at them below: The #1 Backup & Restore Method comes with easy GUI & minimal errors but is very lengthy, offers no customization of data, & users often get confused. The #2 Copy Wizard Method is really quick to execute but there are chances that users will not find data in proper hierarchy at the destination. Also, here, the data loss risk is a bit more. The #3 Import/Export Method is not special as users can opt for this if the first two solutions are not working. The drawbacks here is that it's not a dedicated solution, plus users need some level of SQL Server proficiency to execute this. Lastly, #4 Generate Scripts Method offers total customization and full control over the process. However, it is very technical & not all users can select this one. Also, it is a bit confusing for new users. Any wrong command here might end up losing the entire database. Therefore, I prefer SysTools SQL Database Migration Tool which acts like magic.
Mahesh Chand3y ago
Thank you, Anurag. Appreciate it.