Moving a Database

You can use SQL Enterprise Manager or the LOAD DATABASE statement to move a database to another device, or to another server as long as the same character set and sort order are used on both servers. (If the character sets and sort orders differ, use SQL Transfer Manager instead.)

The recipient database must be as large as, or larger than, the database to be loaded. If the recipient database is too small, SQL Server displays an error message that gives the required size.

If a database will be loaded into a newly created database, use the FOR LOAD option when creating the new database. This specifies that the data pages will not be zero-initialized because the only operations allowed against that database are ALTER DATABASE FOR LOAD or LOAD DATABASE. Users cannot access the database until after a successful load. Any unused data pages will be initialized by the database load.

Note When you create a database with the FOR LOAD option, after completing the load(s) you must clear the database option DBO Use Only by using SQL Enterprise Manager or sp_dboption. Until you do this, users other than the DBO may not access the database.

    To move a database to another database device

1. Create the new device using SQL Enterprise Manager or the DISK INIT statement.

   Or, use SQL Enterprise Manager to make sure the database device exists, or sp_helpdevice to make sure the database device is listed in sysdevices.

2. Use SQL Enterprise Manager or the CREATE DATABASE statement to create the new database on the new database device.
   • The new database name must be unique.
   • Use the FOR LOAD option.
   • Do not place the transaction log on a separate device.
   • The new database must be equal in size or larger than the database that will be loaded.
3. Dump the old database using SQL Enterprise Manager or the DUMP DATABASE statement.
4. Load the dump into the new database using SQL Enterprise Manager or the LOAD DATABASE statement.
5. Clear the DBO Use Only option for the new database using SQL Enterprise Manager or sp_dboption.
6. If appropriate, move the transaction log to a separate device. Use SQL Enterprise Manager or the ALTER DATABASE statement, and then the sp_logdevice system procedure.

   For information about moving the transaction log, see Chapter 6, Managing Databases.

7. Drop the old database using SQL Enterprise Manager, the DROP DATABASE statement, or sp_dbremove.

    To move a database to another server

1. Follow the same procedure as for moving a database to another database device.
2. If the syslogins table on the new server is different from the syslogins table on the old server, the ownership of databases and database objects will be incorrect. Use aliases to map owners to their objects on the new SQL Server.

If the dumped database uses segments to store particular tables or indexes, the new database must include devices of the same size and page order for these segments and their related data in order for the LOAD statement to work correctly. Execute the sp_helpdb system stored procedure with the database name to see the size and fragments. Select from the sysusages system table to determine the order of the device fragments. Then re-execute the CREATE DATABASE statement and the ALTER DATABASE statement to re-establish the fragments.

The device fragments that the new database uses for data and for the log must appear in the same order and have the same amount of space as the fragments in the original database. For example, if the original database had 10 MB of data (on any number of fragments), followed by 3 MB of log, followed by 6 MB more of space for data, the new database must have the same order by space and usage: 10 MB for data, 3 for log, and 6 (or more) for data. In this case, the total size of the destination database must be at least as large and have at least as many fragments (assigned in the same order and have the same uses) as the load.