October 20, 2008 at 2:30 am
Good Morning,
Many Thanks for your help in advance for this. I set up a maintenance plan last week and because its a active database used by about 20 people tested the restore over the weekend to see if it would work. There was no problem recovering the database from a full database backup however I was unable to restore any of the transaction log backups after the full backup. I had backed up the tail as well before trying to restore.
The error I got it below:
Restore failed for Server 'ESRISDE\DEVELOPMENT'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 1737000006863100001, which is too recent to apply to the database. An earlier log backup that includes LSN 1737000006862600001 can be restored. (Microsoft.SqlServer.Smo)
The only other backup system that is running is the Veritas Backup which happens each night.
I have tried in vain to search for any clues as to the problem online. Anything you can suggest would be great.
Cheers,
Oliver
October 20, 2008 at 2:48 am
You're missing one or more log backups. After restoring the full backup, you need to restore all of the log backups, in order, that were taken since the full backup.
When's the full bckup made and when do the tran log backups run?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2008 at 2:53 am
Gail,
I attach an image of what I was trying to do, I dont want to recover all the transaction logs because it may well be the error was made 4 hours ago an in that time a further transaction log backup has been made.
Thanks for your help,
Oliver
October 20, 2008 at 3:12 am
That looks like it should work. All the logs are there and in sequence, and the first one covers the time of the full backup. Is that giving the error you posted above?
The error you listed references an LSN far earlier than any listed in that dialog. The earliest one listed there is 1737000006865700001 and the two referenced in the error you posted were 1737000006863100001 and 1737000006862600001. Those are far earlier than the full backup you're restoring from.
Can you try the restore in T-SQL code please? Restore the full with norecovery and then restore the logs one by one also with norecovery?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2008 at 3:15 am
oliver.morris (10/20/2008)
Good Morning,Many Thanks for your help in advance for this. I set up a maintenance plan last week and because its a active database used by about 20 people tested the restore over the weekend to see if it would work. There was no problem recovering the database from a full database backup however I was unable to restore any of the transaction log backups after the full backup. I had backed up the tail as well before trying to restore.
The error I got it below:
Restore failed for Server 'ESRISDE\DEVELOPMENT'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 1737000006863100001, which is too recent to apply to the database. An earlier log backup that includes LSN 1737000006862600001 can be restored. (Microsoft.SqlServer.Smo)
The only other backup system that is running is the Veritas Backup which happens each night.
I have tried in vain to search for any clues as to the problem online. Anything you can suggest would be great.
Cheers,
Oliver
According to the error message you restored a full backup and then you wanted to restore a log, but there was another log backup that was done between the full and log backup that you are using. You need to make sure to plan your backup strategy. Most of the time here is no reason to have 2 different systems that backup the databases. The only times that I used the maintenance wizard for backups and also an external system to backup all the databases was when I used log shipping. At that time the external system was doing only the full backup and differential backups and I used the maintenance wizard for the log shipping (which created the log backups). From your post it seems that you have 2 different backup plans that are “blind” to each other, interfere with each other and complicate things.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 20, 2008 at 3:16 am
The error I reported was from when I tried to restore yesterday which is why the numbers are not the same as the ones above. I will try to run a restore tonight when no one is using the database. It is strange though this error isnt it.
Many thanks for your help,
Oliver
October 20, 2008 at 3:27 am
Adi,
Thanks for your reply. Over the weekend I tried to restore the database, I wanted to restore it to say a couple of hours earlier so I clicked the full backup and a few of the proceeding transaction log backups. I tried to run this restore after backing up the tail of the log. When I did this I received the error. The server is backed up every night by Veritas Backupexec but as I understand it this should not interfere at all with SQL server and its backups? I am not trying to over complicate things – but do you think its worth testing it after disabling veritas. I only have one maintenance plan backing up in SQL Server.
Many Thanks,
Oliver
October 20, 2008 at 3:38 am
oliver.morris (10/20/2008)
The error I reported was from when I tried to restore yesterday which is why the numbers are not the same as the ones above. I will try to run a restore tonight when no one is using the database.
It should restore fine. The log records match up.
It is strange though this error isnt it.
Not really. It's just that a log backup was missing. Possible one was taken manually and didn't appear in the backup set.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2008 at 3:42 am
oliver.morris (10/20/2008)
Adi,Thanks for your reply. Over the weekend I tried to restore the database, I wanted to restore it to say a couple of hours earlier so I clicked the full backup and a few of the proceeding transaction log backups.
Were there any gaps? Log backups that you didn't check in between ones you did check?
The server is backed up every night by Veritas Backupexec but as I understand it this should not interfere at all with SQL server and its backups?
Depends. Is veritas just backing up files or is it doing database backups (using backup database) as well? If it's just a file-system backup then it won't affect anything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2008 at 3:54 am
Gail,
Thanks again.
No I made sure that I did not miss any logs since the full backup. I understand the the veritas backup does do a database backup as well as a file backup. We paid extra for a license to do this with veritas. I shall ask them is its a problem.
Cheers,
Oliver
October 20, 2008 at 4:07 am
oliver.morris (10/20/2008)
Adi,Thanks for your reply. Over the weekend I tried to restore the database, I wanted to restore it to say a couple of hours earlier so I clicked the full backup and a few of the proceeding transaction log backups. I tried to run this restore after backing up the tail of the log. When I did this I received the error. The server is backed up every night by Veritas Backupexec but as I understand it this should not interfere at all with SQL server and its backups? I am not trying to over complicate things – but do you think its worth testing it after disabling veritas. I only have one maintenance plan backing up in SQL Server.
Many Thanks,
Oliver
You just need to make sure that you know if the Veritas is also backing up the databases. If it does, you need to know when it does it and the type of the backup that it does (log, differential or full). After you’ll know how Veritas works, you’ll be able to decide how to plan the additional backups (and if you need them at all) .
From your attachment it looks as if there was another full backup that was done between the full backup that you did and the first log backup that was taken. You can try and check it by querying backupset table in MSDB database.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 20, 2008 at 4:15 am
oliver.morris (10/20/2008)
Gail,Thanks again.
No I made sure that I did not miss any logs since the full backup. I understand the the veritas backup does do a database backup as well as a file backup. We paid extra for a license to do this with veritas. I shall ask them is its a problem.
Cheers,
Oliver
With this information and your attachment that describes the files that you can use for the restore, my bet is that the Veritas did a full backup after your full backup, but before your first transaction log.
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 20, 2008 at 4:19 am
Adi Cohn (10/20/2008)
From your attachment it looks as if there was another full backup that was done between the full backup that you did and the first log backup that was taken. You can try and check it by querying backupset table in MSDB database.
My guess is that it was a log backup. Fulls don't truncate the transaction log and as such shouldn't affect the log chain. If another log backup was taken though, the log chain would have a break in it and it would not be possible to restore past that point without finding the 'missing' log backup.
I understand the the veritas backup does do a database backup as well as a file backup. We paid extra for a license to do this with veritas. I shall ask them is its a problem.
If you paid extra to do database backups through Veritas, why are you backing the databases up manually. It's a waste of time and space, not to mention the problems with having backups taken by 2 tools stored in 2 places.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2008 at 4:21 am
Adi,
I think this is the case. I have checked the backups and it appears that its doing a full backup. I have now selected Veritas to simply do a Database Copy that (so veritas say) should not interfere with the transaction log records.
Your help on this has been really appreciated. I know that you think 2 backups is not necessary but I would prefer to have a copy both on tape and on the server.
Cheers,
Oliver
October 20, 2008 at 4:34 am
I know what you mean, it's kind of comforting to have two sets of backups. If that's what you want, you could take your native SQL backups, then have BEX copy the BAK files to tape. Then the tape backup can't interfere with log chains. It'll save you money on the extra BEX agent too.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply