Differential backup and differential_base_lsn

In this blog I would like to explain where the Differential backup metadata(LSN details) are stored with an example, Recently I took a Full backup of a very large database DB1 on a SQL instance SQLServerA and restored it to different server instance called SQLServerB,

--Exec on SQLServerA

BACKUP DATABASE DB1 TO DISK = '<backup_path_1>' WITH STATS = 1

--Exec on SQL ServerB

RESTORE DATABASE DB2 FROM DISK = '<backup_path_1>' WITH RECOVERY,STATS = 1

Since we had a differenential backup scheduled on the SQLServerB I was not sure if I need to take a full backup of the database DB2 before the differential backup kicks, but to my surprise the differential backup completed successfully for DB2 database even though there is no full backup of the database in SQLServerB instance.
 

To check the consistency i used the full backup from SQLServerA and differential backup from SQLServerB to see i can restore the database

--Exec on SQLServerB

BACKUP DATABASE DB2 TO DISK = '<diff_backup_path>' WITH DIFFERENTIAL,STATS = 1

--Exec on SQL ServerB using the full backup of SQLServerA.DB1

RESTORE DATABASE DB3 FROM DISK = '<full_backup_path_1>' WITH NORECOVERY,STATS = 1

--Exec on SQL ServerB using the differential backup of SQLServerB.DB2

RESTORE DATABASE DB3 FROM DISK = '<diff_backup_path>' WITH RECOVERY,STATS = 1

I was able to recover DB3 from full backup of DB1 and Diff backup of DB2, which made me think where does the restore task looks for the LSN detail on a differential restore, I wanted to take Log file out of the equation so i recreated the log file for the DB2 database.

 

EXEC sp_detach_db 'DB2', 'true';

CREATE DATABASE DB2

ON (FILENAME = '<db_path>')

FOR ATTACH_REBUILD_LOG

 

Now after the new log was built I tried to take a differential backup again,

 

--Exec on SQLServerB

BACKUP DATABASE DB2 TO DISK = '<diff_backup_path>' WITH DIFFERENTIAL,STATS = 1

 

And it WORKED!!!!

 

After referring MSDN site I found the 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. The data is stored in system tables sys.master_files for read-only database and sys.database_files for Read/Write database using the following columns,

differential_base_lsn	numeric(25,0)	Base for differential backups. Data extents changed after this LSN will be included in a differential backup.
differential_base_guid	uniqueidentifier	Unique identifier of the base backup on which a differential backup will be based.
differential_base_time	datetime	Time corresponding to differential_base_lsn.

 

So when I did the first restore of DB2 these columns were updated on master database and it was referencing these columns after that. So you can use differential backup on different servers without any reference any other metadata.

Hope this exercise helps,More details on differential backup can be found @ http://msdn.microsoft.com/en-us/library/ms175526.aspx