Migrating a SQL Server Instance

By:   |   Comments (3)   |   Related: > Upgrades and Migrations


I need to migrate an instance of SQL Server 2000 due to a hardware issue that I am having.  What are the steps that I should take before, during and after the process?  What pit falls should I watch out for?


Let's walk through the general steps to migrate a SQL Server 2000 instance.  Based on your environment and the specific circumstances, a different set of steps or additional steps may be needed, but below outlines the general steps that are needed:

Preliminary Migration Steps

  • Plan - Pull together a plan for all of the steps that you need to take, who is responsible for individual tasks, general time frames, start times, end times, etc.
  • Notification - Notify the users of the issue and the general amount of downtime that will be needed directly and via your change management process.
  • Team - Determine the team members to perform the migration or determine if you are going to fly solo.  One item that should not be overlooked is application testing.  If you cannot perform this task, see who on the team would be best suited for this critical piece of the process.
  • Hardware - Determine what hardware is needed or available for the migration.  Depending on the uptime requirements for your application, consider doing some research to determine if the server will be able to support the load until the next downtime when the server can be replaced.
  • Review - Since the original issue, review the Windows, SQL Server, Application, etc. error logs to ensure no unexpected errors have occurred.
  • Installation Media - Find your Windows, SQL Server, third party and service pack media (CD's and DVD's).
  • Versions - Capture the Windows, SQL Server, application, etc. versions to be sure to install the same versions on the new machine.
  • Test Backup - Backup a subset of the user defined databases to test on another machine (development, your desktop, etc.).
  • Test Restore - Restore the user defined database on another machine (development, your desktop, etc.).
  • Shrink Databases - Shrink the user defined databases before starting the backup process.
  • New SQL Server - Setup the new SQL Server ahead of time to minimize the amount of steps that need to be taken during the downtime.
  • Preliminary Steps - Depending on the situation, execute as many of the steps as possible on the new SQL Server ahead of time to minimize the downtime needed.

Migration Steps

  • Review - Since the original issue, review the Windows, SQL Server, Application, etc. error logs to ensure no unexpected errors have occurred.
  • Read-only Mode - Set the user defined databases to read-only mode.
  • Online Database Backups - Perform SQL Server online backups and verify the backups.
  • Verifying Backups - Verifying Backups with the RESTORE VERIFYONLY Statement.
  • Shut down SQL Server - Shut down the SQL Server Services.
  • File system backup - Perform a Windows file system backup.
  • Windows Configurations - Make the needed machine name or DNS changes.
    • Old SQL Server - May need to put the original machine in a work group, change the machine name, IP address and reboot.
    • New SQL Server - Enter the machine name, domain, IP address and reboot.
    • DNS - Validate the entries are correct.
  • Copy Process - Move the backups to the new SQL Server.
  • Database Restore - Restore the user defined databases to the new SQL Server.
  • SQL Server Validation - Validate the following items:
    • Restores - Databases are restored properly and no error messages are recorded in the SQL Server Error Log.
    • Security - Logins and users are mapped appropriately for both Windows and SQL Server Standard logins.
    • SQL Server Services - SQL Server services are using the correct domain account and the services are configured to auto start.
    • SQL Server Jobs - Validate that the Jobs are migrated properly and are referencing the correct SQL Server.
    • SQL Server DTS Packages - Validate that the DTS Packages have been migrated.
  • Connection String Changes - If the machine name is changed, then change the application connection strings.
  • Testing - Test your applications to validate they are working properly.
  • Production Promotion - Promote the SQL Server to the production responsibilities.
  • Notification - Notify the users that the SQL Server is back up and running.

Post Migration Steps

  • Monitor the SQL Server - Validate that operational and business needs are being met by checking backups, performance, SQL Server Error Logs, feedback from users, etc.
  • Lessons Learned - Inevitably you will run into a snag, record those items and learn from them for the next time you have to perform this sort of process.
Next Steps
  • If you have the luxury to test the migration process prior to performing the migration in production, take advantage of it and practice the migration.  Note any special steps you need to take for your environment.
  • One potential opportunity to test the migration process is to use a virtual machine.  This may be a simple and easy way to test the process and validate all of the steps taken are correct and nothing is missing.
  • The value in testing the process is to work out the kinks, get a practice migration under your belt before the real thing and to have a good understanding of the time needed to perform the migration.
  • In most cases, these sorts of migrations are executed under duress.  Just keep your cool and 'check twice and click once' during all of the steps that you take.
  • If you also have the luxury to move to a new piece of hardware take advantage of it and try to keep the original server around for a week to 3 months to make sure you have not missed any code for month and quarter end processes.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, January 26, 2015 - 1:44:06 PM - John Back To Top (36059)

I'm looking at moving our SQL Server installation from it's exiting server ( Server 2003 Standard ) to a new one ( Server 2008R2 64 bit ) and your plan covers the bases better than anything else I've been able to find - Great Job !!

I do have a question though - I see it's dated  - 5/17/2007 .... are there any changes/updates for a current environment ?

is the copy Database Wizard a viable option ( issues with System databases ? ) ??

Monday, August 25, 2014 - 1:33:42 PM - Jeremy Kadlec Back To Top (34250)


I would probably start here - http://msdn.microsoft.com/en-us/library/ms191545.aspx.

Thank you,
Jeremy Kadlec
Community Co-Leader

Saturday, August 23, 2014 - 9:41:03 AM - Faisal Back To Top (34242)

Hi Jeremy,

Could you pls. explain the steps on how we can migrate a sql cluster instance from old cluster hardware to a new cluster hardware. The old names should remain the same on this new hardware. 



get free sql tips
agree to terms