Article ID: 314546 - View products that this article applies to.
This article was previously published under Q314546
Expand all | Collapse all

Summary

This step-by-step article describes how to move Microsoft SQL Server user databases and most common SQL Server components between computers that are running SQL Server.

The steps that are described in this article assume that you will not move the master, model, tempdb, or msdb system databases. The steps provide different options for you to transfer logins and the most common components that are contained in the master and msdb databases.

For information about the specific items that are not transferred when you follow the steps in this article, see the "More information" section.

Note For SQL Server 2008, go to the Managing Metadata When Making a Database Available on Another Server Instance Microsoft Developer Network (MSDN) website.

Note Data migration from SQL Server 2000 to Microsoft SQL Server 2000 (64-bit) is supported. You can attach a 32-bit database to a 64-bit database by using the sp_attach_db system stored procedure or the sp_attach_single_file_db system stored procedure, or by using backup and restore in the 32-bit Enterprise Manager. You can move databases back and forth between the 32-bit and the 64-bit versions of SQL Server. You can also migrate data from SQL Server 7.0 by using the same methods. However, downgrading data to SQL Server 7.0 from SQL Server 2000 (64-bit) is not supported. A description of each method follows.

Step 1: How to move user databases

Step 2: How to transfer logins and passwords

Step 3: How to resolve orphaned users

Step 4: How to move jobs, alerts, and operators

Step 5: How to move DTS packages

Step 6: Change the sp_configure settings to match the previous computer

More information

You may also want to move other items such as linked servers, mirroring, replication, log shipping, full-text catalogs, named backup devices, maintenance plans, database diagrams, database snapshots, credentials and proxy accounts, endpoints, server scoped DDL triggers (such as a logon trigger), or other items involving either master or msdb. Examine the source server for these configurations and take steps to set them up manually on the destination server, if you want.

For more information about how to move full text components, click the following article number to view the article in the Microsoft Knowledge Base:
240867 How to move, copy, and back up full-text catalog folders and files
Database diagrams and backup and restore history are not moved if you follow the steps in this article. If you must move this information, move the msdb system database. For information about how to move the msdb database, see the Microsoft Knowledge Base articles that are referenced in the "Step 1: How to move user databases" section. If you move the msdb database, you do not have to follow "Step 4: How to move jobs, alerts, and operators" or "Step 5: How to move DTS packages."

References

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
320125 How to move a database diagram

Properties

Article ID: 314546 - Last Review: July 12, 2013 - Revision: 12.1
Applies to
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbsqlsetup kbhowtomaster KB314546

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com