SQL Server – Database Migration – Move Database from One server to another Server

Pre-requisites:

  • Analyze the disk space in the target server.
  • Confirm the data and log file location in the target server.
  • Collect the information about database properties
  • Collect the information about the application dependencies
  • Collect information about the logins and & users
  • Check the database for Orphan users
  • Check the SQL Server for dependent objects (SQL jobs , Linked Servers)
  • Check the Maintenance Plan for the database

Database Migration Checklist:

  • Stop the application services
  • Change the database to read-only mode (Optional)
  • Take the latest backup of all the databases involved in migration
  • Restore the databases on the target server on the appropriate drives
  • Cross check the database properties as per the database property script output, change the database properties as per the pre migration- checklist
  • Execute the output of Login transfer script on the target server, to create logins on the target server
  • Check for Orphan Users and Fix Orphan Users
  • Execute DBCC UPDATEUSAGE on the restored database.
  • Rebuild Indexes (Optional) as per the requirement and time window you can execute this option.
  • Update Index statistics
  • Re-compile Procedures
  • Start the application services, check the application functionality and check the Windows event logs.
  •  Check the SQL Server Error Log for login failures and other errors