SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Backup Database direct to Tape for DR
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/15/2014 :  05:02:43  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 01/15/2014 :  13:16:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 01/15/2014 :  18:04:46  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2014 :  08:04:17  Show Profile  Reply with Quote
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.

Edited by - Kristen on 01/20/2014 11:57:32
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/20/2014 :  13:18:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/25/2014 :  14:33:52  Show Profile  Reply with Quote
What will I have when I restore after disaster?

I want to avoid:

Install SQL
Restore System DBs
Restore each individual DB

Having all DB's swept to tape means:

Restore from tape
All 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)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/26/2014 :  20:11:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/26/2014 :  20:17:46  Show Profile  Reply with Quote
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]
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/27/2014 :  14:33:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
I wonder if it works. The client will need to test this scenario.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/30/2014 :  13:25:33  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000