Cross database dump and load

SQL Enterprise Manager contains a Database/Object Transfer tool designed to ease the pain of transferring databases and individual objects from one place to another. This tool generates a script to recreate the selected objects and data in the target database. In most cases, the transfer occurs seamlessly. However, there are a number of scenarios in which it will generate errors.

The script generator is not intelligent enough to resolve dependencies such as tables with foreign keys or stored procedures that depend on the existence of other stored procedures. Developers are usually forced to invest the time and effort writing scripts manually to recreate objects in the proper order.

Another common solution is to use database dumps to transfer the whole database, including all objects and data, to the target location. This solution is not without problems either. For example:

• Database users and groups are not linked with correct logins on the target location (not a problem if the target database is on the same server).
• The logical page (lpage) and segment map (segmap) structures on the source and target databases must be compatible.

As a preface to a solution, let us first explore the technical background of these problems.

Synchronizing users and groups

The sysUsers table stores information about database users and groups. The suid column in this table references the primary key in the sysLogins table, but is without foreign key constraint because system users (dbo and guest) and group (public) do not have to be linked to a login).

Failed synchronization between sysUsers and sysLogins

Thus logins are stored in the master database (which is server specific) and users are stored in the database. Here we have the root of the problem: when you transfer a database from one server to another, the contents of sysUsers.suid will point to wrong, or even non-existent records in the sysLogins table on the new server.

Before version 6.5, the only way to handle this problem was to manually edit these tables. Microsoft designed the sp_change_users_login stored procedure to reduce the manual labor required for this process.

If you use Auto_Fix as the first parameter in this stored procedure, the procedure will loop through all users and compare the contents of the name columns in sysLogins and sysUsers. If the names do not match, the procedure will find the login with the matching name and adjust sysUsers.suid:

sp_change_users_login Auto_Fix

If you use the Report parameter instead, the procedure will only report changes that are doable by the Auto_Fix method:

sp_change_users_login Auto_Fix

In cases when sysUsers.name does not have a matching sysLogins.name, the administrator must join records manually:

sp_change_users_login Update_one, ‘Dejan’, ‘DejanS’

Note the use of the Update_One parameter with matching user name and login in this case.

Segment compatibility

Another issue usually neglected is compatibility between logical page (lpage) and segment map (segmap) structures in the source and the target databases.

The sysUsages table stores information about device segments used by the database. You can query this table or use sp_helpdb to get segment information:

/*-----------------------------
exec sp_helpdb 'adv_main'
select * from sysusages where dbid = db_id('adv_main')
-----------------------------*/

name       db_size       owner    dbid  created     status
---------- ------------- ------- ------ ----------- ---------------
adv_main       220.00 MB sa           6 Feb 23 1998 no options set

device_fragments           size          usage
-------------------------- ------------- ---------------
NAS_Advancing_D1               100.00 MB data only
NAS_Advancing_L1                60.00 MB log only
adv_main_data_2                 20.00 MB data only
adv_main_log_2                  10.00 MB log only
adv_main_data_2                 30.00 MB data only

dbid   segmap      lstart      size        vstart
------ ----------- ----------- ----------- -----------
6      3           0           51200       16777216
6      4           51200       30720       33554432
6      3           81920       10240       83886080
6      4           92160       5120        100663296
6      3           97280       15360       83896320

In the device_fragments section you can see ‘history’ of the database. The DBA must type and execute multiple Create and Alter commands to create a compatible database on target server.

Fortunately, there are two methods to ensure this compatibility with far less manual labor.

The Two Devices method

Compatibility will be preserved with minimal effort if we continue to use a single device for data pages and a single device for log pages per database. The DBA can even expand these devices. They will have new segment fragments, but those fragments will be continuous and segment mapping on logical pages will still be compatible.

If the DBA decides to transfer or restore a database using a backup file, he has to create one device for data pages and one device for log pages. The size of these devices should be equal to or larger than the size of the original devices. The database load will not encounter problems since devices created in this way are compatible.

Microsoft provides the sp_coalesce_fragments stored procedure to combine these sequential segments into one large segment.

The Database Structure Replication Method

If the DBA decides to create more (or fewer) than one data page device and one log page device per database, ensuring compatibility becomes much more complex and must include the following steps:

Exec sp_help_revdatabase 'adv_main'

The result will look similar to the following:

/********1*********2*********3*********4*********5*********6**

Reverse generated at 1998/02/28  17:11:29:033

Server / Database / Default sortorder ID :

P200 / adv_main / 52

DBName                         FromLPage   ToLPage     segmap
------------------------------ ----------- ----------- -----------
adv_main                       0           51199       3
adv_main                       51200       81919       4
adv_main                       81920       92159       3
adv_main                       92160       97279       4
adv_main                       97280       112639      3

@@version:  Microsoft SQL Server  6.50 - 6.50.258 (Intel X86)

*********1*********2*********3*********4*********5*********6**/

go

USE master

go

-----------------  Space and Log allocations  ---------------

CREATE  Database  adv_main

       on  NAS_Advancing_D1  =  100  -- 51200  of two Kb pages

go

ALTER   Database  adv_main

       on  NAS_Advancing_L1  =  60  -- 30720  of two Kb pages

go

EXECute sp_logdevice adv_main ,NAS_Advancing_L1

go

ALTER   Database  adv_main

       on  adv_main_data_2  =  20  -- 10240  of two Kb pages

go

ALTER   Database  adv_main

       on  adv_main_log_2  =  10  -- 5120  of two Kb pages

go

EXECute sp_logdevice adv_main ,adv_main_log_2

go

ALTER   Database  adv_main

       on  adv_main_data_2  =  30  -- 15360  of two Kb pages

go

---------------------  DB Options  -------------------

EXECute sp_dboption  adv_main ,'ANSI null default'

                              , false

EXECute sp_dboption  adv_main ,'dbo use only'

                              , false

EXECute sp_dboption  adv_main ,'no chkpt on recovery'

                              , false

/***

EXECute sp_dboption  adv_main ,'offline'

                              , false

***/

/***

EXECute sp_dboption  adv_main ,'published'

                              , false

***/

EXECute sp_dboption  adv_main ,'read only'

                              , false

EXECute sp_dboption  adv_main ,'select into/bulkcopy'

                              , false

EXECute sp_dboption  adv_main ,'single user'

                              , false

/***

EXECute sp_dboption  adv_main ,'subscribed'

                              , false

***/

EXECute sp_dboption  adv_main ,'trunc. log on chkpt.'

                              , false

go

--------------------  sa  is  dbo  ---------------------

go

---

2. The DBA creates devices on the target server using the sizes and names of the devices on the original server.

In some cases, the script created by sp_help_revdatabase can be simplified if the DBA executes sp_coalesce_fragments first. This stored procedure is also based on the idea that database fragments created subsequently on the same device are actually continuous and can be treated as one segment.

These two architectural problems have prevented Microsoft from recommending backups as a viable solution for transferring databases. However, with a solid understanding of the technical background to these difficulties, you can use this flexible tool with confidence.

Published in SQL Server Pro.