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
print

3 thoughts on “SQL Server – Database Migration – Move Database from One server to another Server

  1. Author’s gravatar

    Hi Mr Arun
    while transfering a database from one server to other is there any difference in following procedures
    1) backup from source and restore at dest ,
    2) detach db files and attach at dest ?

    waiting your reply
    thanks in advance

  2. Author’s gravatar

    From above question i meant any affect on rights will happen on database or server or on user security level like that. i think while restoring identity cols of tables has to set again? that won’t happen while attach and detach? or any affects like that.

    deepak

    1. Author’s gravatar

      No. identity column will not get resetted during backup and restore.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


two + 1 =