What is a Tail-Log Backup failure?
-
I've got a small SQL Server 2022 Developer Edition database on my laptop. It has three tables, the one with the most data in it has about 15 rows. So, this database is small. I have it in a C:\Databases folder on my laptop.
I wanted to copy it over to my desktop and put it into the same folder on my desktop. I got into SSMS 20.2, selected the database, performed a full backup, which SSMS then reported as having happened successfully.
So, I then copied the .bak to my desktop, got into SSMS 20.2 there and selected the .bak to restore the database to my desktop. This is where things went bad. The restore issued this error:
Restore of database failed. However, the tail-log backup operation completed successfully
Huh? What in heck is a "Tail-log" backup? Why is it that SSMS on my laptop informs me that everything went well, but when I tried to restore the database on my desktop, it complains and tells me that it failed??? How can it simultaneously succeeded and failed, and NOT tell it that it failed? Something, somewhere, has lied to me and I don't like it.
I tried looking up the error, which brought me to an article on Microsoft Learn, but honestly, I am no clearer as to what a tail-log backup is, why it failed, what I'm supposed to do about it, etc. I got out of SSMS on my desktop, then back in again in hopes that might somehow resolved the issue, but I'm wrong. Instead, it has the name of the database in the Databases folder, and the string "(Restoring...)" after the database name. But it is not in the C:\Databases folder, where I wanted it to be restored to and exactly where it would have restored it, if this Tail-Log thing hadn't happened.
I could use some clarification on whatever this Tail-Log thing is, why doing a full backup failed even though it said it succeeded, how to fix the failed restore, etc.
Rod
-
I'm not sure why it went wonky Rod. A restore of a full backup shouldn't be doing tail log backups, successful or otherwise. Something odd going on. Did you use the GUI or a SQL command for the restore?
As to what a tail log backup is, it's when you're planning to restore to a point in time. You've got regular log backups running. Let's say, for our example here, every 20 minutes. It completed successfully about 8 minutes ago, again, plucking numbers out of the air for the example. Now, you have 8 minutes worth of log entries. So, you run a manual log backup. This is referred to as a tail log backup. It can happen through use of the SSMS gui as well. But it's the residual transactions since the last log backup.
However, yeah, no idea why you're seeing this as part of a full backup restore, not a point in time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
The GUI - by default - performs a tail-log backup of the existing database when you perform a restore over that existing database. If you do not uncheck the box in the GUI you will get that message.
The assumption here is that you would want to be able to recover to the latest point in time for the overwritten database if something were to go wrong with the restore operation, or just to make sure you can restore to the latest point in time.
I have to assume you did the restore through the GUI and did not uncheck that item.
One other note - when you perform a tail-log backup, SQL Server takes that database offline once the tail-log backup has completed. This prevents any further transactions from being applied to that database. Just something to remember...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
That's what I get for not using the GUI for backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you, Grant, for responding. I am using the SSMS graphical UI. Here's what it looks like in SSMS:
Is it the LogBackup that I've messed up?
Rod
-
A tail-log backups only purpose is that you can restore the original database as is was on the target server right before you performed the intended restore.
What's the difference with a regular log backup? The use of parameter Norecovery. It sets the database in restoring state after it completes !
ref: Tail-log backups (SQL Server)
Of course, a tail-log will fail if no full backup has been completed ( i.e. if you have created the database and have not yet created a full backup ( it initiate the log to work and log backups to be possible at all )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I did perform a backup of the database. That created the .bak file on my laptop, which I copied to my desktop. There I tried to perform a restore, but that failed with the error message concerning Tail-Log.
Rod
-
Make sure you check the options on all pages. By default the GUI includes a tail log backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Actually, if you restore the backup to an instance as a new database, there is no need for a tail-log backup at all !
( this is a flaw in the SSMS dialog )
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply