You are currently offline, waiting for your internet to reconnect

How to move databases between computers that are running SQL Server

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q314546
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
If you are using SQL Server 2005, you can use the same method to migrate data from SQL Server 7.0 or from SQL Server 2000. However, the management tool in SQL Server 2005 differs from the management tool in SQL Server 7.0 or in SQL Server 2000. You should use SQL Server Management Studio instead of the SQL Server Enterprise Manager. Additionally, you should use the SQL Server Import and Export Wizard (DTSWizard.exe) instead of the Data Transformation Services Import and Export Data Wizard.

To move user databases, use one of the following methods.  

Method 1: Back up and restore the user databases
Back up the user databases on the source server, and then restore the user databases to the destination server.
  • The database can be used when the backup is in process. If users perform INSERT, UPDATE, or DELETE statements on the database after the backup is complete, the backup will not contain these changes. If you must transfer all changes, you can transfer the changes with minimal downtime if you perform both a transaction log backup and a full database backup.
    1. Restore the full database backup on the destination server and specify the WITH NORECOVERY option.

      Note To prevent additional database modifications, direct users to stop database activity on the source server.
    2. Perform a transaction log backup and restore the transaction log backup to the destination server by using the WITH RECOVERY option. Downtime is limited to the time of the transaction log backup and restore. For more information, see the "RESTORE" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.
  • The database on the destination server will be the same size as the database on the source server. To reduce the size of the database, you must either reduce the source database before you perform the backup, or reduce the destination database after the restore is completed. For more information, see the "Shrinking a Database" sub-topic in the "Creating and Maintaining Databases" heading of SQL Server Books Online.
  • If you restore the database to a different file location than the source database, you must specify the WITH MOVE option. For example, on the source server the database is in the D:\Mssql\Data folder. The destination server does not have a D drive, and you want to restore the database to the C:\Mssql\Data folder. For more information about how to restore a database to a different location, click the following article numbers to view the articles in the Microsoft Knowledge Base:
    221465 Using the WITH MOVE option with the RESTORE statement
    304692 Moving SQL Server 7.0 databases to a new location with BACKUP and RESTORE
  • If you want to overwrite a pre-existing database on the destination server, you must specify the WITH REPLACE option. For more information, see the "RESTORE" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.
  • Depending on the version of SQL Server to which you restore, the character set, sort order, and Unicode collation may have to be the same on both the source and destination servers. For more information, see the "Note about collation" later in this section.


Method 2: Use the sp_detach_db and sp_attach_db stored procedures 
To use the sp_detach_db and sp_attach_db stored procedures, follow these steps:
  1. Detach the database on the source server by using the sp_detach_db stored procedure. You must copy the .mdf, .ndf, and .ldf files that are associated with the database to the destination server. See this table for a description of the file types:
    File name extensionDescription
    .mdf Primary data file
    .ndf Secondary data file
    .ldf Transaction log file
  2. Attach the database on the destination server by using the sp_attach_db stored procedure and point to the files that you copied to the destination server in the previous step. For more information about how to use these methods, click the following article number to view the article in the Microsoft Knowledge Base:
    224071 How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
  • The database is inaccessible after the detach, and you cannot use the database when you copy the files. All data that is contained in the database at the point in time of the detach is moved.
  • The character set, sort order, and Unicode collation may have to be the same on both servers when you use the Attach or Detach method. For more information, see the following note about collation.
Note If you move databases between SQL Server 7.0 servers by using the backup and restore or Attach and Detach methods, the character set, sort order, and Unicode collation must be the same on both servers. If you move databases from SQL Server 7.0 to SQL Server 2000 or between SQL Server 2000 servers, the database maintains the collation of the source database. This means that if the destination server that is running SQL Server 2000 has a different collation than the source database, the destination database has a different collation than the destination server's master, model, tempdb, and msdb databases. For more information, see the "Mixed Collation Environments" topic in SQL Server 2000 Books Online.



Method 3: Use the Import and Export Data Wizard to copy objects and data between SQL Server databases
You can copy a whole database or selectively copy objects and data from the source database to the destination database by using the Data Transformation Services Import and Export Data Wizard.
  • The source database may be used during the transfer. If the source database is used during the transfer, you may see some blocking when the transfer is in progress.
  • When you use the Import and Export Data Wizard, the character set, sort order, and collation do not have to be the same between the source server and destination server.
  • Because unused space in the source database does not move, the destination database may not have to be as large as the source database. Similarly, if you move only some objects, the destination database may not have to be as large as the source database.
  • SQL Server 7.0 Data Transformation Services may not transfer text and image data longer than 64 KB correctly. This problem does not apply to the SQL Server 2000 version of Data Transformation Services. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
    257425 FIX: DTS Object Transfer does not transfer BLOB data greater than 64 KB


Step 2: How to transfer logins and passwords
If you do not transfer the logins from the source server to the destination server, your current SQL Server users may be unable to log on to the destination server. You can transfer the logins and passwords by using the instructions in the following Microsoft Knowledge Base article:
246133 How to transfer logins and passwords between instances of SQL Server
The default databases for the logins on the destination server may differ from the default database for the logins on the source server. You can change the default database for a logon with the sp_defaultdb stored procedure. For more information, see the "sp_defaultdb" sub-topic of the "Transact-SQL Reference" topic in SQL Server Books Online.


Step 3: How to resolve orphaned users
After you transfer logins and passwords to the destination server, users may be unable to access the database. Logins are associated to users by the security identifier (SID), and if the SID is inconsistent after you move a database, SQL Server may deny the user access to the database. This problem is known as an orphaned user. If you transfer logins and passwords by using the SQL Server 2000 DTS Transfer Login feature, you will probably have orphaned users. Additionally, integrated logins granted access on a destination server in a different domain than the source server cause orphaned users.
  1. Look for orphaned users. Open Query Analyzer on the destination server, and then run the following code in the user database that you moved:
    exec sp_change_users_login 'Report'
    The procedure lists any orphaned users who do not link to a logon. If no users are listed, skip step 2 and step 3 and go to step 4.
  2. Resolve the orphaned users. If a user is orphaned, database users can log on to the server successfully but will not have permission to access the database. If you try to grant the logon access to the database, you receive the following error message because the user already exists:
    Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
    For more information about how to resolve orphaned users, click the following article numbers to view the articles in the Microsoft Knowledge Base:
    240872 How to resolve permission issues when you move a database between servers that are running SQL Server

    The following Microsoft Knowledge Base article contains instructions about how to map the logins to the database users and resolves users orphaned from standard SQL Server logins and integrated logins:

    274188 "Troubleshooting Orphaned Users" topic in Books Online is incomplete

    This article describes how to use the sp_change_users_login stored procedure to correct the orphaned users one by one. The sp_change_users_login stored procedure only resolves users orphaned from standard SQL Server logins.
  3. If the database owner (dbo) is listed as orphaned, run this code in the user database:
    exec sp_changedbowner 'sa'
    The stored procedure changes the database owner to dbo and resolves the issue. To change the database owner to another user, run sp_changedbowner again with the user who you want. For more information, see the "sp_changedbowner" sub-topic in the "Transact-SQL Reference" topic of SQL Server Books Online.
  4. If your destination server is running SQL Server 2000 Service Pack 1, the database owner user may not be in the list in the Users folder in Enterprise Manager after you perform the attach or restore or both. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
    305711 BUG: DBO user does not display in Enterprise Manager
  5. You may receive the following error message if you try to change the system administrator (SA) password through Enterprise Manager if the logon that was mapped to dbo on the source server does not exist on the destination server:
    Error 21776: [SQL-DMO] The name 'dbo' was not found in the Users collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.
    For more information, click the following article number to view the article in the Microsoft Knowledge Base:
    218172 Cannot change SA password in Enterprise Manager
Warning If you restore or attach the database again, the database users may be re-orphaned and you have to repeat step 3.

Step 4: How to move jobs, alerts, and operators
Step 4 is optional. You can generate scripts for all jobs, alerts, and operators on the source server, and then run the script on the destination server.
  • To move jobs, alerts, and operators, follow these steps:
    1. Open the SQL Server Enterprise Manager, and then expand the Management folder.
    2. Expand SQL Server Agent, and then right-click Alerts, Jobs, or Operators.
    3. Click All Tasks, and then click Generate SQL Script. For SQL Server 7.0, click Script All Jobs, Alerts, or Operators.
    You will have the option to generate scripts for All Alerts, All Jobs, or All Operators based on the item that you right-click.
  • You can move jobs, alerts, and operators from SQL Server 7.0 to SQL Server 2000 or between computer servers that are running SQL Server 7.0 and SQL Server 2000.
  • If you have operators that are set up for notification by SQLMail on the source server, you have to set up SQLMail on the destination server to have the same functionality. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
    263556 How to configure SQL Mail


Step 5: How to move DTS packages
Step 5 is optional. If DTS packages are stored on the source server in the SQL Server or the repository, you can move them if you want. To move DTS packages between servers, use one of the following methods.

Method 1: Save the DTS package on SQL Server
  1. Save the DTS package on the source server to a file, and then open the DTS package file on the destination server.
  2. Save the package on the destination server to the SQL Server, or to the repository.
    Note You have to move each package one by one in separate files.


Method 2: Open the DTS package in DTS Designer
  1. Open each DTS package in the DTS Designer.
  2. On the Package menu, click Save As.
  3. Specify the destination SQL Server.
Note The package may not run correctly on the new server. You may have to change the package, and change any references in the package to connections, files, data sources, profiles, and other information that is located on the old source server, to reference the new destination server. You must make these changes on a package by package basis based on the design of each package.

Step 6: Change the sp_configure settings to match the previous computer
You may have to change the settings so that they match the settings in the new computer. For example, if the new computer has more memory or if it is running different SQL instances and applications, you may want to change the min and max server memory settings or the AWE setting. You may have to change the MAXDOP setting if the number of CPU cores that are exposed to the operating system has changed.
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
dump load
Properties

Article ID: 314546 - Last Review: 07/12/2013 07:34:00 - Revision: 12.1

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

  • kbsqlsetup kbhowtomaster KB314546
Feedback
dy>e="display:none;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">