SQL Server Database Migration Steps and Types

Rollins Duke
Published: December 10th, 2024 • 12 Min Read

Nowadays several organizations and individual SQL project managers have the desire to know SQL Server database migration steps. There are multiple reasons for doing SQL Server migration but for new users, this is a complicated task. Hence, various users want to know about database migration best practices.

As we all know, SQL migration is moving an SQL database from one place to another. But this task has some complexity when following steps as users have concerns about losing crucial data and server downtime because it may affect the business revenue.

There are major SQL Server database migration steps that users need to execute. These 4 steps are:

Extraction of Data:
Extract the data from database in the source server & transfer it further. It happens after filtration of the data files.
Organization of Data:
Now, the major task is to standardize the data as per the destination SQL Server and its metadata properties to fit perfectly.
Collection of Data Together:
Execute the collection of data for any aggregate tasks that need to be deduplicated as per users’ specific requirements.
Loading the Data to Destination:
Migrating the aggregate, organized & transformed data files to the destination SQL Server without errors as soon as possible.

Recommended: Manual Methods to Recover Deleted Table Data in SQL Server

SQL Server Migration Issues and Reasons

As we all know that Microsoft officially stopped support for old SQL Server versions such as SQL Server 2008/R2 and SQL Server 2012. Thus users don’t want to compromise security hence they may prefer SQL server database migration from 2008/2012 to the latest SQL editions.
Multiple small-sized businesses are in a growing phase and using SQL Server locally. But for future use, they want to set up a virtual environment with upgraded hardware. Hence, they prefer to migrate SQL server database instances from local to virtual.
Several organizations want to reduce their per-year Windows OS license costs for multiple devices that use Microsoft SQL Server to handle automation transactions. Also, instead of this nowadays users prefer to setup a SAN (storage area network).
Sometimes companies and professionals have the desire to move SQL server databases from local servers to cloud-hosted data centers because of scalable features. Hence users search for SQL Server data center migration steps.

Types of Database Migration in SQL Server

Evidently, different business enterprises prefer to do various types of database migration in SQL Server as per work requirements. Here we are discussing a few major types of SQL migration. Understanding the SQL migration checklist after this is also going to help users understand and fix the operation from its very roots.

Storage Migration

It refers to moving SQL Server data from one device to another such as hard drives, solid state drives, on-premise servers, and cloud-hosted servers.

Data Migration

Data migration refers if someone wants to do SQL Server data migration between two vendors and software providers.

Business Process Migration

If two or more businesses complete the merger or acquisition formalities then the parent company wants to merge the business process which is called business process migration.

Application Migration

If someone decided to change applications or operating systems environment that is considered application migration. For example, SQL Server database migration from 2012 to 2019.

Pre-Migration Checklist

Ensure enough free drive space on your target drive.
Purchase the licenses for the latest required versions.
Verify if the database possesses any orphan users or not.
Confirm the log file and data location on the target server.
Setup your target drive, virtual drive, on-premise, and hosted server.
Verify if any maintenance plan is associated with the database or not.
Cross check SQL Server for dependent objects like Linked Servers & Agent Jobs.
Keep in mind that dependent application services must be stopped during migration.
Collect your current SQL Server database information including all properties mentioned below:
- Compatibility level
- Trustworthy option
- Database Owner
- Recovery Model
- Auto Statistics
- Etc Elements.
Work on preparing the rollback plan as well for any mishappening in advance.
Pre-determine the time required & schedule the entire migration process accordingly.
Always test the migration with a pilot project or demo migration to cross-verify all configurations.

Manual Method for SQL Server Database Migration Using SSMS

Use SQL Server Management Studio (SSMS) for completing SQL migration steps manually free of cost. Also, experts recommend SSMS for small database migration. You can easily complete this process within 3 phases. Also, we must mention that all SQL Server data migration techniques work well but users need to select one as per their needs and priorities. For safety, automated solution is good and for efficiency, SSMS might be considered.

Phase 1 Backup SQL Database from Source Server

Login to your source SQL Server that you have decided to migrate and start SQL Server Management Studio (SSMS).
Sign in to SQL Server using Windows and SQL authentication.
Thereafter, select the database that you wish to migrate.
Right-click on the database and choose Tasks >> Backup option.
Select Backup Type such as Full or Differential accordingly.
Browse a preferable destination path to store output data.
Under BackupType, select the checkbox as Copy-only backup.
Now you can see the selected location under Destination for the path of the new backup.
Click on the Ok button to backup the SQL database in the *.bak file extension.
Go to the destination path and get SQL Server database backup.

Phase 2: Restore Downloaded SQL Database to Destination Server

Now you need to move SQL Server backup BAK files to the destination SQL Server using multiple techniques i.e., USB Drive, External Drive, FTP, and Robocopy Command. following the commands carefully can help users complete all SQL Server Database Migration Steps without errors.

Start Microsoft SQL Server Management Studio.
Login to SQL Server using SQL and Windows authentications.
Right-click on SQL database and choose the Restore Database option.
Click on the Device option and browse a device location.
Navigate to the folder which has .bak files.
Select a database having a BAK extension and press the Ok button.
Now you are ready to migrate SQL Server database, see the message restored successfully.

Note: You can repeat above mentioned two phases for completing the SQL server database migration process for all databases. Now you have successfully moved your SQL Server database, but you cannot use it because your login details are not yet connected with migrated associated database(s). Hence, first, verify your SQL logins.

Phase 3: Verify SQL Server Logins

Open SQL Server Management Studio at the destination server.
Choose Server >> Security >> Logins here you can see previous logins are not transferred here.
So, choose Server >> Databases >> Your Database >> Security >> Users, now you can correct the logins associated with the database.
If you have one or two users then you can also delete the users associated with server databases.
Go to Servers >> Databases >> Your Database >> Security >> Users >> Recreate Users in Server >> Security >> Login.
Thereafter, map login details with the proper database.

Read Similar: Know Steps to Repair Corrupted MDF File After SQL Server Migration

SQL Migration Checklist to Keep in Mind

Now, we are going to understand the migration checklist for SQL Server database to make the entire process quite simple and easy for even beginner users. All of the above mentioned SQL Server data migration techniques works quite well in case uses need a reliable solution. Still, users must select the solution based on their needs.

Planning the Entire Migration – Data migration techniques in SQL Server are quite crucial as it plays a major role. Users must plan the entire migration in advance to make sure nothing goes wrong when they execute the actual migration.

Backup Entire SQL Data – After planning, simply backup all your SQL server data to make sure that nothing goes wrong. This backup file acts as an extra layer of security to be prepared for any disastrous situation & never be a victim of data loss.

Test Migration Script – Always test your migrations scripts before you step into the real world. Make sure to monitor any mistakes here, in the scripts. Thai way users can have ample time to fix any faults, typos, or errors present in the scripts. It acts as a pilot project.

Track The Progress – Tracking the progress as per the SQL Server migration plan is what users must follow. It helps in comparing the expected and actual progress of the SQL migration process. This helps in further learning, & understanding the behavior of the DB.

Post- Migration – After complete migration, users must check for the database working. Also, they must make other users aware of the changes they will observe. Testing the new environment to see if the expectations are met or not is vital here.

Some Useful Scripts to Collect SQL Server Database

To Check Size of Database
-- Procedure to check disc space

exec master..xp_fixeddrives

-- To Check database size

exec sp_helpdb [dbName]

or

use [dbName]

select str(sum(convert(dec(15,3),size)) / 130,10,3) + 'MB'

from dbo.sysfiles

GO

Check Database Properties
select

sysDB.database_id,

sysDB.Name as 'Database Name',

syslogin.Name as 'DB Owner',

sysDB.state_desc,

sysDB.recovery_model_desc,

sysDB.collation_name,

sysDB.user_access_desc,

sysDB.compatibility_level,

sysDB.is_read_only,

sysDB.is_auto_close_on,

sysDB.is_auto_shrink_on,

sysDB.is_auto_create_stats_on,

sysDB.is_auto_update_stats_on,

sysDB.is_fulltext_enabled,

sysDB.is_trustworthy_on

from sys.databases sysDB

INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

Know Linked Servers
select *

from sys.sysservers

Know List of Orphan Users
sp_change_users_login 'report'GO

SQL Server database migration steps & scripts for Index Rebuild & Index Reorganization

Index Rebuild: Scripts are used here to create new indexes and drop the existing ones.
Index Reorganize: Here, scripts are used to physically reorganize index leaf nodes.

— Index Rebuild Scripts —

USE [dbName]; GO ALTER INDEX ALL ON [ObjectName] REBUILD GO

— Index Reorganize Scripts —

USE [dbName]; GO ALTER INDEX ALL ON [ObjectName] REORGANIZE GO

After rebuilding & reorganizing, users also need to update the indexes as well. Therefore, it’s better for them to use the following command.

Sp_updatestats

After running all the scripts, if the user’s application & all associated applications are running fine, users can take the source database offline. Moreover, if not offline, they can transform the source database into the read Only form. For that, they can opt for the below mentioned command:

— Script to make the database Read Only —

USE [master] GO ALTER DATABASE [dbName] SET READ_ONLY WITH NO_WAIT GO ALTER DATABASE [dbName] SET READ_ONLY GO

— Script to take the Database Offline —

EXEC sp_dboption N'dbName', N'offline', N'true' OR ALTER DATABASE [dbName] SET OFFLINE WITH ROLLBACK IMMEDIATE

Download SQL Server Database Migration Utility – Best Plan

Download and install a third-party software named SQL Server Database Migration Tool which is a fully automated tool for completing SQL database migration steps. Moreover, the tool comes with several advanced features that make it valuable to handle all types of migration situations.

Download Now Purchase Now

Migrate the Live SQL Server database to another live SQL Server.
Move SQL Server database between two SQL servers (upgrade and downgrade)
Facility to choose only required items for SQL Server migration.
Separate option to migrate SQL data with Schema or Schema and Data.
The tool is also capable to repair SQL database files before migration.

SQL Server Database Migration Steps

Start the suggested software and press the Open button.
Select a migration mode from Online Mode and Offline Mode.
After that, select a server and choose the authentication type.
Select the Source Database from your current databases.
After choosing a database, click on Ok to continue.
The scanning process is completed successfully, click on Close.
Now you can see SQL database items in the software panel.
Click on any item for previewing the associated data.
Afterward, press the Export button from the software GUI for SQL Server migration.
Select an option to export as SQL Server database option.
Choose the server name and authentication type.
Select Create new database option as shown in the screenshot.
After that, choose Collation Settings.
Choose the required items from the SQL database.
Select Export with Schema & Data option and Export button.
SQL Server data is exported successfully.

Conclusion

We have discussed details about SQL Server database migration steps using both manuals as well as automatic methods. We have also explained the types of migration in SQL Server. If the manual method failed to execute SQL Server migration between two different servers, then you can go with the suggested third-party software. Also, SQL Server migration plan must be well researched to not miss out on any detail.