Analyzes an existing database and creates a script that can be used to replicate the database structure on another server.
sp_help_revdatabase [DBNamePattern]
where
For more information about the LIKE operator, see Wildcard Characters in the Microsoft SQL Server Transact-SQL Reference.
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.
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.
Execute permissions default to permissions of the public group.