The following restrictions apply to databases with offline pages:
Transactions that need offline data, either directly or indirectly (for example, because of referential integrity constraints), fail and generate a message.
You cannot use dump database when any part of the database is offline.
A System Administrator can force the offline pages online using sp_forceonline_db with “sa_on” dump the database, and then use sp_forceonline_db with “sa_off” after the dump completes.
You cannot use dump transaction with no_log or dump transaction with truncate_only if any part of a database is offline.
A System Administrator can force the offline pages online using sp_forceonline_db with “sa_on”, dump the transaction log using with no_log, and then use sp_forceonline_db with “sa_off” after the dump completes.
If you want to drop a table or index containing offline pages, you must use a transaction in the master database. Otherwise, the drop will fail because it needs to delete entries for the suspect pages from master.dbo.sysattributes. The following example drops the object and deletes information about its offline pages from master.dbo.sysattributes.
To drop an index named authors_au_id_ind, which contains suspect pages, from the pubs2 database, drop the index inside a master database transaction as follows:
use master go sp_dboption pubs2, "ddl in tran", true go checkpoint pubs2 go begin transaction drop index authors.au_id_ind commit go use master go sp_dboption pubs2, "ddl in tran", false go checkpoint pubs2 go
