Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Who administers the SQL Backups in your company Expand / Collapse
Author
Message
Posted Saturday, July 27, 2013 3:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:32 AM
Points: 155, Visits: 184
Recently, our company has decided to go with CommVault as an enterprise backup and archiving solution. As a result, a bit of a riff has occurred between the DBA team and the Storage team. The Storage team believes they should be in charge of doing backups and restores.

What I'm looking for here is how other company who use an enterprise backup and archiving solution? Does the DBA team still handle backup and restores or is this a function handled by a separate team?

Also any feedback or discussion specifically towards the CommVault software and how it performs against products such as Red Gate SQL Backups would be appreciated.

thanks
Post #1478314
Posted Saturday, July 27, 2013 7:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
I guess I'd have to say the horse is already out of the barn on which backup software to use. That's a battle I wouldn't bother fighting.

So far as to who the backups and restores are done by, we have a system at work that has been working wonderfully. As the DBA, I backup the systems to disk. The "storage team" takes care of giving me the disk space I need (yes, I justify it... it's part of being on a team) and they take care of copying my backups to tape, tape rotation, etc. It works out really great because I frequently have to make ad hoc backups because of upcoming changes and other special requests plus, simply because I know much more about SQL Server, backups, and restores than the rest of the storage team combined (let's see them do a "piece-meal partitioned restore"... they don't even know such a thing exists), I'm able to create huge savings insofar as backup time, restore time, disk space, tape space, tape backup time, etc, etc, etc. Of course, you have to consider that the software they bought (CommVault) might also be able to do all of that, as well

So far as the growing rift that you speak of, that's a damned shame. My apologies to both groups but that's starting to sound a little like empire building by both groups. Both groups need to drop any personal bias and figure out what's the best for the company (which means what's best to protect the data) and just because there might be more of them or the fact that you're the DBA doesn't necessarily mean that's best for the company. In my humble opinion, "best for the company" is going to be something like what I described in my second paragraph above where both teams do key parts of the job. The DBA is left to figure out which backup/restore methodology is best at any given moment and the "storage team" makes damned sure that what the DBA saves to disk is correctly backed up to tape. If a restore is necessary from any time in the last 2 days, the DBA should be the one to do it and from disk. Anything further out than 2 days will need a coordinated effort from both teams to retrieve the proper tape(s) and restore them to the database.

I also believe that you, as a DBA, will probably know what's best to protect the data and what's best for the company when it comes to backups and restores. That, notwithstanding, we DBAs sometimes don't get things done our way. You have 3 choices if they decide to go in a manner you don't agree with...

1. Look at the bright side of having one less thing to do and throw your arms up in the air and point at the "storage team" if an emergency restore doesn't work. I, of course, don't recommend that method.

2. Find a different company that will let you be large and in-charge when it comes to backups and restores Of course, you'd better not fail... ever.

3. Be a team player. You gave it your best shot with your recommendations and they didn't follow those recommendations. If they decide the "storage team" is going to be totally responsible for backups and restores, make sure they understand that they need to practice emergency recovery from off-site stored tape at least once a month and that everyone has worked out how to identify which databases need Point-in-Time (PIT) recoverability, which databases need "Read Only" recoverability, and everything in between including "split" databases where some file-groups are "Read Only" and some file-groups need PIT recoverability. The company should also identify "critical" file groups for Piece-Meal restores (to get "back in business") and file-groups that can be "On-Line Restored" after the critical parts of the system are recovered.

I'm sure that neither team is trying to turn things into a rift but sometimes the desire to do a good and proper job causes such rifts. You ARE the DBA... Be "the MAN!" that makes it work no matter what...

... and, if you elect option 3 above and if you're like me, you'll have a "secret backup" system squirreled away (even if it takes 2 days to write it to a portable USB drive) even if you have to buy the bloody drive yourself just in case something goes wrong... goes wrong... goes wrong... (Whack! Bang! Smack! Ugh! Hurl!!!) goes wrong.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478327
Posted Monday, July 29, 2013 6:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 961, Visits: 4,991
A couple months back, I changed jobs...
My new employer uses (and has been using) CommVault for several years to handle all the backups, and here's what I've seen / discovered / realized:

1. Commvault does support and can do "native" SQL backups. When such a backup is running, the actual SQL Query text is "backup database [whatever] etc, etc"
2. It's either not possible, or a PITA to do a "ad-hoc" backup in CommVault. Refreshing my QA environment I just take a copy-only full backup in SSMS, it's less trouble.
3. Restores from Commvault aren't too bad, although the wording in the message boxes can lead you to think it's not going to work like you expect because you haven't had the chance to specify the restore location yet so it looks like it's going to restore into Production and did you maybe miss an option? Nope, you select what to restore, then hit OK, THEN you can specify the where (another server?,) name (yes, you can rename the DB,) the point-in-time, and whether to leave the DB in recovery or not.
4. Commvault requires JAVA. Deal with it...

All told, so far it's not as bad as I thought. It will definately be to your advantage to work with, rather than fight with, the Commvault Admin. My CV Admin and I get along, she's got some limited knowledge of SQL, and I do my best to explain *why* I want the backups done in a particular way, while still trying to also work within the limitations she has (storage, storage, storage. The employer wants even a lot of the desktops backed up nightly) The backups are kept on local media (I think a SAN array) for 30-days, then they drop to tape and are moved off-site.

So, nice part from my point of view, I can pawn off backup problems on someone else (unless it's my server causing the problems,) downside is, that someone else may not be able to resolve the issue as quickly as I would prefer. (CV here was down for almost a week because of a problem with the CV library. Admin worked on it for 16-20hrs at a time with CV support before getting it fixed. Not a fun week, worrying that I'd need a restore of recent data, with no backups. Even more annoying, myself and the Oracle admin thought we had fixed an on-going Oracle backup issue to CV, and couldn't test until the library problem in CV was fixed...)

So, similar to what Jeff suggested in option 3, rather than fighting the storage team, work with them. Even more, get on the CV Admins good side, explain that you would like at least enough access to CV to be able to add / remove DBs to the backup clients as needed, run restores of your DBs, etc, without having to ask / wait for the CV Admin / Storage team.

So far, that's working for me.
Jason
Post #1478550
Posted Tuesday, July 30, 2013 7:58 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 15,554, Visits: 27,925
I'm cool with anyone handling backups. As long as they get done and (the kicker) as long as we can successfully restore them in a timely manner. If it takes hours to track down a backup (it's happened to me) or I don't have access to the backup software at 3AM (that's happened) or the "backups" work great but the database either won't restore (happened) or restore corrupted (happened), then we are RAPIDLY renegotiating how backups are done (probably following Jeff's model). I don't have a territory to defend. I have processes and protections for the companies information that I have to support. How I do it just doesn't matter. That it gets done is what matters.

With any 3rd party backup utility (and that includes Red Gate), I would put it through a pretty thorough set of backups and restores, as many scenario's as you've run into recently. Be sure. That's all.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1479011
Posted Sunday, August 11, 2013 1:25 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:41 PM
Points: 522, Visits: 234
We have IBM team handling servers and backups
Post #1483104
Posted Monday, August 12, 2013 3:00 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:11 AM
Points: 3,244, Visits: 529
With around 200 servers and lots of databases, se (the DBAs) do the backups and restores of the databases. The Server group backups up our files to tape and restores the file when we need it.

-SQLBill



Post #1483473
Posted Monday, August 12, 2013 10:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Snigdha Vartak (8/11/2013)
We have IBM team handling servers and backups


Who is handling test restores? Are they even being done?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1483552
Posted Tuesday, August 13, 2013 6:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 905, Visits: 1,418
Our responsibilities are nearly identical to Jeff's. The DBA's perform the backups to disk and the storage team is responsible for copying the files to and from tape. The DBA's also are responsible for restoring the databases.

In testing the process, we randomly pick a day(s) for the storage team to copy files off of tape back to disk for us to restore. This helps us validate the tape backup process and our backup/restore process.

Everyone is fine with their specific roles in this regard as they see it's best to let each specialist perform their specific work.

Steve




Post #1483705
Posted Tuesday, August 13, 2013 9:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:10 AM
Points: 861, Visits: 2,360
We also share responsibility:
DBA's handle Database<->Disk and disk retention/aging, automated and ad-hoc and PITR.

Backup team handle Disk<->Backup software (disk and tape), automated and ad-hoc, and tape retention/aging.

DBA's get requests, and if the backups files are no longer on disk, DBA's send the Backup team a request to get the files from the backup software.

If a non-DBA group wants to handle backups and restores, and talk to them about what happens if someone kicks off a restore they shouldn't have and causes data loss/production impact. Be mindful DBA's can cause these same problems, too. Also, have them test ad-hoc Point in Time Recovery to a different database name (i.e. "I deleted X by accident, can you get it back")
Post #1483795
Posted Wednesday, August 21, 2013 6:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:10 PM
Points: 33,095, Visits: 15,202
Nadrek (8/13/2013)
We also share responsibility:
DBA's handle Database<->Disk and disk retention/aging, automated and ad-hoc and PITR.

Backup team handle Disk<->Backup software (disk and tape), automated and ad-hoc, and tape retention/aging.

DBA's get requests, and if the backups files are no longer on disk, DBA's send the Backup team a request to get the files from the backup software.

If a non-DBA group wants to handle backups and restores, and talk to them about what happens if someone kicks off a restore they shouldn't have and causes data loss/production impact. Be mindful DBA's can cause these same problems, too. Also, have them test ad-hoc Point in Time Recovery to a different database name (i.e. "I deleted X by accident, can you get it back")


This is how I've usually done this. DBAs manage the db and get files to disk. The backup team takes it from there.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1487011
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse