Can we take full backup of read-only database

_{Wednesday, July 12, 2017 1:19 PM}

Good Morning Experts,

An expert in a blog said that full backups reset the diff bitmap on the database, which is clearly not supported on a read-only database. But, in this link https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx, it says we can take full backup of read-only database . I am confused. Could you please clarify.

Kiran

_{Thursday, July 13, 2017 8:31 AM ✅Answered}

As mentioned in the article that Teige linked to, a full backup maintains the diff information in the master database for a read-only database. But doing so in an Always On scenario would require SQL Server to go to the master database on the Primary (and possibly all secondaries) to reflect the same info on these, which clearly isn't possible/desirable.

Tibor Karaszi, SQL Server MVP (Web Blog)

_{Wednesday, July 12, 2017 1:41 PM}

It is possible to backup a read only database. Apparently there are DCM pages in the database which tracks what has possibly changed in other file groups since the last full was done. This is to figure out what should go in differential backups. There is an explanation here.

http://sqlity.net/en/2387/dcm/

So, most of the primary file is read only, but there are some system tables/pages which can be written to.

_{Wednesday, July 12, 2017 2:10 PM}

Thanks for replying Hilary. So, you are saying we can take full backup of a read-only database?

Kiran

_{Thursday, July 13, 2017 2:39 AM}

Hi juniorkiran,

Yes, we can take full backup of a read-only database. The process of differential backups of read-only databases is a little different from the normal database. This problem has explained in the document: https://technet.microsoft.com/en-us/library/ms190374(v=sql.105).aspx, please refer to the Differential Backups of Read-Only Databases part of it.

Best Regards,

Teige

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Thursday, July 13, 2017 4:20 AM}

Thanks for replying Teige. So, what is the difference between read-only database and readable secondary replica(AlwaysOn AG).

Kiran

_{Thursday, July 13, 2017 8:33 AM}

Thanks for replying Teige. So, what is the difference between read-only database and readable secondary replica(AlwaysOn AG).

The latter is 100% readonly as it is a true copy of the primary and cannot be permitted to deviate from the primary.

_{Thursday, July 13, 2017 8:41 AM | 1 vote}

Hi juniorkiran,

First, readable secondary databases are not set to read-only. Read-only database cannot be written into while readable database can accept changes on the corresponding primary database. Readable replica is just a read-only connection.

The difference on back is that readable secondary database only support copy-only full backups of databases, files, or filegroups and regular log backups.

Best Regards,

Teige

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.