Backing Up Read-Only Databases

This topic presents considerations for backing up databases that are read-only or were read-only at the time of the last backup.

For a read-only database, the primary file cannot be updated during a backup. The best practice for a read-only database is to take a full backup. However, if a database alternates between read-only and read/write, it may make sense to back up the database when it is read/write. Then, as long as the database remains read/write, if the scope of the changes are small, you can take differentials backups.

Note

You cannot change the IsReadOnly property of a filegroup during a backup. Trying to do this produces an error and fails.

Partial Backups After Changing a Database to Read/Write Permission

A partial backup of a read-only database contains only the primary filegroup. If the database is later changed to read/write, there might now be read/write secondary filegroups that are not in the partial backup. If this is the case, if you try to take a differential partial backup, the backup fails. Before you can take a differential partial backup of the database, you must take another partial backup. The new partial backup contains every read/write secondary filegroup and can serve as the base for differential partial backups.

Differential Backups of Read-Only Databases

For read-only databases, full backups used alone are easier to manage than when they are used with differential backups. When a database is read-only, backup and other operations cannot change the metadata that is contained in the file. Therefore, metadata that is required by a differential backup, such as the log sequence number at which the differential backup begins (the differential base LSN) is stored in the master database. If the differential base is taken when the database is read-only, the differential bitmap indicates more changes than have actually occurred since the base backup. The extra data is read by backup, but is not written to the backup, because the differential_base_lsn stored in the backupset system table is used to determine whether the data has actually changed since the base.

When a read-only database is rebuilt, restored, or detached and attached, the differential-base information is lost. This occurs because the master database is not synchronized with the user database. The SQL Server Database Engine cannot detect or prevent this problem. Any later differential backups are not based on the most recent full backup and could provide unexpected results. To establish a new differential base, we recommend that you create a full database backup.

Best Practices for Using Differential Backups with a Read-Only Database

After you create a full database backup of a read-only database, if you intend to create a subsequent differential backup, back up the master database.

If the master database is lost, restore it before you restore any differential backup of a user database.

If you detach and attach a read-only database for which you plan to later use differential backups, as soon as it is practical, take a full database backup of both the read-only database and of the master database.

See Also

Concepts

Differential Database Backups
Differential Partial Backups
Base of a Differential Backup

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Moved the "Changing a Database to Read-Only After Bulk-logged Transactions" section to Backup Under the Bulk-Logged Recovery Model.