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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Primary Backup Disk . . . or Tape?

Author  Topic 

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-04 : 04:37:11
Need a few quick responses here if possible, for a meeting later on today.

We are in the process of putting in place a Dual Site Dispersed SQL Server Cluster, with SAN Storage (With CA Replication) at each site. Each SAN Will be attached to a Tape Backup Library device. Now I have always Backed my SQL Server DB's to Disk using
BACKUP DATABASE dbname DISK= . . .
and then had the Backups copied to tape on a daily basis for Archive purposes. We have agreed with the Supplier (HP) to use HP Data protector as the backup software. HP is insisting on us using DP to backup the databases online, even though I am sure that best practice is to have disk backups, and I am holding out for this.

The current Backup Strategy is to Backup the DB's morning and evening with an incremental backup at lunchtime. The Tranlogs are backed up to disk every 15 minutes uring the working day and once an hour during the night. My personal view is that this just cannot be implemented using Tape Backups alone.

So quick straw poll how many DBA's would work in an environment where the only backups (DB and Tran Log) were on tape?

Thanks

--
Regards
Tony The DBA

Westley
Posting Yak Master

229 Posts

Posted - 2006-12-04 : 05:17:53
Hi Tony,
Since you are having DIFF backup, why not put those to TAPE as well? I do not have any DIFF backup running on any of my box, so I only have FULL and TLog backup to TAPE. The only good use of a DIFF is for restore, and if you are not backing it up to TAPE, then you cannot use that for restore when needed, so why bother take it at the first place?
Go to Top of Page

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-04 : 06:30:11
Hi Westley,

I have no problems with the Disk backups being copied to tape for Archive purposes, and in fact that is what we are doing on all our current SQL Servers. The problem I have is relying on Data protector and tape backups as my ONLY form of backup.

What Happens Currently

06:30 Full Database Backup taken (Copied to 'spare' SQL Server)
06:45- Log Backups Every 15 Minutes (Copied to Spare Server)
13:00 Incremental Backup Taken (Copied to Spare Server)
19:00 Full Database Backup taken (Copied to 'spare' SQL Server)
20:00 Log Backups (Hourly Intervals Copy To Spare Server)
21:00 Backup Directory Archived to Tape

What the Supplier is proposing is to do away with the disk backup and back it up directly to tape with the Data Protector Plug In for SQL Server . . . Yet More software for me to learn . . . Whats wrong with BACKUP DATABSE . . . DISK = . . . AND RESTORE DATABASE . . .? How much simpler does it get?


--
Regards
Tony The DBA
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-04 : 07:20:23
"I am sure that best practice is to have disk backups, and I am holding out for this."

I don't know anything about HP Data protector backup software. Maybe it is brilliant. However, this forum is littered with questions from folk who have used direct-to-tape backup routines and had problems restoring etc.

I can see a case for it if you have insufficient disk space to make a backup to disk (although in that circumstance I would advocate buying more disks first

0) Tape backup software integrated directly into a 3rd party application (like SQL Server) will always be one step behind. What happens if a HotFix comes out and the tape software stops working until the Tape Vendor issues a fix? Will you even know that the tape software is no longer reliable in such circumstances?

1a) How easily can you get your data back off tape? Do you have to find the SysAdmin, and the tape, and wait for something else the tape drive is being used for to finish?

1b) And what about getting the tape that the FULL is on, then the tape for the DIFF and then the tape(s) for all the TLogs - how much of a hassle / downtime is that?

2) Is it quicker to restore off Tape than from Disk? (I doubt it, but if the Tape is on a separate channel its possible - but then the Disk Channel you backup to should be separate from Data channel anyway)

3) How easily can you change your backup processes? For example, you are about to rollout a patch to your system. For comfort you would like to take a DIFF backup. Is that going to be easy to do, on a one-off basis, to tape? (See 1A above for availability etc.)

OK so maybe you can just do a DIFF backup to disk in such circumstances (although I hate having two different paths to backup solutions, likely to be a Snafu because of it sooner-or-later)

But what about this scenario. You are about to do some "risky procedure". As part of that you are going to Shrink/Reindex/UpdateStats/etc. and that task is going to generate a lot of log. So you might need to do:

HeavyTransactionTask
TLog backup (to dump the abnormally huge TLog)
Diff backup (so you can quickly resume from here)
Some "risky" tasks
(TLog backups will occur at normal intervals)

So how easily can you do the one-off TLog tape backup?

You absolutely must not do the TLog backups to Disk as you will have broken the recovery chain if it is not recorded on to disk.

And so on ....

... Just say "no" !

Kristen
Go to Top of Page

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-04 : 07:56:07
Thanks Kristen, Just what I was looking for
quote:
Kristen

"I am sure that best practice is to have disk backups, and I am holding out for this."

this forum is littered with questions from folk who have used direct-to-tape backup routines and had problems restoring etc.

I know
quote:
You absolutely must not do the TLog backups to Disk as you will have broken the recovery chain if it is not recorded on to disk.
I take it that is in the Scenario you outlined, and not as a general principle?
quote:
... Just say "no" !
I intend to

--
Regards
Tony The DBA
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-04 : 08:16:56
"I take it that is in the Scenario you outlined, and not as a general principle?"

There should be no circumstances where someone takes a TLog backup that "breaks the chain". That includes taking a Tlog backup to their C: drive (factitious example, of course) such that the DBA can't find it when a RESTORE is needed. Same thing for a non-standard folder.

We provide our Developers etc. with an SProc to make a backup:

usp_Backup @Databasename, @Type -- Type: 1=Full, 2=Diff, 3=TLog

which automatically puts the file in the appropriate folder for ease of housekeeping and restore, and sets an appropriate retention period (there is an optional parameter for that - e.g. for a FULL backup that may be a large file and only needs to be stored for a day until the current operation is deemed "safe" [the scheduled FULL backup and subsequent TLog backups will do for a disaster recovery, the current one-off Full backup is just for speed of recovery if it becomes necessary)

Additional point for the above list:

4) All the existing disaster recovery documentation will need to be rewritten and retested (or fire-drills if that is all you have). IME that would be a VERY expensive exercise to change.

Kristen
Go to Top of Page

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-04 : 08:44:16
There should be no circumstances where someone takes a TLog backup that "breaks the chain".

Ahhh! Understood, of course not, One of the first things I did when I moved into the post was to standardise the Backup proceedure . . . That and remove sysadmin, dbo, dbadmin from anyone who didn't need it

--
Regards
Tony The DBA
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 01:08:55
(Not sure this makes a significant difference, but I'll mention it anyway)

5) How are you going to test restore?

You need to prove that all backups are readable. Thus they need restoring (preferably to a different server) and DBCC CHECKDB'ing. Better to do that before copying to tape if possible, but in parallel will do.

Then test-restoring from tape only needs the tape system to validate that the Checksum is valid.

If you backup direct from SQL-to-tape you will have to test restore everything from tape direct into a different database. I expect the tape unit is busy enough without having that extra burden, and it may be harder to route from the tape backup unit to your test SQL Servers box (whereas test restoring a disk file only involves solving "How do I copy this XXX.BAK file from Server_A to Server_B")

Kristen
Go to Top of Page

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-05 : 07:41:32
Well, I won that argument of course when 'the management' get back and we have our weekly project meeting tomorrow I can see me having to justify it all again as they purchased the SQL integration licences for data protector. Still I can ask the question "Does it treat them as files or a database"

--
Regards
Tony The DBA
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-05 : 09:04:49
Tony, I know you've already had your meeting, but the one thing I thought was missing (EDIT: ok, not 100% fair, Kristen does dance around this issue a bit) from this discussion was SLA (Service Level Agreement). On disaster, how much time will the business give you to do your restores and will you be able to meet that SLA both FROM DISK and FROM TAPE?

As a general rule, whenever anyone mentions backups, the first thing I want to know is the SLA for restoral.

Jay
to here knows when
Go to Top of Page

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-05 : 09:14:57
Jay,

The whole point of us moving to a dual site clusterd infrastructure means that if disaster strikes (Catastrophic), we should be able to fail over to the second site with few problems. I suspect the harder issue to resolve is when it is not a catastrophic event, In general what usually happens is the overnight batch run (Yes Vendor has migrated a mainframe system to SQL Server without re-engineering the application!) fails. What usually happens is my team get a call at 8am asking us to restore the previous nights backup to test so that the reports can be run (for things like court summonses etc). So far we have been lucky in that the job failures haven't impacted the posting of cash to the live system requiring us to re run the overnights in a live environment. What I guese I'm trying to say is that our users don't want to tolerate ANY downtime during the day, although they will accept up to an hour or so. As far as an SLA goes . . . . . management are still negotiating that one, and I suspect that recovery timescales will be down to 'best endevours'

--
Regards
Tony The DBA
Go to Top of Page
   

- Advertisement -