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 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.
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.
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.
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.
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:
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.
To use the sp_detach_db and sp_attach_db stored procedures, follow these steps:
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:
Collapse this tableExpand this table
File name extension
Description
.mdf
Primary data file
.ndf
Secondary data file
.ldf
Transaction log file
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:
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.
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:
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:
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.
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.
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.
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:
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:
"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.
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.
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:
BUG: DBO user does not display in Enterprise Manager
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:
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:
Open the SQL Server Enterprise Manager, and then expand
the Management folder.
Expand SQL Server Agent, and then right-click Alerts, Jobs, or Operators.
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:
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.
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.
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.
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:
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."
Back to the top
