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

8 comments:

  1. I really like your blog and have one with similar information. If you have time check it out.please visit
    my home page home tech

    ReplyDelete
  2. My SQL database is is easy to manage all application.
    we can easily add new features in website from backend database.
    bahrain web development company

    ReplyDelete
  3. Well organized!! we can show deep drive demos, even standard edition of SQL data & focusing on how to move data b/w different disk arrays also keep system online. thanks for your great guide about SQL server.
    Web Development Dubai Company

    ReplyDelete
  4. Great Article it its really informative and innovative keep us posted with new updates. its was really valuable. thanks a lot.

    ReplyDelete
  5. Great article! Very informative and simple easy to understand. Keep posting it really help for those people that needs this. Thanks a lot!

    ReplyDelete

  6. I have not heard about before. Keep sharing more blog posts on this Community and get extra benefits with this ideas and knowledge. Thanks for this one.
    sparknotes
    sparknotes techstarusa
    technocompe sparknotes

    ReplyDelete

  7. thanks for sharing best topics, keep such update and share best ideas over to all and me so i very happy with this.
    social media today Market
    Best article sites

    ReplyDelete