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

Leave a Reply

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


nine − = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>