Article ID: 314546 - View products that this article applies to.
This article was previously published under Q314546
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
(http://msdn.microsoft.com/en-us/library/ms187580.aspx)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
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:
240867Database 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."
(http://support.microsoft.com/kb/240867/ )How to move, copy, and back up full-text catalog folders and files
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/320125/ )How to move a database diagram
Article ID: 314546 - Last Review: July 12, 2013 - Revision: 12.1