| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2010-10-20 : 18:46:03
|
| If you start afresh with a company as a DBA, and notice there are no backups organised at all within SQL Server, and the first person on hand to ask about this is your line manager, senior DBA, what would you say?If otherwise, you have to ask a non-tech person Financial Director, what would you say.When your answer is that there is no need to perform your backups, as your IT Support people schedule daily overnight backups on 'all the files' with Windows Server with a tool such as Veritas, what's your response to that? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-20 : 19:40:25
|
I was at a client site one time to help them out with some performance problems they were having.During the initial conversation it came out that they hadn't been able to get backups for over 3 weeks.I asked them what would happen if they lost all of the data. The answer was exactly what you'd expect.I said "ok, let's start with the backups, then once we know we have good backups we'll address the performance issues."They loved me after that  |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-20 : 19:41:39
|
| By the way, I've said this b4 and I'll say it again:The #1 thing a DBA needs to do is ensure there are good backups. Period. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2010-10-21 : 02:21:32
|
| Should backups as .bak, .trn and .log always start with SQL Server, or is it practical to ignore that part, and just get those files copied regularly to another place and kept safe? A lot of people take the second approach, and I can't really see much wrong with that... |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-21 : 02:25:01
|
| should do native backups and archive them. copying off the data files with veritas is not a safe practice. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-21 : 04:42:26
|
| The main problem with copying the files off is that they mayy well be unusable.SQL locks its files while the DB is open. Hence a backup tool that can't read through file locks will skip those files. A backup tool that can read through file locks can end up with inconsistent and unusable files because of the way SQL does its writes (log before data, always).The only safe way to do a file backup of SQL is to use a backup tool that uses SQL's VSS writer, which requests SQL to temporarily suspend its IO operations while the files are copied. That's how the SAN shapshot backups of SQL work. Of course, the IOs can't be suspended for long.Using a standard file backup tool (that doesn't hook in to the VSS writer) is like playing Russian Roulette with your database backups. Maybe they worked this time, but there's no guarantee that the files will always be usable and in the middle of a disaster is not the time to find that the latest backup is useless because SQL considers the files inconsistent.In addition, file copies cannot give you point-in-time restores. A combination of full and log backups can--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 06:26:04
|
| " I can't really see much wrong with that"How would you restore them? Will your software restore them such that the DB is "consistent" and "valid" from SQL's perspective (I doubt it, but I'm just wanting to plant appropriate questions in your mind).How easily could you restore to DEV or a replacement server?How often can you get files on to tape? Once a day? always at exactly the same time?, or will it depend on the number of files needing to be backed up that day? What happens if the time is variable - will that make life difficult when trying to work out disaster recovery scenarios? How easily can you get a list of what-back is on what-tape? (There is a query in SQL you can run to find that out for SQL Backups you make, so its easy for SQL Backup Files)What happens when you want to restore? Find appropriate tape, and restore? What if a tape backup is in progress, how long will you have to wait? What if the tape you need is "the one" that is currently off-site (for fire/total catastrophe avoidance)?OK, so the tape drive is not in use, the tape is available, how long to restore the file? (wind the tape forwards and physically restore the file)? If that backup turns out to be be duff how long until you can restore your second-choice from tape?Compare that with BAK files on disk. No "retrieve from tape" time, lets assume you have a week's worth of backup files on disk (which is what we aim to store at "restore-ready" state). So then you can restore to any point in the last week without having to get a tape out of storage (and lets face it, if you want to restore back MORE than a week things are REALLY BAD!). Furthermore the file-backups are made to schedule (not a variable time) and once on disk the Tape Backup can store them to tape whenever it next runs (which needs to be soon-enough for whatever your disaster-recovery plan requires).You can copy the on-disk-backup-files to A.N.Other server hourly / whatever to give you a little bit more redundancy / recoverability - Tape is probably only going to copy them once-a-day, so if you lose the server you will lose at least 24 hours data - if you copy to A.N.Other server then you will lose and hour or two at most.If you have an OLTP application (transactions - e.g. where operators are keying data in all day long) you need to be using Log Backups. You need to be able to restore to point-in-time. If someone (Person, Accidental application task, or just a bug in the application) deletes some data you can restore it from point-in-time backups (actually you will restore the backup(s) to a New, Temporary, database, and then just copy the "lost" records across to the Live databases).If your database becomes corrupted you can take a final "tail" Log Backup and then (9-times-out-of-10) restore the whole lot from backups with ZERO data lossIf your Financial Controller tells you that s/he suspects fraud you can restore the Log backups piecemeal (to a New, Temporary, database) to see what-changed-when to get some evidence of what happened.These things are much harder when all you have is tape backups (i.e. software that does a Full Database Backup direct to tape).They are significantly harder still if all you have is copies of the physical database files on tape (assuming that they are physically viable when restored, which I think is a bit doubtful)Summary:Take SQL Full Backups to disk (e.g. daily). If you have OLTP Application then use FULL Recovery Model and also take LOG backups every 15 minutes (question it if they oare only being taken once an hour, or less frequently, you are increasing the risk of data loss for NO gain). If application is not OLTP then SIMPLE Recovery Model and (say) one full backup a day may be enough.Then have the Tape System copy the backup files to tape (and make sure that the tape software is configured NOT to copy the physical files at all)Perform regular Restores to A.N.Other server to prove that "you can" - otherwise the first time you will discover that you can't is when you first need to!Perform regular DBCC CHECKDB on the database to check it is not corrupted (better still, do this on the Restore Database on the A.N.Other server INSTEAD (not AS WELL as on the live server, there is no need and it will slow the live server)And to answer your question:"If you start afresh with a company as a DBA, and notice there are no backups organised ..."I would leave immediately! (Unless my first conversation with Line Manager / Financial Controller understood clearly enough that when s/he said "Fix that immediately" it was clearly meant!) |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2010-10-21 : 07:18:38
|
| Wow! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-21 : 07:24:04
|
Well so long as you understand it, or raise those thoughts with your line manager (if its a real scenario), then it was worth typing it  And the next person coming along needs to do the same.Otherwise there will be trouble / detention / frowning-eyebrows  |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2010-10-21 : 08:36:17
|
I would add that if you can't demonstrate recovery from disaster then you don't have a viable strategy for recovery. That's true even if you have .bak files. A .bak file is worthless if you can't restore from it!That means you must occasionally RESTORE from your .bak on a test system to prove to yourself that you COULD recover if you needed to.
elsasoft.org |
|
|
|
|
Backupz