Restore - too recent to apply to the database?

Question

Restore - too recent to apply to the database?

Viewing 15 posts - 16 through 30 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

GilaMonster (10/20/2008)
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.

You are correct. A full backup doesn’t truncate the log. My mistake.

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/

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 2

OK I will try that instead. Is there anything to be said for using both to backup because they are using different services and if one service went down you could rely on the other to get a full backup.

Many Thanks,

Oliver

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

As suggested, look at your backup history to see what actually ran and when: For instance, you can run this:

[font="Courier New"]SELECT sysdb.name, bkup.description, bkup.backup_finish_date,

case

when type='D' then '** FULL **'

when type='I' then 'DIFFERENTIAL'

when type='L' then 'LOG'

end as Backup_Type,

(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',

ceiling(bkup.backup_size /1048576) as 'Size Meg' ,

cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',

server_name, sysdb.crdate

,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'

,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)

as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn

FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name

where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days

AND sysdb.name = 'MY_DB_NAME'

ORDER BY sysdb.name, bkup.backup_finish_date desc[/font]

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 4

Hi,

May thanks for this.

My server name is esrisde\development, how can I enter this in your sql without bring up an error.

Many Thanks as always,

Oliver

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Where do you want to put the server name?

Run the query on the server that the backups were made on and replace the placeholder

AND sysdb.name = 'MY_DB_NAME'

with your actual DB name

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 6

That worked thanks.

So the report gives me this for the past few days - but no backup ran last night. So it does appear that there vertias is backing it up as predicted. I think we have found the solution now. You are all life savers 🙂

TeamNULL40:07.0LOG

TeamNULL40:06.0LOG

TeamNULL40:07.0LOG

TeamNULL40:01.0LOG

TeamNULL40:02.0LOG

TeamNULL00:58.0** FULL **

TeamNULL40:02.0LOG

TeamNULL40:03.0LOG

TeamNULL27:49.0LOG

TeamNULL23:02.0LOG

TeamNULL40:06.0LOG

TeamNULL40:02.0LOG

TeamNULL40:01.0LOG

TeamNULL40:02.0LOG

TeamNULL01:04.0** FULL **

TeamNULL40:06.0LOG

TeamNULL40:08.0LOG

TeamNULL40:09.0LOG

TeamNULL40:08.0LOG

TeamNULL40:01.0LOG

TeamNULL40:02.0LOG

TeamNULL40:05.0LOG

TeamNULL13:56.0** FULL **

TeamNULL40:02.0LOG

TeamNULL40:03.0LOG

TeamNULL01:40.0** FULL **

TeamNULL40:08.0LOG

TeamNULL40:02.0LOG

TeamNULL40:06.0LOG

TeamNULL40:07.0LOG

TeamNULL40:08.0LOG

TeamNULL04:39.0** FULL **

TeamNULL40:09.0LOG

TeamNULL40:05.0LOG

TeamNULL40:03.0LOG

TeamNULL40:06.0LOG

TeamNULL01:38.0** FULL **

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

I am facing a similar issue. I usually do my own custom logshipping but with this particular project (attempting to logship from '05 to '08 with the '08 DB's in NORECOVERY mode, I have opted for using the GUI in Management studio.

** Also, please forgive me if I'm not posting this in the correct place **

I've set the logshipping up 3 times now with the same result.

Message

2011-03-02 16:15:01.26*** Error: The file 'F:\SQL\LOGSHIPPING\DBNAME\DBNAME_20110302204503.trn' is too recent to apply to the secondary database DBNAME.(Microsoft.SqlServer.Management.LogShipping) ***

2011-03-02 16:15:01.26*** Error: The log in this backup set begins at LSN 320722000003620400001, which is too recent to apply to the database. An earlier log backup that includes LSN 320722000003498800001 can be restored.

RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

2011-03-02 16:15:01.26Searching for an older log backup file. Secondary Database: DBNAME

Message

2011-03-02 16:15:01.37Skipped log backup file. Secondary DB: 'COREISSUE', File: 'F:\SQL\LOGSHIPPING\DBNAME\DBNAME_20110302203003.trn'

2011-03-02 16:15:01.37*** Error: Could not find a log backup file that could be applied to secondary database DBNAME.(Microsoft.SqlServer.Management.LogShipping) ***

2011-03-02 16:15:01.37Deleting old log backup files. Primary Database: DBNAME

2011-03-02 16:15:01.37The restore operation completed with errors. Secondary ID: '0bc392ee-079d-4545-ab1e-8fac6eaf5930'

2011-03-02 16:15:01.38----- END OF TRANSACTION LOG RESTORE -----

Why is this happening????

Primary (no errors)

- I've configured tlog backups to a \etwork drive and verify that the trn files are writing there

- They do so every 15 minutes

Secondary (failing on restores)

- Configured destination folder properly (files get copied there)

- Restore tlogs in NORECOVERY mode, with a daly of 5 mins

This tends to run fine for the first hour then goes out to lunch. It has happened so far for 3/4 db's i set up this way (exactly the same way)

Does the fact that I've got a transaction log maintenance plan running tlog backups every 15 mins affect the log shipping? perhaps messing up the LSN's?

If so, how can i get around it?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Serge Mirault (3/2/2011)
Does the fact that I've got a transaction log maintenance plan running tlog backups every 15 mins affect the log shipping?

Yes.

Transaction log backups form a chain. You need all of them in sequence to restore. If you have log shipping you cannot have anything else backing up the log.

If you have both log shipping and a maint plan, each will have half of the log backups and both will be completely useless for restores of any form.

p.s. Please in future post new questions in a new thread

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mtassin SSC-Insane Points: 23099 More actions · Answer 9

oliver.morris (10/20/2008)
OK I will try that instead. Is there anything to be said for using both to backup because they are using different services and if one service went down you could rely on the other to get a full backup.
Many Thanks,
Oliver

If SQL (which is what's doing the backups) is down, Vertias can't back it up anyway.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Please note that this thread is originally over 3 years old

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

djguana SSC Enthusiast Points: 152 More actions · Answer 11

I got the same issue.

I have setup a full backup (part of a maintenance plan) on a daily basis at midnight + running a transaction log shipping every 15 min to my DR database and every day, right after the full backup the transaction log shipping no longer work... it used to work fine before but some day i got the exact same error and the restore are no longer working...

What should i do to fix this ? i need my daily backup and i need my DR database to be replicated realtime (at least every 15 minutes)...

thanks for your help

mtassin SSC-Insane Points: 23099 More actions · Answer 12

djguana (3/24/2011)
I got the same issue.
I have setup a full backup (part of a maintenance plan) on a daily basis at midnight + running a transaction log shipping every 15 min to my DR database and every day, right after the full backup the transaction log shipping no longer work... it used to work fine before but some day i got the exact same error and the restore are no longer working...
What should i do to fix this ? i need my daily backup and i need my DR database to be replicated realtime (at least every 15 minutes)...
thanks for your help

Check your maintenance plan and make sure that it's doing a full or differential backup. No step of that maint plan can make a t-log backup or it will break the log chain.

Very likely your maint plan has a Transaction Log backup happening in it. Full Backups won't normally break a log chain, but I've seen enough maint plans where somebody sets them up with either two steps and one is a t-log backup or the t-log backup is accidentally selected instead of the full or differential.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

djguana SSC Enthusiast Points: 152 More actions · Answer 13

here is my maintenance plan :

is a skrink database may cause the issue ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Please attach the image to your post. Below the edit box there's an 'Edit Attachements' button.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

djguana SSC Enthusiast Points: 152 More actions · Answer 15

djguana

SSC Enthusiast

Points: 152

March 25, 2011 at 4:21 am

#1303460

done