Sequence of the Log i.e. LSN and Restore?
essamughal
Posts: 35
Hi Petey;
I am again here to bug you.
Okay, I am performing some test case of sequence of the log to restore. I am working on Log Shipping implemented by Scripts generated by SQL Backup Version 4. Here is my test cases:
Case # 1:
At Primary Server DB:
1- Take Log Backup i.e. Log1
2- Did some changes in the data, let say add a record in a table
3- take another log backup i.e. Log2
At Seconday Server DB:
1- Skip the the Log1 , just for testing
2- Try to Restore the Log2
Result Case # 1:
It did not restore Log2 and gave me an Error and sent me Email reporting Error:
**************************************
Server: Msg 3013
RESTORE LOG is terminating abnormally.
Server: Msg 4305
The log in this backup set begins at LSN 24009000001520300001, which is too late to apply to the database. An earlier log
backup that includes LSN 24009000001519800001 can be restored.
**************************************
Which is very good I am expecting that, SQL Backup Version 3 was not doing that, version 3 was not reporting me Error in email and reporting me SUCCESS email and moving the file to Processed folder. It is good now that is fixed in version 4.
Case # 2:
At Primary Server DB:
1- Take Log Backup i.e. Log1
2- no changes in the data, means the data is same as it was in previous log
3- take another log backup i.e. Log2
At Seconday Server DB:
1- Skip the the Log1 again, just for testing
2- Try to Restore the Log2, it restored that log2
3- try to restore the Log1 and it restored that log1 as well
Result Case # 2:
So, it restored the Log2 before the Log1 when the data was not changed in the database.
Confusion and Question:
Now, I am wondering the SQL Server Transaction Log behaves like that and it gives the same LSN when the data is not changed in the database, which is confusing me.
Or, Red-Gate SQL Backup is very intelligent and it is looking the log if it there is no difference then it is processing and restoring the log regardless the sequence no. :?:
Or, it is a bug in SQL Backup. :?:
looking forward for your response.
Thanks
I am again here to bug you.
Okay, I am performing some test case of sequence of the log to restore. I am working on Log Shipping implemented by Scripts generated by SQL Backup Version 4. Here is my test cases:
Case # 1:
At Primary Server DB:
1- Take Log Backup i.e. Log1
2- Did some changes in the data, let say add a record in a table
3- take another log backup i.e. Log2
At Seconday Server DB:
1- Skip the the Log1 , just for testing
2- Try to Restore the Log2
Result Case # 1:
It did not restore Log2 and gave me an Error and sent me Email reporting Error:
**************************************
Server: Msg 3013
RESTORE LOG is terminating abnormally.
Server: Msg 4305
The log in this backup set begins at LSN 24009000001520300001, which is too late to apply to the database. An earlier log
backup that includes LSN 24009000001519800001 can be restored.
**************************************
Which is very good I am expecting that, SQL Backup Version 3 was not doing that, version 3 was not reporting me Error in email and reporting me SUCCESS email and moving the file to Processed folder. It is good now that is fixed in version 4.
Case # 2:
At Primary Server DB:
1- Take Log Backup i.e. Log1
2- no changes in the data, means the data is same as it was in previous log
3- take another log backup i.e. Log2
At Seconday Server DB:
1- Skip the the Log1 again, just for testing
2- Try to Restore the Log2, it restored that log2
3- try to restore the Log1 and it restored that log1 as well
Result Case # 2:
So, it restored the Log2 before the Log1 when the data was not changed in the database.
Confusion and Question:
Now, I am wondering the SQL Server Transaction Log behaves like that and it gives the same LSN when the data is not changed in the database, which is confusing me.
Or, Red-Gate SQL Backup is very intelligent and it is looking the log if it there is no difference then it is processing and restoring the log regardless the sequence no. :?:
Or, it is a bug in SQL Backup. :?:
looking forward for your response.
Thanks
Comments
Try the same test, but using native SQL Server backups. The results will be the same.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Thanks for nice suggestion, I did that test on SQL Server Native backup and it does the same thing. The reason is that when there is no change then SQL Server does not give a new LSN to the Log and the LSN of Log1 and Log2 is same so that there is no error if you restore log2 before log1 if data is not changed during these two. When there is data change then it gives the new LSN to the logs and thru error while restoring out of sequence logs.
In SQL Server Management Studio 2005, it shows you all the LSN in the log which is kind a cool and I was curious to see the LSN of each log as well. So, I found it there.
Thanks.
[/img]