August 9, 2012 at 2:07 pm
The restoring process gives me an error: "the tail of the log for the database XYZ has not been backup up"
The database works fine (except that users makes errors and the DB needs a restore)
The backup process works fine too (full or transaction log) and gives no error message (used Management studio)
We have a special situation here: most of our databases are not on network and DB are moved from a computer to another one. The DB comes from a copy of the detach database (two files .mdf and .ldf) from one computer "X" to another one "Y" in the appropriate directory then is attached to the server on the "Y" machine .
For years, I always did a "full" backup before moving the files and I never had problems moving the DB from one computer to another.
Why this one gives me problem now? The only thing I can imagine is that this particular one was on a "network" ???
How to restart a new backup, deleting all the backup history, and making sure that the future restore would work?
Where the "tail of the log?" is stored? Is it still on the previous SQL serveur (on the network) or is it in the files of the DB?
Thanks a lot for your help
August 9, 2012 at 9:37 pm
The tail of the log is the part of the transaction log that hasn't been backed up yet. if you take a log backup of the databse you are replacing before restoring over it, the error will go away.
August 10, 2012 at 7:12 am
tilew-948340 (8/9/2012)
The restoring process gives me an error: "the tail of the log for the database XYZ has not been backup up"The database works fine (except that users makes errors and the DB needs a restore)
The backup process works fine too (full or transaction log) and gives no error message (used Management studio)
We have a special situation here: most of our databases are not on network and DB are moved from a computer to another one. The DB comes from a copy of the detach database (two files .mdf and .ldf) from one computer "X" to another one "Y" in the appropriate directory then is attached to the server on the "Y" machine .
For years, I always did a "full" backup before moving the files and I never had problems moving the DB from one computer to another.
Why this one gives me problem now? The only thing I can imagine is that this particular one was on a "network" ???
How to restart a new backup, deleting all the backup history, and making sure that the future restore would work?
Where the "tail of the log?" is stored? Is it still on the previous SQL serveur (on the network) or is it in the files of the DB?
Thanks a lot for your help
You are restoring over the top of an existing database and you have not checked the WITH REPLACE option.
If you want to replace the existing database completely check the WITH REPLACE option otherwise take a log tail backup first
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2012 at 6:23 am
Perry Whittle (8/10/2012)
You are restoring over the top of an existing database and you have not checked the WITH REPLACE option.
If you want to replace the existing database completely check the WITH REPLACE option otherwise take a log tail backup first
The restore works fine with the replace (thank you!) BUT it is a temporary solution because if I try another restore, I still have the tail log error message and I do not want to always check the "with replace" option in the futur.
I did many tests like making a new backup of transaction tail log or backup to a new media set, but I am still not sure what I have to do to "reset" the backup log and making the restore without any errors.
What should I do to never have this error message in futur restore?
August 13, 2012 at 6:34 am
The message is warning you that there are transactions in the log that will be lost if you restore. It's a safety net. The assumption is, if the DB is in full recovery then data loss is not acceptable and hence losing data because you haven't backed the tail of the log up is a very bad thing.
If point-in-time restores are not necessary for this DB, put the DB into simple recovery model. If point-in-time restores is a requirement and data must not be lost, then you need to back the tail of the log up before restoring over the DB or you'll lose all data since the last log backup.
You backup the tail of the log with BACKUP LOG ... WITH NORECOVERY, that puts the database into a restoring state, ensures that no more transactions can occur and that you cannot lose data by restoring over the DB.
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
August 13, 2012 at 7:25 am
GilaMonster (8/13/2012)
... then you need to back the tail of the log up before restoring over the DB or you'll lose all data since the last log backup.You backup the tail of the log with BACKUP LOG ... WITH NORECOVERY, that puts the database into a restoring state, ensures that no more transactions can occur and that you cannot lose data by restoring over the DB.
Still does not resolve the restoring in futur: I did the backup log with norecovery, did a restore like you said and it worked fine (had no error message)
BUT
then I changed one line, did a new backup, then tried to restore from the "new" backup I just did.
I still have the "tail log has not been backup" error message (unless I check the with replace option).
I never had to do the backup of the log to restore, never had an error from the log. So why now and how to stop it?
August 13, 2012 at 8:05 am
Because your database is in full recovery model. When a database is in full recovery SQL assumes you care about the data and don't want to lose any by accidentally restoring over a database without taking a log backup first
You need to either:
- Switch the database to simple recovery if you don't need point in time restores
- Take a tail-log backup right before starting a restore
- Do the restore WITH REPLACE
Pick one.
Which you do depends how much you value the data in that database and whether you are happy to lose data when you restore over the top.
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
August 13, 2012 at 9:40 am
GilaMonster (8/13/2012)
...You need to either:
- Switch the database to simple recovery if you don't need point in time restores
...
Which you do depends how much you value the data in that database and whether you are happy to lose data when you restore over the top.
For a weird and specific reason (too long to explain why), I need to use the management studio to do the backups, but the only option I have in the management studio is "Full" recovery model. How do you put the option to "simple"?
Because de DB is not on network anymore and only one person works on it at a time, I thought that doing a "full" backup would get all the transaction to a point that every thing would be on the backup? I am wrong? :crazy: ...But that would explain why the copy of the DB from the server on the network needed a transaction log and maybe still does need it even after the move... I'll try to mount the virtual machine and do a new backup to see what is happening...
August 13, 2012 at 10:14 am
tilew-948340 (8/13/2012)
How do you put the option to "simple"?
Using T-SQL
ALTER DATABASE yourdb SET RECOVERY SIMPLE
[/CODE]
Or using SSMS GUI, right click the database and select properties. On the database properties select the Options side tab, there is a drop down list box for recovery model where you may select Full, Simple or Bulk-Logged
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2012 at 10:38 am
tilew-948340 (8/13/2012)
For a weird and specific reason (too long to explain why), I need to use the management studio to do the backups, but the only option I have in the management studio is "Full" recovery model. How do you put the option to "simple"?
The 'option' on backups is just telling you what the recovery model of the database is. You don't set it from there
I thought that doing a "full" backup would get all the transaction to a point that every thing would be on the backup? I am wrong?
No, you're mostly correct, but a backup is just to point-in-time that the backup completes, it's entirely possible for something to start just before the backup completes and that won't be in the full backup. Because of that, it's not absolute protection for the log records, which being in full recovery implies to SQL Server that you need and want.
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
August 13, 2012 at 11:36 am
I put the DB in "simple" recovery model and the error message is gone. I will still make some test with the backup from the networked virtual machine (just in case it has to go back on network), but for my immediate needs, the "simple" solution is going "simple" on the recovery model
Thanks a lot to everyone! still lots to learn about backups as you can see...
August 13, 2012 at 12:04 pm
Before you go and set every DB to simple recovery, make sure that simple recovery is acceptable for your disaster recovery and backup strategies...
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply