When SQL Server loads a database, it rolls back any uncommitted transactions that were active at the moment the dump began. When the load is complete, the database is in the same state it was in when the DUMP statement was executed, minus any transactions that were active at that point.
Once the data from the dump has been reloaded, SQL Server reinitializes any remaining unused pages. For example, if a 100-MB database contains only 5 MB of data, all 100 MB of space is still rewritten. A database load command takes at least as long as the CREATE DATABASE statement that created the database.
Loading a database locks it so that it cannot be modified while recovery is in progress. Users can, however, access and modify other databases on SQL Server during this time.
If a failure occurs while a database is being loaded, SQL Server notifies the SA but does not recover the partially loaded database. The database load must be restarted.
The destination database must have at least as much storage space as was allocated to the dumped database. The actual amount of data in the dumped database is irrelevant. To get information about allocated storage space, use SQL Enterprise Manager or the DBCC CHECKALLOC statement.
After a media failure, restart SQL Server in the usual way. If SQL Server cannot access (USE) a database, it marks the database as suspect, locks it, and displays a warning message. A damaged database must be dropped, which can be done using SQL Enterprise Manager, the DROP DATABASE statement, DBCC DBREPAIR (db_name, DROPDB), or sp_dbremove.
After the database is dropped, it must be re-created. After it is re-created, the SA or database owner can load the most recent database dump (plus any transaction log dumps) using SQL Enterprise Manager or the LOAD DATABASE statement. (The master database is restored using a special procedure. For information, see Restoring the master Database, later in this chapter.)
To learn how to load databases, see Restoring a Database or Applying a Transaction Log, earlier in this chapter.