SQL Server Database Migration Steps and Types

Mark Regan ~ Modified: 18-05-2023 ~ Migrate Data ~ 8 Minutes Reading

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 SQL Server database migration best practices.

As we all know, SQL Server database 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.

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

SQL Server Database 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. 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

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.

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

Setup your target drive, virtual drive, on-premise, and hosted server.
Ensure enough free drive space on your target drive.
Purchase the licenses for the latest required versions.
Confirm the log file and data location on the target server.
Collect your current SQL Server database information including all properties.

Manual Method for SQL Server Database Migration Using SSMS

Use SQL Server Management Studio (SSMS) for completing SQL Server database migration steps manually free of cost. Experts recommend SSMS for small database migration. You can easily complete this process within 3 phases.

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.

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

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

Read Similar: Techniques to Recover SQL Server Database from Suspect Mode

Download SQL Server Database Migration Tool

Download and install a third-party software named SQL Server Database Migrator which is a fully automated tool for completing SQL Server database migration steps. 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 Step by Step

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.
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 database 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 migrate your data between servers, then you can go with the suggested third-party software.