log backups/restore

Question

Does the backups which happen through a maitenance plan always follow sequence . I mean the LSN numbers are always in a sequence?. I am trying to restore a DB to a point in time but I get an error about not matching LSN. I looked in to the logs and seems something is missing . We are doing full backups at 5:30PM everyday and log backups every 30mins

I need to get to a point in time restore which would need 09/11 5:50 full back up and log backups on the top of it. I restored full backup and left it in restoring state and now when I am doing the next log backup I get error "A more recent log backup that includes LSN 157591000002332600001". Now I have no log backup which has starting 23326 in its LSN. I checked the sql error log. there is no other log except one full backup which has 23326 in its LSN.

Here is what I have in the logs

Date  9/11/2012 5:30:01 PM

Log was backed up -first LSN: 157591:22389:1, last LSN: 157591:22391:1

Date  9/11/2012 5:31:55 PM

Database backed up - first LSN: 157591:23233:224, last LSN: 157591:23326:1

Date  9/11/2012 6:00:01 PM

Log was backed up- first LSN: 157591:22391:1, last LSN: 157591:23337:1

Date  9/11/2012 6:30:02 PM

Log was backed up-first LSN: 157591:23337:1, last LSN: 157591:23340:1

and goes on with different LSN's

---

@R

Answer 1

No, it doesn't always follow a sequence. The maintenance plan will only generate backups based on the schedule and the type of backup you define. If somebody runs an adhoc FULL and LOG backups in between the times when the maintenance plan runs, then, the series of backups that the maintenance plan has created is already broken as far as the backup chain is concerned. Restoring from backups require the correct backup files that have the log sequence chain intact. Your best bet would be to read the msdb database and look at the backuphistory tables to see the the series of backups tat you need to restore in sequence. 

SELECT database_name,Backup_type=   
     CASE  type             
    when 'D' then 'FULL'   
    when 'I' then 'DIFFERENTIAL'   
    when 'L' then 'LOG'   
    when 'F' then 'FILE/FILEGROUP'   
    when 'G' then 'DIFFERENTIAL FILE'   
    when 'P' then 'PARTIAL'    
    when 'Q' then 'DIFFERENTIAL PARTIAL'   
    END                
      , physical_device_name,backup_start_date            
              
  FROM msdb.dbo.backupset a inner join msdb.dbo.backupmediafamily b   
  on a.media_set_id = b.media_set_id   
where database_name='yourDatabaseNameHere' 
order by a.database_name,a.backup_start_date DESC   

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 2

Thank you Edwin. I already thought about some random backups and checked the logs but did not find anything unusual.  No one else should be backing up those databases besides  maintenance plan. But now I feel I am missing something.

I will excecute your above query and get back with results.

Thanks

---

@R

Answer 3

Hi Ramshna,

Please run the following T-SQL command:

USE msdb
GO
SELECT first_lsn,last_lsn, backup_finish_date, type, recovery_model FROM backupset
WHERE database_name='yourdatabasename'

The LSN 23326 is the last LSN of the database backup at 5:31:55 PM. It should also be the first LSN of of the log backup at 6:00:01PM.

The log chains may be broken. Generally, the SQL Server Database Engine prevents gaps in the sequence of log backups, keeping the log chain intact. However, a database administrator can break the log chain by changing the recovery model to simple and back to full or bulk-logged.

You cannot roll forward across recovery model changes that involve the simple recovery model, because the log chain is broken. After changing to the full or bulk-logged recovery model, you should take a new differential base or set of differential bases. Alternatively, you can use differential backups to bridge a gap. Have you changed the recovery model during the backup?

Log Sequence Numbers and Restore Planning: http://msdn.microsoft.com/en-us/library/ms190729(v=sql.105).aspx.

TechNet Subscriber Support
If you are TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 4

Maggie,

I ran the query provided by you and it got me some 736 rows, out which around 50 were of 2012 backups. It  started from 2010. So how much backup information is stored in the backup set and can I get the backup information of a specific date. It would be of tremendous help if I will be able to list all the LSN's of a specific date.

By the way ,we did not change anything on the database and I am positive that no other backups were performed besides maintenance plan. Unless something happened with out my knowledge. And since the creation of this server no body should have messed with system databases.

Thanks so much for your time

---

@R

Answer 5

Maggie,

I ran the query provided by you and it got me some 736 rows, out which around 50 were of 2012 backups. It  started from 2010. So how much backup information is stored in the backup set and can I get the backup information of a specific date. It would be of tremendous help if I will be able to list all the LSN's of a specific date.

Hi Ramshna,

You could run follow T-SQL command to get the backup information and list all the LSNs of a specific date. It could be a great help.

USE msdb
GO
CREATE VIEW backuptable
AS
(
SELECT first_lsn,last_lsn,backup_finish_date = convert(varchar(10),backup_finish_date,120),
type, recovery_model 
FROM backupset
)
SELECT first_lsn,last_lsn,backup_finish_date, type, recovery_model 
FROM backuptable
WHERE backup_finish_date='2012-05-31'

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 6

That was so helpfull Maggie,

so I created a view and did a select statement for all the backups happend on that day. As we do log backups every half hr there were lot of backups. I noticed that one full backup has last_lsn :157591000002332600001  and from the logs I saw that this is the DB I want to restore. So I wanted a next log backup which has 23326 in  first_lsn. I used the below query but it returned no value/row. Can I search in someother way. I mean if the full backup has the last LSN as 157591000002332600001  then what should I look for in the next log back up.

Thank you for your time.

SELECT first_lsn,last_lsn,backup_finish_date, type, recovery_model 
FROM backuptable
WHERE backup_finish_date='2012-09-11' 
and 
first_lsn like '%23326%
		

---

@R

Answer 7

Below is the image from the output of the query to list all backups. I changed the Db names to A,B,C for security reasons. I am trying to restore backup A. The next log backup which happened at 18:00:01 has a different first LSN

---

@R

Answer 8

The Reason why you dont have a log backup of '23326' is due to the fact that during the FULL Backup the logbackup also happens as the last piece.

Which means that.....lets say if my FULL backup starts at 5:30PM and takes for 20min to complete..so during this 20min there may be changes happening to DB...so inorder to capture them in FULL backup ..SQL Server also takes a logbackup at end of 20min to capture the changes happened during the time the backup was running and ties it to FULLBackup.

---

Thanks
-SQLHarry

http://www.sqlharry.blogspot.com/

Answer 9

so what is the next log backup  I need to restore after restoring full backup. For the backup A in the above pic if I am trying to restore 18:00:01 log after full backup restore I am getting an error about the LSN sequence

---

@R

Answer 10

can you try to get both backup start time and finish time on the DB you are trying to restore.

USE msdb
GO
SELECT first_lsn,last_lsn, backup_start_date, backup_finish_date, type, recovery_model FROM backupset
WHERE database_name='DBNAME'

Thanks

---

Thanks -SQLHarry http://www.sqlharry.blogspot.com/