June 27, 2011 at 6:57 pm
Executing the query "BACKUP LOG [Database] TO DISK = N'C:\\Backup\\Databas..." failed with the following error: "Database 'DATABASE' cannot be opened. It is in the middle of a restore.
The above is the error message at time 1050hrs last friday. So i think that could be the reason, the transaction log wasnt backup during that point of time. so how do i prevent it from happening in the future ? thanks
June 27, 2011 at 7:19 pm
Erode P. Satheesh (6/27/2011)
T-sql, maintenance plan and even if you are using third party tool the information will be stored in these tables.
Hi Erode, i have run the script and it generate a list of the backups done. (including the log shipping).
But is confirm that there is a loss of file at 1050hrs on 24th Jun, because the transaction logs only have 1040hrs and follow by 1100hrs.
June 27, 2011 at 7:55 pm
Does this affect the log shipping as well? seems like my log shipping also got some problems. When i run the Restore job for the log shipping, it got this error: *** Error: The file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLINSTANCE2\MSSQL\DATA\Database_20110627010019.trn' is too recent to apply to the secondary database 'Database'.(Microsoft.SqlServer.Management.LogShipping) ***<nl/>2011-06-28 09:45:18.51*** Error: The log in this backup set begins at LSN 22000000027100001<c/> which is too recent to apply to the database. An earlier log backup that includes LSN 22000000024800001 can be restored.<nl/>RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***<nl/>2011-06-28 09:45:18.54Searching for an older log backup file. Secondary Database: 'Database',00:00:18,0,0,,,,0
So i have to re-setup the logshipping?
June 28, 2011 at 12:52 am
Have you scheduled a transaction log backup through maintenace plan on a database which is already a part of log shipping? If so, you should not be doing this way. Your log shipping is already doing a transaction log backup and it will be storing the backup log files on your primary sever or at any network path. Scheduling an another transaction log backup may cause the conflicts.
Probably a log backup has occurred between 10:40 and 11:00 by the log shipping and stored at the path you had specified in the log shipping.
---------------------------------------------------
Thanks,
Satheesh.
June 28, 2011 at 12:58 am
Erode P. Satheesh (6/28/2011)
Have you scheduled a transaction log backup through maintenace plan on a database which is already a part of log shipping? If so, you should not be doing this way. Your log shipping is already doing a transaction log backup and it will be storing the backup log files on your primary sever or at any network path. Scheduling an another transaction log backup may cause the conflicts.Probably a log backup has occurred between 10:40 and 11:00 by the log shipping and stored at the path you had specified in the log shipping.
The transaction log backup that occured during 10:50 was lost and i have checked and is due to the restoration and hence causing the problem. I have gone through the job history for that transaction log and it had this error: Executing the query "BACKUP LOG [Database] TO DISK = N'C:\\Backup\\Databas..." failed with the following error: "Database 'DATABASE' cannot be opened. It is in the middle of a restore. . I was wondering how can i prevent this type of problems?
As for the log shipping, it is done on the same database where i copy and restore the database on another instance. So do you mean that if i had done a log shipping for that database, i should not be doing a backup for that database? thanks
June 28, 2011 at 1:09 am
Exactly. You should not be doing transactional log backup on the database which is part of logshipping. In short, logshipping will perform a transactional log backup on your primary database - ship to the secondary server - restore on the secondary database. The sequence of your transactional log backup is based on the LSN value which will be updated on each log backup. Now you are comprimising the sequence of log shipping by taking a log backup in middle and vice versa.
---------------------------------------------------
Thanks,
Satheesh.
June 28, 2011 at 2:54 am
Illegal_Operation (6/27/2011)
Executing the query "BACKUP LOG [Database] TO DISK = N'C:\\Backup\\Databas..." failed with the following error: "Database 'DATABASE' cannot be opened. It is in the middle of a restore.The above is the error message at time 1050hrs last friday. So i think that could be the reason, the transaction log wasnt backup during that point of time. so how do i prevent it from happening in the future ? thanks
It's not that the log backup ran that's causing that error, it's that the DB was restored.
Once a restore happens, the log chain is broken. Log backups after that are based on the full backup that you restored, not previous full/log backups. If you want to restore past that point, you need the full that you restored that day and then the log backups (or a full backup created after that point)
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
June 28, 2011 at 3:16 am
GilaMonster (6/28/2011)
Illegal_Operation (6/27/2011)
Executing the query "BACKUP LOG [Database] TO DISK = N'C:\\Backup\\Databas..." failed with the following error: "Database 'DATABASE' cannot be opened. It is in the middle of a restore.The above is the error message at time 1050hrs last friday. So i think that could be the reason, the transaction log wasnt backup during that point of time. so how do i prevent it from happening in the future ? thanks
It's not that the log backup ran that's causing that error, it's that the DB was restored.
Once a restore happens, the log chain is broken. Log backups after that are based on the full backup that you restored, not previous full/log backups. If you want to restore past that point, you need the full that you restored that day and then the log backups (or a full backup created after that point)
Yup, is the db restore that is causing the problem and hence the log chain is broken.
So what u meant was in future, when there is a restoration done, we will have to take a full backup after that point and then follow by the log backups...
So as in example, as i did a restoration on 10:50hrs last friday, 24 June 2011, i will have to manually do a full backup on 11:00hrs, 24 June 2011, then everything will be back to normal and hence i will be able to recover any point after that period. is that correct? thanks
June 28, 2011 at 4:14 am
That'll work.
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
June 28, 2011 at 8:23 pm
GilaMonster (6/28/2011)
That'll work.
thanks for the advice... noted.
June 28, 2011 at 8:37 pm
GilaMonster (6/28/2011)
That'll work.
ok, btw how abt the log shipping? if the restore was done during last friday 1050hrs, it will cause broken link to the transaction logs for the logshipping and hence nw my logshipping also had problem restoring to the secondary database, what should i do in this case?
So as advice by Erode, we should not be doing backup of that database if it is being configured as log shipping, am i right?
June 29, 2011 at 2:25 am
REinitialise log shipping.
Full or diff backups of a log shipped primary, yes. Log log backups, no.
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
June 29, 2011 at 7:01 pm
GilaMonster (6/29/2011)
REinitialise log shipping.Full or diff backups of a log shipped primary, yes. Log log backups, no.
Hi GilaMonster, what do you mean by reinitialise log shipping? re-setup again?
As for "Full or diff backups of a log shipped primary, yes. Log log backups, no"? I do not quite get what you mean. thanks
June 30, 2011 at 2:42 am
any advice on the logshipping portion? thanks
June 30, 2011 at 6:17 am
Please go and do some reading on log shipping, log backups and restore sequences.
Reinitialise as in recreate the log shipping secondary the same way you did when you first set the log shipping up.
Re backups, you need to back the log shipping primary up. You can run full and diff backups. You just cannot have another job as well as the log shipping one that backs up the log.
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
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply