Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2014-01-15 : 05:02:43
|
I hate backing up direct to tape. I'm sure the posh tools do it well, but small clients tend to have rubbish backup tools, and I just don't trust them (install a Service Pack and perhaps Tape Backup Agent is no longer doing it right ... and you don't know until you restore). There's also the issue of whether DIFFs are against a FULL backup I made to disk (easy to restore the combination) or a FULL backup that the Tape made, that I cannot now see on disk, and thus I am snookered if I need to restore that DIFF - I have to get the tape restored.Also restore is much more likely to be to different TEMP database, and compare data, rather than actually restore the DB. Or restore to be able to run CHECKDB etc. etc.Thus Backup to File, and copy File to Tape, I think is better.However, in Disaster Recovery I don't want to have to manually recreate databases, remembering the file locations, and all that jazz. So I do need the tape backup to restore "complete machine context" so that we can get the machine back, everything in the right place, and then restore Latest Backup of each DB.What's the best option to achieve that?Let's assume we need to use Backup Exec for the tape backup, 'coz it freely available to my small clients, therefore costs nothing  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-15 : 13:16:24
|
| Will this be in addition to the backups to disk? If so, then you'll need to specify a COPY_ONLY backup so that it doesn't impact the differentials (if any). Does Backup Exec support COPY_ONLY backups?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-01-15 : 18:04:46
|
| I'm not familiar with the capabilities of Backup Exec so forgive me if this question is obvious. Could you backup the database to disk and then copy the backup file to tape? It seems like the best option. You'd have an online backup available, a near online copy as an option and you wouldn't need to worry about which Differential goes with which Full backup.In re: "Tape Backup Agent is no longer doing it right ... and you don't know until you restore" - If you aren't restoring your backups to a test server on a regular basis, you don't have Disaster Recovery policy; you have a Disaster waiting to happen.=================================================A man is not old until regrets take the place of dreams. - John Barrymore |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-20 : 08:04:17
|
quote:
Originally posted by tkizer
Will this be in addition to the backups to disk? If so, then you'll need to specify a COPY_ONLY backup so that it doesn't impact the differentials (if any). Does Backup Exec support COPY_ONLY backups?
I think this is the answer I need, thanks Tara. I will check if COPY_ONLY Backup is available.quote:
Originally posted by Bustaz Kool
I'm not familiar with the capabilities of Backup Exec so forgive me if this question is obvious. Could you backup the database to disk and then copy the backup file to tape?
This is how we normally backup databases - it gives us a backup file on Disk to restore from (without having to find the correct tape, get it mounted, copied back - and that is assuming that the tape drive is not busy backing up ...), and ease of restoring to TEMP DB if we want to do "How did that happen" queries or selectively copy-back a single table / some rows.However, in disaster recovery we have to restore SQL in some way. If there is no backup on tape then SQL probably won't start (it would have all latest Service Pack EXE / DLLs but no "current" system database files). Worst case we would have to reinstall SQL, ptch it to latest Service Pack, and then restore all DBs from scratch - making sure we got all the file locations the same as they were before (I suppose if we restored Master and MSDB that would then tell us the correct location for the individual DBs)Whereas a copy-backup to tape will bring the whole machine "upright" and then we can restore individual DBs if necessary - might be that the client would actually be happy with a restore from "Last night's tape" and no further individual DB restores needed (although I would then ask myself why we are bothering to back up LOGs etc. to give client almost zero downtime!!)quote:
In re: "Tape Backup Agent is no longer doing it right ... and you don't know until you restore" - If you aren't restoring your backups to a test server on a regular basis, you don't have Disaster Recovery policy; you have a Disaster waiting to happen.
Fair point! I don't think any of our clients make a test restore more often than once a quarter ... Service pack for SQL could easily have happened in between times ...I have been thinking that we should have an OnBoot scheduled task that copies MASTER etc. MDF / LDF to another folder, which would be picked up by normal backup, then we would not be relying on Tape Restore to safely restore the SQL System databases - i.e. if that failed for any reason we could just copy-back the Master etc. DB files. SQL would start up OK but the DB's (which it knew the location for, but files were missing) would then be Suspect or Offline or somesuch, and then we could restore the Databases (from Backup files recovered from Tape).Not the First Choice, but would give us a get-out-of-jail card. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-20 : 13:18:55
|
| I'm not understanding the DR point. If backups are to disk and those files are swept to tape, then why does the database also have to be backed up to tape? Why can't the backup files that originated on disk and were swept to tape be used for the restore?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-25 : 14:33:52
|
| What will I have when I restore after disaster?I want to avoid:Install SQLRestore System DBsRestore each individual DBHaving all DB's swept to tape means:Restore from tapeAll DBs operational (might not be latest data)--Restore from local disk copied of backups any database that needs to point-in-time. Depending on the time of the disaster that might be none - people are only in work for 8 out of 24 hours, so reasonable chance that the disaster is out of office hours.Other than this disaster recovery scenario I want "normal" backups to be to-disk, and then later on from disk to tape.However, that's just how I see it, I'm open to ideas about the best way to get a machine upright after disaster (scenario is that the client has Tapes off site, but not hot-start / warm-start office, so they are starting with a new server, and in an unfamiliar office (or their own office, but the server was destroyed) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-26 : 20:11:23
|
| The part I'm not understanding is why the tape backup is any different than the bak files that were swept to tape. If by tape backup, you mean just backing up all files on disk, then you could end up with corrupted databases as those files were open when the tape software backed them up.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-26 : 20:17:46
|
| The tape backup software has an Agent for SQL so, supposedly!, it will backup all the databases properly, and restore them to their original locations. Sorry, I should have said that at the outset.Thus after a restore all the SQL programs, and all the databases, should be in a "usable" state - even if not a) the most recent backup point and b) probably not a consistent point-in-time across all databases [assuming that that matters] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-01-27 : 14:33:55
|
| I wonder if it works. The client will need to test this scenario.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-30 : 13:25:33
|
| Yes, I am sceptical that it will work, on the day, when they need it. But provided we also have normal SQL Backups taken to disk, and then to tape, I'm not too worried. It will just be a bit longer to get them back upright.These are small clients, no standby office space etc., so it will be "best endeavours" when it happens - but if the tape restore works then that will make us look good because the client will be update in a reasonably short time.I've been busy but I'll check out whether Backup Exec supports COPY_ONLY backups and report back.Thanks for your help. |
|
|
|
|
|
|
|
|
Backup Database direct to Tape for DR