INF: Moving Databases Between Servers Using DUMP/LOAD

ID: Q82946

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2x

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

FRAGMENTED is created on SERVER2 with the following commands:


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

Selecting segmap, lstart, and size from SYSUSAGES, where


   dbid = DB_ID( 'fragmented' )

results in the following:


   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

NOTE: A segmap value of 3 indicates a data segment and a segmap value of 4 indicates a log segment. Lstart indicates the starting logical address for this segment and size is the total number of 2K data pages in the segment.

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.

Additional query words: Dumping loading Windows NT

Keywords : kbusage SSrvGen SSrvWinNT
Version : 4.2 | 4.2 4.21 4.21a
Platform : OS/2 WINDOWS
Issue type :