I recently got following email from one of the readers. It is about Backup Log file.
Hi Pinal,
Even thought my database is in full recovery mode when I try to take log backup I am getting following error.
BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)
How to fix it?
Thanks,
[Name and email removed as requested]
Solution / Fix:
This error can happen when you have never taken a full backup of your database and you try to attempt to take backup of the log only. Take full backup once and attempt to take log backup. If the name of your database MyTestDB follows procedure as following.
BACKUP DATABASE [MyTestDB] TO DISK = N'C:\MyTestDB.bak' GO BACKUP LOG [MyTestDB] TO DISK = N'C:\MyTestDB.bak' GO
Please note that this is a very common issue which we see when a user have just changed their recovery model from Simple Recovery Model to Full Recovery Model. It is recommended to take full backup whenever you change your recovery model to avoid any unpleasant situation. Remember, it is important that your backup can be restored in your system so always try to restore your backup on your development server.
Please leave a comment with your opinion.
Reference: Pinal Dave (https://blog.sqlauthority.com)
24 Comments. Leave new
Can also happen if you change the recovery model. If you happen to switch from full to simple and back to full (such as for reindexing maybe) then it should immediately followed by a full backup. SQL 2000 used to back up logs with no errors in this situation but the logs will not work if you attempt to restore. SQL 2005 and 08 do error out asking for a full backup.
That is the answer that I was looking for.
Hi
After changing the recovery model also we will get the same error.but when he is trying to take log backup after full backup the recovery model chaged to simple and again moved to simple(here LSN chain was broken).the solution for this to maintain log chain we need to take differential backup it will allow you to take log backups after differential backup.for more inforamation https://www.sqlskills.com/blogs/paul/category/backuprestore/
Thanks
Balakrishna
small corruptions above “again moved to full” above post
Hi Pinal
I’m getting the same error message. BUT my backup is present!
At 2000 I take a full backup of my database (recovery model is set to FULL.)
Every hour I take a transaction log backup. It works at 2100, 2200 etc until 0600 when it fails with the message
“BACKUP LOG cannot be performed because there is no current database backup.”
I checked the backup location – the .bak file is there, as are all my .trn files up to 0500. I haven’t run any truncate jobs.
I take an ad hoc backup at 0953, and the 1000 log backup works perfectly.
What am I missing? Does Microsoft have a special definition of “current”?
Regards
Jack
Ah – found the problem – there *was* a job running to truncate the logs of one of the databases – now disabled, and I expect the problem to go away.
a transaction log backup work between 2 Diffrenshial backup
you should first run a diffrentioal backup and then run transaction backup,
have a good time,.
transactional log truncation is the issue..
Hi pinal,
SuperSocket Info: Bind failed on TCP port 1433. what does this error means and how to fix it
In my server, data is saving in temp database not in .mdf & .ndf thus i need to fix how can fix this issue’s
Following issues
1. Invalid Protocol specified for a ?????t?c?????t?????? instance: ???y?????t?c?????t??????.
2. SuperSocket Info: Bind failed on TCP port 1433.
3.LogEvent: Failed to report the current event. Operating system error = 31(A device attached to the system is not functioning.).
Please advise me the solution or how to fix this issues
Regards
Ram
i need to open vb exe’s in window 7 and office 2000 but it asking that , no current datebase create or open
I have a load job that switches to Bulk-Logging and then back to Full. All that I have found states that this error is when switched to Simple, then back to Full. Does anyone know if this is correct because I have not seen this before with bulk logging to full.
Forget my last because it seems that some changed setting from bulk-logging to simple so that explains the issue.
If you want to restore backup in new database then you have to un-check the “take tale log backup” in Options page.
Thanks to Manoj Manish it works for me and saved lot of time…
What if the database size is around 200GB. Taking backup first means keep 200GB odd disk space free and then wait for the LOG backup to happen. This definitely is not the option!!! :(
Hi to all,
If the database is from 2000 want to Restore to 2012 then first restore to 2005 / 2008 then restore to 2012 by unchecking tail-log Backup option.
The error can be rectified (in MSSQLSERVER2012) and database can be restore by Unchecking the Tail-Log Backup from Options in Restore Database Window.
you are Seriously Awesome!
Dear Deve,
if I run same below sql backup query
backup database School to DISK = ‘C:\School_Full_database_Backup.BAK’
more than one time, why back up file size keep on increasing rather than just overwrite the first file?
i am using Sql server 2008
Thanks for this, it worked. OTOH, don’t understand the need, I was creating a new database from a backed up copy so there shouldn’t have been a backup.
Hello Sir
I have taken full backup as you have suggested after that i have tried for log and got same error.
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.
could you please help me out to resolve this issue.
You can also see this error if you’re running COPY_ONLY. We recently stopped using a 3rd-party tool to do our FULL backups. Unknown to us, that tool had an issue with a pair of our DBs, and the log chains to it were corrupted.
If using COPY_ONLY, try removing that to get a fresh log chain started. I’m not sure if you are running that or not, sorry if I missed the code snippet.