Home » Blog » Migrate Data » SQL Server Database Migration Steps and Types

SQL Server Database Migration Steps and Types

  author
Rollins Duke   
Published: March 7th, 2024 • 10 Min Read

SQL Server Migration

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.
Table of Content Hide
  1. SQL Server Database Migration Issues and Reasons
  2. Types of Database Migration in SQL Server
  3. Pre-Migration Checklist
  4. Manual Method for SQL Server Migration Using SSMS
  5. Some Useful Scripts to Collect SQL Server Database
  6. Download SQL Server Database Migration Tool
  7. Conclusion

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. 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.

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.

Phase 1 Backup SQL Database from Source Server

  1. Login to your source SQL Server that you have decided to migrate and start SQL Server Management Studio (SSMS).
  2. Sign in to SQL Server using Windows and SQL authentication.
  3. Thereafter, select the database that you wish to migrate.
    start ssms
  4. Right-click on the database and choose Tasks >> Backup option.
    ssms backup option
  5. Select Backup Type such as Full or Differential accordingly.
  6. Browse a preferable destination path to store output data.
  7. Under BackupType, select the checkbox as Copy-only backup.
  8. Now you can see the selected location under Destination for the path of the new backup.
    migrate sql server database
  9. Click on the Ok button to backup the SQL database in the *.bak file extension.
  10. 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.

  1. Start Microsoft SQL Server Management Studio.
  2. Login to SQL Server using SQL and Windows authentications.
  3. Right-click on SQL database and choose the Restore Database option.
  4. Click on the Device option and browse a device location.
  5. Navigate to the folder which has .bak files.
    sql server database migration
  6. Select a database having a BAK extension and press the Ok button.
  7. 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

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

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

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

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

Download SQL Server Database Migration Utility

Download and install a third-party software named SQL Server Database Migrator 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

  1. Start the suggested software and press the Open button.
    sql server migration tool
  2. Select a migration mode from Online Mode and Offline Mode.
    two modes for sql server database migration
  3. After that, select a server and choose the authentication type.
    select server
  4. Select the source database from your current databases.
    select database
  5. After choosing a database, click on Ok to continue.
    click ok
  6. The scanning process is completed successfully, click on Close.
    scanning
  7. Now you can see SQL database items in the software panel.
    view database
  8. Click on any item for previewing the associated data.
    preview data items
  9. Afterward, press the Export button from the software GUI for SQL Server migration.
    sql server database migration step by step
  10. Select an option to export as SQL Server database option.
    export type sql server
  11. Choose the server name and authentication type.
    select export server
  12. Select Create new database option as shown in the screenshot.
    select destination db
  13. After that, choose Collation Settings.
    select collation
  14. Choose the required items from the SQL database.
    select items
  15. Select Export with Schema & Data option and Export button.
    schema data
  16. SQL Server data is exported successfully.
    export done
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.