August 12, 2012 at 5:50 am
So, let me first preface this by saying, I've been a SQL Server DBA for 13 years. I've performed some hardcore administration over the years for some hardcore clients (reebok.com, subaru.com, ups, priceWaterhouseCooper.com, Accenture, on and on and on), but I was reprimanded pretty good from my current job, from my ORACLE lead ( yes, he is the lead over myself which is the SQL Server department). That scourning had to do with backups and restores. I thought I would ask a question of my peers, to see if you all agree, disagree, or what your thoughts on this issue(s) are.
My experience has taught me, that if the database is small enough, it's always a good idea to just do a full backup of the database (in this example it was 23 GB) before an upgrade begins. It took about 10 minutes to do the backup in this case, and 10 minutes for the verify. My lead thinks that is a waste of time, and that I should just do a simple TLOG backup of the database(which would have happened in just a couple minutes, if not seconds) beforehand because it's "easier", as if he knows more about SQL Server than I do.
Here's why my experience tells me that a full backup is best, and why I do not think it is "easier" to just kick off another TLOG backup beforehand. That there is much more to it, than just "easier".
#1. If I have to restore that database, restoring a full backup and 23 TLOG backups, is not "EASIER" in my book. Nor, is it the quickest. Restoring the full backup is all you would have to do in my scenario, but in the TLOG scenario, this is where you would have to start. You would then need to apply 23 logs after restoring the full backup.
#2. I've done this long enough, that I know, and have had to deal with over the years, that everytime you do a TLOG backup, you introduce additional risk. I know we have verify's, but if something, ANYthing, happens to one of those logs, in the middle of this 23 log chain, I'm fu*k$@. I can't tell you how many log files don't come off disk right, or something has happened with LSN's, or corruption, etc/whatever over the years. pick your reason/excuse for how something can happen with 24 files to restore, as opposed to 1. I know that when I can do a full backup, and a verify on that backup as soon as it's done, I am in a less risk precarious position, than if I have to restore 1 full , and 23 tlog's (or more).
#3. I know there are other reasons as well, so please be my guest to list any additional ones for my lead to see.
My hope is to show this thread to my oracle lead, so he has an opportunity to see, that yes, while it may be "easier" and "quicker" to just kick off a small TLOG backup, that if something happens, it's not "EASIER" to restore, nor the "safest" when/and or/if a restore needs to occur behind it. If you all disagree with me, and agree with him, then so be it. I'll show it to him either way.
Thanks for your thoughts.
August 12, 2012 at 6:47 am
I would agree with you, you want the fastest possible restore if such a restore is necessary, that's a full backup (or at most a differential backup that cab then be restored with the last full)
That said, there's an even easier way (if you're using enterprise edition). Create a database snapshot before the upgrade. It's pretty much instant. If something goes wrong then you can just revert the snapshot.
Reverting the snapshot breaks the log chain, so another full or diff backup would be needed afterwards and the log will have to be resized, but it is about the easiest way to return a database back to a known state.
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 4:27 am
I'm with you. If you can get a quick, full backup (copy only of course), or better still, like Gail says, a snapshot, then that's the way to go. Absolutely, 100%. It's going to lead to faster recovery in the event of an issue. Plus, do you have backup testing in place? Do you know that you'll absolutely be able to restore the system? If not, a little paranoia goes a long ways. Same rules don't apply as the system grows, but for small systems, yes, you're in the right here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2012 at 7:34 am
I agree completely with you. A full db backup is the way to go. Especially if something goes wrong and you need to restore. The last thing you want to do is create extra work for yourself and have the slight possibility of a log being corrupt or missing... granted chances are slim but you just never know. I do full backups before I do any change to SQL Server myself as a rule.
August 13, 2012 at 8:13 am
Had I seen this thread without the already great replies my initial instinct would still be the same as yours. Take the full backup first. As others have already pointed out, it's not the backup we're worried about here, it's the restore. In that case, a full backup is priceless.
August 13, 2012 at 9:24 am
swiedner (8/12/2012)
So, let me first preface this by saying, I've been a SQL Server DBA for 13 years. I've performed some hardcore administration over the years for some hardcore clients (reebok.com, subaru.com, ups, priceWaterhouseCooper.com, Accenture, on and on and on), but I was reprimanded pretty good from my current job, from my ORACLE lead ( yes, he is the lead over myself which is the SQL Server department). That scourning had to do with backups and restores. I thought I would ask a question of my peers, to see if you all agree, disagree, or what your thoughts on this issue(s) are.My experience has taught me, that if the database is small enough, it's always a good idea to just do a full backup of the database (in this example it was 23 GB) before an upgrade begins. It took about 10 minutes to do the backup in this case, and 10 minutes for the verify. My lead thinks that is a waste of time, and that I should just do a simple TLOG backup of the database(which would have happened in just a couple minutes, if not seconds) beforehand because it's "easier", as if he knows more about SQL Server than I do.
Here's why my experience tells me that a full backup is best, and why I do not think it is "easier" to just kick off another TLOG backup beforehand. That there is much more to it, than just "easier".
#1. If I have to restore that database, restoring a full backup and 23 TLOG backups, is not "EASIER" in my book. Nor, is it the quickest. Restoring the full backup is all you would have to do in my scenario, but in the TLOG scenario, this is where you would have to start. You would then need to apply 23 logs after restoring the full backup.
#2. I've done this long enough, that I know, and have had to deal with over the years, that everytime you do a TLOG backup, you introduce additional risk. I know we have verify's, but if something, ANYthing, happens to one of those logs, in the middle of this 23 log chain, I'm fu*k$@. I can't tell you how many log files don't come off disk right, or something has happened with LSN's, or corruption, etc/whatever over the years. pick your reason/excuse for how something can happen with 24 files to restore, as opposed to 1. I know that when I can do a full backup, and a verify on that backup as soon as it's done, I am in a less risk precarious position, than if I have to restore 1 full , and 23 tlog's (or more).
#3. I know there are other reasons as well, so please be my guest to list any additional ones for my lead to see.
My hope is to show this thread to my oracle lead, so he has an opportunity to see, that yes, while it may be "easier" and "quicker" to just kick off a small TLOG backup, that if something happens, it's not "EASIER" to restore, nor the "safest" when/and or/if a restore needs to occur behind it. If you all disagree with me, and agree with him, then so be it. I'll show it to him either way.
Thanks for your thoughts.
Depending on your SQL Server version .. Enterprise or Standard or lower...
I agree to What others already said:
Just ask your Oracle DBA to run below script 😀
SELECT CASE
WHEN cast(SERVERPROPERTY('Edition') AS NVARCHAR(max)) LIKE 'Enterprise Edition%'
THEN 'Database Snapshot is available. So in case upgrade fails, you can revert it !!'
ELSE 'Database snapshot is not available ... Do a FULL Backup and then start upgrade !!'
END as Recommendation
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
August 13, 2012 at 9:24 am
Somehow the comments are posted 2 times ... Need some coffee ...
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
August 13, 2012 at 9:57 am
I have to agree as well, taking a full backup of the database prior to an upgrade is the best answer. Remember: backups are worthless (no matter how quick), restores are priceless.
Having to restore a previous full backup and 23 (or more) t-log backups does introduce the potential for problems.
As an aside, I worked for an organization where the Oracle DBA's didn't even want to run any kind of backup prior to a production database upgrade saying the scheduled backup at midnight was good enough. We had to push for, and in the end, incorporate the backup in our own upgrade scripts.
August 13, 2012 at 12:54 pm
😀 Sounds like the Oracle guy is in serious need of blanket party. 😀
August 13, 2012 at 1:55 pm
There is a lot to be said for just ignoring bad advice and doing things your own way, especially since you would be the one doing the recovery if things go wrong.
There is no way that the extra backup is going to hurt, and it might help.
Of course, I would also say that it would be a good idea to do a tlog backup just before you start, and to note the exact time that you start the upgrade, so that you could restore and roll forward to just before that time. And the database snapshot isn't a bad idea either.
DBAs do not get fired for having too many backups. Having a backup you don't need is better than needing one you don't have.
August 13, 2012 at 2:33 pm
If the database is really small then I'd take a full backup, but mostly i would take a diff pre the upgrade and use this plus the full to restore if i had too.
If i'm correct, I don't think Oracle has a concept of a differential backup as SQL Server does. With Oracle it involves log switches and other messy operations like that.
The problem is, crossing between the platforms is not a good idea unless you have significant experience in both. Its a case of a little knowledge can do a lot of damage
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply