Microsoft KB Archive/82946

= INFO: Moving Databases Between Servers Using DUMP/LOAD =

Article ID: 82946

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q82946



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: kbinfo kbusage KB82946

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.