Chapter 11: Developing a Backup and Recovery Plan

Side effects of offline pages

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.
To drop a table or index containing offline pages, you must use a transaction in the master database. Otherwise, the drop fails because it must 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
```

Recovery strategies using recovery fault isolation

View this book as PDF