INF: Moving Databases Between Servers Using DUMP/LOAD

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server versions 4.2, 4.21, and 4.21a

SUMMARY

When moving databases between servers using the DUMP and LOAD commands, the device fragments of the target database must match the device fragments of the source database not only in size, but also in sequence. If the device fragments are inconsistent, the integrity of the LOAD may be compromised.

MORE INFORMATION

A database may be fragmented on a single device or across multiple devices. The fragmentation is dependent on the way in which the database is created, logged, and altered.

For example, consider a 12-MB database consisting of 8 MB of data and a 4-MB log. The database FRAGMENTED is created on SERVER1 with the following commands:

   CREATE DATABASE fragmented
   ON dev1 = 2, dev2 = 2, dev3 = 2, dev4 = 2, log1 = 2, log2 = 2
   EXEC SP_LOGDEVICE fragmented, log1
   EXEC SP_LOGDEVICE fragmented, log2

   CREATE DATABASE fragmented
   ON dev1 = 4, log1 = 4, dev2 = 4
   EXEC SP_LOGDEVICE fragmented, log1

   dbid = DB_ID( 'fragmented' )

   SERVER1

   segmap  lstart  size
     3         0   1024
     3      1024   1024
     3      2048   1024
     3      3072   1024
     4      4096   1024
     4      5120   1024

   SERVER2

   segmap  lstart  size
     3         0   2048
     4      2048   2048
     3      4096   2048

Comparing the values in the two tables reveals that only the first two segments on SERVER1 correctly map to the first segment on SERVER2. The remaining segments map to the wrong segmap for the corresponding logical addresses. In other words, SERVER1's third segment (lstart=2048) maps to SERVER2's second segment (lstart=2048). However, SERVER1's third segment is for data, and SERVER2's second segment is for log.

Because of the way LOAD works with SQL Server, some of SERVER1's data may be loaded into SERVER2's log area, and some of SERVER1's log may be loaded into SERVER2's data area. SQL Server merely copies page-for-page when doing a load; for optimum speed, no checks are made against a page's segment to prevent the mismatch above.

The LOAD will succeed with no indications of errors during the load or upon completion. However, running DBCC CHECKALLOC indicates a problem, most commonly an 2558 error (if one exists). Microsoft recommends running DBCC CHECKDB and DBCC CHECKALLOC after the completion of all loads.