sp_help_revdatabase (version 6.5)

Analyzes an existing database and creates a script that can be used to replicate the database structure on another server.

Syntax

sp_help_revdatabase [DBNamePattern]

where

DBNamePattern
Specifies a database name pattern. The name pattern must adhere to the LIKE operator standards for string arguments. The default is '%' (all databases will be affected).

For more information about the LIKE operator, see Wildcard Characters in the Microsoft SQL Server Transact-SQL Reference.

Remarks

The purpose of the sp_help_revdatabase stored procedure is to ease the task of building a database by ensuring the lpage (logical page) and segmap (segment map) structures are compatible with an existing database.

The output of this system stored procedure is a script of CREATE/ALTER DATABASE statements that match an existing database. The output also contains any necessary invocations of sp_logdevice to update sysusages.segmap. For every row in the sysusages system table, sp_help_revdatabase produces an ALTER DATABASE statement. With fewer rows in the sysusages system table, fewer ALTER DATABASE statements are produced by sp_help_revdatabase and fewer rows in sysusages make it easier to verify compatible logical page and segment map combinations between databases.

The database and device names in the generated script output can be edited in preparation for creating another database with compatible lpage and segmap structures. The output script might be unable to achieve compatible lpage and segmap structures when any fragment size in the original database is not an even multiple of 512 2K pages. When this procedure detects fragments with a size not evenly divisible by 512, sp_help_revdatabase includes in the output a cautionary comment indicating that the ALTER size in 1 MB units represents a rounded-up representation of the size as recorded in 2K pages in sysusages. You can avoid this problem if you code size-parameter values in even multiples of 512 2K pages when you issue DISK INIT statements.

System administrators sometimes load a database or table backup file from a production database into a database on another server. Requirements for successful cross-database dump and load activities are documented under the LOAD statement in the Microsoft SQL Server Transact-SQL Reference. One requirement is that the two databases have compatible lpage and segmap column information in the sysusages table. The target database must be created so the segmap for each range of logical pages (lpage) is compatible with the same information in the dumped database.

Important If the target and destination databases have different segmap structures, dumped or loaded databases can experience data access problems.

Two databases have compatible lpage and segmap structures if for every given lpage number they have the same segmap value. Fragment sizes and sequences are not an issue. If one database is larger than the other, some of the larger database's lpage numbers will not exist for the smaller database. A smaller database can be loaded into a larger database, but a larger database cannot be loaded into a smaller database.

For more information about logical pages, see the Microsoft SQL Server Administrator's Companion.

For more information about preparing to run sp_help_revdatabase, see sp_coalesce_fragments, earlier in this document.

For more information about altering a database, see the ALTER DATABASE statement in the Microsoft SQL Server Transact-SQL Reference.

For more information about performing a database load, see the LOAD statement in the Microsoft SQL Server Transact-SQL Reference.

For more information about performing a database backup, see the DUMP statement in the Microsoft SQL Server Transact-SQL Reference.

Example

This example shows the results of running sp_help_revdatabase after executing sp_coalesce_fragments. Since sp_coalesce_fragments does not generate any output, this example only shows the output from running sp_help_revdatabase.

SELECT
'dbname'=substring(db_name(ug.dbid),1,10)
,ug.segmap,ug.lstart -- page number within a database
,ug.size,ug.vstart -- page number within dv    
,'(vstart+size)' = ug.vstart + ug.size
,'device'=substring(dv.name,1,10)
FROM
master.dbo.sysusages ug,master.dbo.sysdevices dv 
WHERE ug.vstart between dv.low and dv.high     AND
dv.status & 2 = 2 --physical disk
AND
db_name(ug.dbid) in ('DB1','DB22','db_junk3')
ORDER BY ug.vstart
  

This is the results set:

dbname segmap lstart size vstart    (vstart+size) device     
------ ------ ------ ---- --------- ------------- ------
DB5         3      0 1024 520093696     520094720 dv9        
DB66        3      0 1536 520094720     520096256 dv9        
DB5         3   1024  512 520096256     520096768 dv9        
DB5         4   1536  512 536870912     536871424 dv8        
DB66        4   1536 1024 536871424     536872448 dv8        
DB5         4   2048  512 536872448     536872960 dv8        
DB777       3      0 1024 687865856     687866880 dv7        
DB777       3   2048  512 687866880     687867392 dv7        
DB777       4   1024 1024 704643072     704644096 dv6        
  

This is the output from running sp_help_revdatabase:

-- This is the command for executing sp_help_revdatabase
EXECUTE sp_help_revdatabase
-- This is the output generated by executing sp_help_revdatabase
CREATE Database DB5 on dv9 = 2 --  2 Mb = 1024 2Kb pages
go
ALTER  Database DB5 on dv9 = 1
go
ALTER  Database DB5 on dv8 = 1
go
Execute sp_logdevice DB5,dv8
go
ALTER  Database DB5 on dv8 = 1
go
Execute sp_logdevice DB5 ,dv8
go
-- - - - - -
CREATE Database DB66  on dv9 = 3
go
ALTER  Database DB66  on dv8 = 2
go
Execute sp_logdevice DB66 ,dv8
go
-- - - - - -
CREATE Database DB777 on dv7 = 2
go
ALTER  Database DB777 on dv6 = 2
go
Execute sp_logdevice DB777 ,dv6
go
ALTER  Database DB777 on dv7 = 1
  

The output this procedure generated differs for databases DB5 and DB66. However, both of these databases can be safely loaded with a dump from the other. However, DB777 cannot be involved with dump and load activity along with DB5 or DB66. The following SELECT statement example shows the lpage and segmap combinations for all of these databases after running sp_coalesce_fragments.

SELECT
         'DBName'=substring(db_name(ug.dbid),1,6),
        'FromLPage'=ug.lstart,
        'ToLPage'  =ug.lstart + ug.size - 1,
        ug.segmap
FROM
         master.dbo.sysusages ug
ORDER BY
         DBName,FromLPage
  
DBName FromLPage   ToLPage     segmap      
------ ----------- ----------- ----------- 
DB5              0        1023           3 
DB5           1024        1535           3 
DB5           1536        2047           4 
DB5           2048        2559           4 
DB66             0        1535           3 
DB66          1536        2559           4 
DB777            0        1023           3 
DB777         1024        2047           4 
DB777         2048        2559           3 
  

Note lpage 1029 corresponds to a segmap of 3 for databases DB5 and DB66, but to a segmap of 4 for DB777. Because the lpage and segmap combinations are in agreement between DB5 and DB66, dump and load activity between the two databases is safe. This dump and load activity is safe even though DB5 and DB66 have different numbers of fragments after sp_coalesce_fragments has been run.

This procedure does not update or change anything on the server.

Permission

Execute permissions default to permissions of the public group.