June 23, 2011 at 8:15 am
Hi,
For testing purpose, I created one database on test server took full backup of this database. Deleted its mdf file, now I am tried to take tail log backup to do point in time recovery but it is showing the below error :
------------------error message------------------------------------------------
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
--------------------------------------------------------------------------------
Thanks in advance.
June 23, 2011 at 8:24 am
if you deleted the mdf file, you had to have stopped the server to do it...so when you restarted the server, didn't the database show an error? did you recreate a database with the same name due to an error?
Lowell
June 23, 2011 at 8:31 am
Yes I stopped the service, I just stopped and started the SQL service. Database had not showed any error when I started the SQL services again. I have not recreated the database. The same database is there but it is not showing any objects in it as .mdf has been removed.
June 23, 2011 at 8:35 am
gotcha, so in a disaster recovery scenario like that, where the mdf was damaged/lost, you would not be able to do a tail backup...
all you can do is restore the full backup plus any transactional backups you have...so the data loss would be from last transaction log to the moment the mdf was lost.
you'd do a tail backup if someone came running to you and said "i just updated ALL invoices without a WHERE statement" or something...THEN you do a tail, and a point in time restore to what should be just before the offending mistake. the database would still be in a running, recoverable mode before the tail./
Lowell
June 23, 2011 at 8:37 am
Lowell (6/23/2011)
gotcha, so in a disaster recovery scenario like that, where the mdf was damaged/lost, you would not be able to do a tail backup...all you can do is restore the full backup plus any transactional backups you have...so the data loss would be from last transaction log to the moment the mdf was lost.
you'd do a tail backup if someone came running to you and said "i just updated ALL invoices without a WHERE statement" or something...THEN you do a tail, and a point in time restore to what should be just before the offending mistake. the database would still be in a running, recoverable mode before the tail./
Ah!, nice new sigh Lowell :w00t:
June 23, 2011 at 8:39 am
Ninja's_RGR'us (6/23/2011)
Ah!, nice new sigh Lowell :w00t:
Thanks! i got inspired by seeing at least three recent threads with that same theme.
Lowell
June 23, 2011 at 9:11 am
Lowell somewhere on internet i read that we can take Tail Log backup if .ldf is not missing or corrupted or lost or deleted.
June 23, 2011 at 9:13 am
So, I would not be able to take Tail Log backup if .mdf if missing.
June 23, 2011 at 9:19 am
beejug1983 (6/23/2011)
So, I would not be able to take Tail Log backup if .mdf if missing.
<disclaimer : start testing mode>
Here's what I'd try if I were you :
Rename the old ldf file.
Recreate the whole database
Stop Server
Delete the new log file
Un-rename the old one
Start Server
Hope that you get past some of the recovery where you can take the tail log
<end test mode>
I've never done this but if I had time to spare I'd give that a go. Keep in mind that I'm no DR expert and have not read a single book on the subject except a few 100 posts on this site.
As a side note. This is why I take log backups every 15 minutes. With only 50-60 employees working on the system it would be a pita to lose 15 minutes but not the end of the world.
June 23, 2011 at 9:40 am
Thanks guys..
June 23, 2011 at 10:37 am
what command did you issue for the tail backup? If the .ldf was intact you should have been able to back it up with the no_truncate option.
norecovery would only be used if the database was online.
---------------------------------------------------------------------
June 23, 2011 at 10:42 am
.... and what's the state of the db? Does it at least show in the list of databases? If it does in any fashion at think you have a shot at the log.
June 23, 2011 at 11:09 am
Lowell (6/23/2011)
gotcha, so in a disaster recovery scenario like that, where the mdf was damaged/lost, you would not be able to do a tail backup...
That's exactly the scenario where you can and need to do a tail log backup (It's a missing ldf where you can't)
To do a tail-log backup where the mdf is missing, you'd run the log backup WITH NO_TRUNCATE.
However the error has nothing to do with that.
An error saying there's no current database backup when running a log backup means that the DB has been switched to simple recovery and back to full or an explicit log truncation has been run and there has not been a full or diff backup taken since. In this situation, there is NO way to take a log backup (the log chain is broken) so the best you can do is restore full, diff and any log backups you have.
To reiterate, the error is not because of the missing mdf file, it's because of the broken log chain.
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 23, 2011 at 11:11 am
Any comments on my wild guess Gail?
June 23, 2011 at 11:13 am
In this situation, there is NO way to take a log backup (the log chain is broken) so the best you can do is restore full, diff and any log backups you have.
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 - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply