March 16, 2011 at 1:28 pm
I have a transaction back up failed with the following error.
What could be wrong, how can I fix it?
Description: Executing the query "BACKUP LOG [Metadata] TO DISK = N'G:\Backups\Meta..." failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:19:10 PM Finished: 12:19:16 PM Elapsed: 5.89 seconds. The package execution failed.
Thanks!
March 16, 2011 at 1:30 pm
There's been either an explicit log truncation or a switch to simple recovery (and back to full) since the last full/diff backup
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
March 16, 2011 at 2:37 pm
Thanks.
This is a maitenance plan for many databases, is there a way I can find out which database got changed from Simple to full, or an explicit truncate log.
And what should I do to fix this in order to have a successful transaction backup.
Thanks
March 16, 2011 at 2:50 pm
I just found out in SQL log there are some records like:
Setting databaes option ANSI_Padding to ON for database ...
Starting up database auto_upate statisticst to Data base Metadata
Setting database options to recovery full to simple.
It looks a programmar did that.
Does that mean every time if there is some options changed we need to do a full backup, or can I do a diffrential back up, so that the the transaction backup could be successful?
Thanks
March 17, 2011 at 1:54 am
annasql (3/16/2011)
Does that mean every time if there is some options changed we need to do a full backup, or can I do a diffrential back up, so that the the transaction backup could be successful?
A diff will work, though you'll then have to remember or document that such a diff exists in case you need to recover.
I would suggest asking around and finding out who did that and having a word with them. Changing recovery models of a production DB is not something a developer should be doing. They could have had nasty effect on database recoverability.
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
March 17, 2011 at 5:33 am
Basically, it works like this:
A full backup is just that - a full backup.
It will completely restore the database to its state at the time the full backup was made.
A differential backup is a backup of all pages in the database that have changed since the last full backup. You may think of it as an update or "service pack" to the full backup, and it can only be restored together with the full backup it's based on.
Log backups can be based on either a full backup or a diff backup. They require an unbroken log chain from their base and up to the present, meaning that if your log is truncated - either by a recovery model change to simple or by a "truncate_only" backup command - you will need to make a new full or diff backup before you will be able to make any new log backups after that.
In a restore situation, you always have to start with a full backup.
You can then update this full backup with any available differential backups based on that full backup. Diff backups do not relate to each other - only to the last full backup - so you only need the latest diff prior to the point in time to which you want to restore. Transaction log backups can be restored on top of that again to "roll forward" up to where you want to go.
If you miss a full backup, diff backups will continue to build on the full backup before that, so no real problem there except your restore might take a bit longer. If you miss a diff backup, you will still be able to restore from log backups if you have those in order, so no real problem there either except once again, your restore is likely to take longer. And if you haven't had any backups at all for a day or two but your database is in recovery model Full and has a kind of big transaction log... stay calm and make a backup of that big transaction log! If you have an unbroken log chain, this log backup will cover the entire time period that your database was without backups and you'll be able to restore to any point in time within this period by using this log backup. So, no real problem there either - except again, your restore operation will take longer if you have to restore whole days from transaction log backups.
As you can see, if you treat your database and backups right, most things can be worked around without much impact on recoverability.
Your two main concerns here are keeping your log chains unbroken (or immediately restart them with a full or diff backup if you know they've been broken) and always keeping in mind that diff backups are based on the latest full backup.
In other words: Making an ad-hoc full backup of a database (unless made with the "copy only" option introduced in SQL server 2008) has a direct impact on database recoverability because any subsequent diff backups will be based on this full backup and not your last scheduled full backup. So, if someone made an ad-hoc backup of a database, copied it to another server for restore and then deleted the backup file, you will not be able to restore any of your differential backups from the time period between this action and your next full backup. ( ! )
But again, you will be able to restore from log backups if you have all of those available. Contrary to what many believe - making a full or diff backup does not truncate the log, so you don't need to have all full or diff backups made in the period - you just need an unbroken log chain from your "base" (either a full or diff, remember?) and up to where you want to go.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 18, 2011 at 8:25 am
In other words: Making an ad-hoc full backup of a database (unless made with the "copy only" option introduced in SQL server 2008) ...
Minor amendment: Copy-only backups http://msdn.microsoft.com/en-us/library/ms191495%28v=SQL.90%29.aspx
were implemented in SQL Server 2005.
Rich
March 18, 2011 at 10:28 am
rmechaber (3/18/2011)
In other words: Making an ad-hoc full backup of a database (unless made with the "copy only" option introduced in SQL server 2008) ...
Minor amendment: Copy-only backups http://msdn.microsoft.com/en-us/library/ms191495%28v=SQL.90%29.aspx
were implemented in SQL Server 2005.
Rich
Hey, ooops, I kind of thought I'd had that option available to me for a bit longer than that, yes.
The scripting option for it was implented in 2005 as you say, but the button for it in the GUI did not appear until 2008.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 18, 2011 at 10:43 am
Thanks, this is very helpful for me to understand the backup and restore.
I understand most of it, and will add it as a bookmark and keep checking it.
Thanks
March 18, 2011 at 10:48 am
The last statement doesn't make sense to me, quote:"But again, you will be able to restore from log backups if you have all of those available."
So if someone deleted the adhoc full backup, do you mean I can still recover it from the full backup before the adhoc full and then a differential backup before the ADhoc full, and then what....?
If there is a diffrential backup before the transaction log backup, does the transaciton log backup is based on the differential backup?
If the differential backup is deleted by someone, can I still use the full and the transaction log backup to restore?
Thanks
March 18, 2011 at 10:50 am
Log backups. Starting with the one taken straight after the diff.
This may help a bit: http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
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
March 18, 2011 at 11:08 am
To expand a bit on this: to perform a point-in-time recovery, you MUST have a full backup and all log backups performed between that full backup and the point in time you wish to restore to. That's all you MUST have.
So, as Gail's article will tell you, if you have a full backup from 5 years ago and every log backup from the past 5 years, you could perform a point-in-time recovery to this morning. It will take forever and is not a good DR practice. But it won't matter how many other full or differential backups were taken in the past 5 years, so long as the DB recovery mode wasn't changed to Simple or some such.
In practice, if you wish to restore to some time, the typical approach is to restore from the most recent full backup (assuming the point-in-time occurs after that backup), then to restore the most recent differential backup (same assumption), then all the logs after the differential to the point-in-time. The ability to use differential backups means that you can perform a restore faster b/c you don't have to restore the log backups taken between the full backup and the most recent differential taken before the point-in-time.
As an example, say you perform full backups Sunday at noon and daily differentials M-Sat at noon. It's Thursday, and you need to restore the DB to Wednesday at 9:30 a.m.. You'd restore last Sunday's Full backup, Tuesday's differential backup (b/c it is the most recent differential backup taken before the desired restore time), and the log backups taken between Tuesday noon and Wednesday at 9:30 a.m.
But if you've lost the Sunday full backup, those daily differential backups are of no use to you. To be able to restore to the same Wednesday, you would need the to have the previous Sunday's backup and every log backup from between then and Wednesday (10 days' worth).
Try it on a test database until you are confident it works.
Do read everything you can of Gail's blogs/articles. She is one of the best contributors on this newsgroup and excels at debunking SQL myths.
Rich
March 18, 2011 at 9:42 pm
Thanks.
This is very helpful for me to undertand.
One point not sure is : when you say:"But if you've lost the Sunday full backup, those daily differential backups are of no use to you. .....
I guess "lost" means it did a full backup but was deleted somehow.
But in situation if it was a failed full backup on sunday, can I still recover from previous sunday full backup and the differential backup after the failed this sundays full backup?
I mean the differential backup can be still in use after a failed full backup in the case there is previous sunday's backup, is that correct?
Thanks
March 19, 2011 at 1:32 am
Yes, if the full backup never ran at all so that the full backup before that is still the most recent full backup, you can still use all your diffs. But - if you made a defective full backup, then although defective, it is still the most recent full backup, thus creating a gap you will need your log backups to bridge.
Edit: Fixed a typo. 😉
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 19, 2011 at 3:00 pm
annasql (3/18/2011)
Thanks.This is very helpful for me to undertand.
One point not sure is : when you say:"But if you've lost the Sunday full backup, those daily differential backups are of no use to you. .....
I guess "lost" means it did a full backup but was deleted somehow.
But in situation if it was a failed full backup on sunday, can I still recover from previous sunday full backup and the differential backup after the failed this sundays full backup?
I mean the differential backup can be still in use after a failed full backup in the case there is previous sunday's backup, is that correct?
Thanks
Glad that helped.
Yes, I meant someone deleted it, or misplaced it, or never copied it to tape, or it was overwritten, or....
I have to say, I don't know whether there are classes of errors that could occur in a full backup attempt that would/wouldn't break the backup chain. There's an option to "continue after errors", and I'll admit I have no idea if the differential backup chain is restarted or not in such a case, or if it depends on what error was encountered.
Me, I assume that any error encountered during a backup means I have a problem needing immediate investigation, that my backup chain is broken, and that I need to preserve my old full backups and my log backups until I (1) find and fix the problem and (2) have run a successful full backup.
If your question is to explore the backup and restore process, great. But as a pragmatic issue, you're doing it wrong if you did a full backup on Sunday that failed and didn't investigate and fix the problem on Monday ASAP.
And do practice as much as you need to to be comfortable with a restore. Take notes on things you forget or get wrong and keep them in a disaster recover location.
Rich
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply