Learn more about SQL Server tools


Latest from MSSQLTips

Migrating a SQL Server Instance

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (3)   |   Related Tips: More > 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.

Last Update: 5/17/2007

About the author
MSSQLTips author Jeremy Kadlec
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources

print tip Print  
Become a paid author

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Monday, January 26, 2015 - 1:44:06 PM - John Read The Tip

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 Read The Tip


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 Read The Tip

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. 



More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.