You need to consider several things when loading a database from a dump. First, the target database needs to be at least as large as the database that was dumped. Attempting to load a dump into a database that is smaller than the database that the dump came from will result in a SQL Server 3105 error, stating:
Data on dump will not fit into current database. Need xx Mbyte database.In this message, xx is the size of the original database. You can, however, load a dump into a target database that is larger than the original database.
Also consider whether the database will be loaded on the same server from which it was dumped, or if there is a potential for it to be loaded onto a different server. If the database may be dumped and loaded between servers, the code page and sort order between the servers must match. You cannot load a database on a SQL Server that is running with a code page or sort order different from the SQL Server where the dump was from.
SQL Server 6.0 provides a tool, SQL Transfer Manager, which uses a graphical interface to allow you to easily transfer some or all of the data from one server to another. This is useful when the servers are using different sort orders or code pages, or when they are using different hardware architectures (for example, to move data from a SQL Server running on an Intel®-based computer to SQL Server running on a RISC-based computer). If your environment has multiple SQL Servers, it is recommended that they all be configured with the same code page and sort orders.
If a database needs to be re-created and loaded from a previous dump, it is important that the database is re-created in exactly the same way that it was created. That is, the device usage, log space allocation, and segment definitions in the new database must be identical to how they were when the database was dumped. This information is kept in the sysusages table in the master database, so you should always dump the master database whenever a change to a database is made that records anything in master (such as an ALTER DATABASE or DISK INIT command). In addition, if you save the scripts you used to create the initial database, re-creating it will simply be a matter of rerunning those scripts.
If a dump is loaded into a database that has data, log, or segment mappings different from the original database, DBCC CHECKALLOC may report SQL Server error 2558, stating:
Extent not within segment: Object <object id>, indid <index id> includes extents on allocation page <page number> which is not in segment <segment number>.
This error can indicate that there are data pages allocated in portions of the database intended for log pages, or log pages allocated in portions intended for data. In the first case (data pages in log space), the effect is that less space is available for the transaction log to grow. The second case (log pages in data space) is less serious, because the data space being used by log pages will eventually be freed up when the log is truncated with the DUMP TRANSACTION statement.