Thursday 4 October 2012

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

No comments:

Post a Comment