Detach database breaks the DIFFERENTIAL backup chain when READ ONLY

Question: During a routine activity I detached a READ ONLY database , reattached the READ ONLY database. The standard backup procedure for this database was a FULL BACKUP on Friday and DIFFERENTIALS on other days.

If the database is READ ONLY , there is no requirement for a DIFFERENTIAL , but it’s part of a SQL Server Instance wide backup policy. Occassionally , the database is taken out of READ ONLY and data updated.

A few days after the database attach I needed to complete a database restore . I discovered I couldn’t restore back to the last DIFFERENTIAL  backup. The last FULL backup was taken BEFORE the database detach \ attach

The differential base lsn did not match the last full backup. Why is this occurring?

Answer:   According to MSDN-the microsoft developer network “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.”

Based on this information – the corrective action is to take the database out of READ ONLY mode, take a FULL BACKUP and then restart the DIFFERENTIAL backups.

As you progress through the test scenario – use the script on SQL
Server - Display sql backup history for a single database   to view the values.

I’m including a sequence of sql code which recreates the problem.

--create a database 
CREATE DATABASE [backup_test]

--make backup_test READ ONLY
ALTER DATABASE backup_test SET READ_ONLY 

--check sys.master_files
SELECT name,differential_base_lsn,differential_base_guid,differential_base_time
 FROM sys.master_files where name = 'backup_test'

--take FULL BACKUP 
BACKUP DATABASE [backup_test] to DISK='E:\BACKUP\backup_testRO.bak'

--check sys.master_files - you should now see some values 
SELECT name,differential_base_lsn,differential_base_guid,differential_base_time
 FROM sys.master_files where name = 'backup_test'

--detach the database
EXEC master.dbo.sp_detach_db @dbname = N'backup_test'
GO

--check sys.master_files - you should now see no values 
SELECT name,differential_base_lsn,differential_base_guid,differential_base_time
 FROM sys.master_files where name = 'backup_test'

--attach the database 
USE [master]
GO
CREATE DATABASE [backup_test] ON 
( FILENAME = N'E:\backup_test.mdf' ),
( FILENAME = N'F:\backup_test_log.ldf' )
 FOR ATTACH
GO

--take a DIFFERENTIAL BACKUP 
BACKUP DATABASE [backup_test] TO DISK = 'E:\BACKUP\backup_testDIFFRO.bak'  WITH DIFFERENTIAL 

Read More On SQL Server Backups and Restores

Database backup does not cause blocking - SQL Server DBA

SQL Server – Audit Backup\Restore Event with SQL Default trace ...