August 11, 2008 at 1:57 pm
I am in the unenviable position of having just been outsourced to another company ... so my whole DBA universe is in turmoil
Background:- I am the team leader for the SQL Server DBA's in a large company. responsible for an infrastructure of about 600 SQL Servers (Mirroring, Clustering, Log Shipping e.t.c). We came to restore some V High profile databases from Tape on friday .. only to be told "Oh the backup failed that day" ................... Sounds familiar I hear you say
Resolution:- I want to monitor the backups myself and put emergency steps in place to ensure that there is no data loss in the result of a failure of a tape backup.
We use Legato Networker and I would like to interrogate the legato networker database (from within SQL server) to check that the daily backups have indeed run .. and give me that warm comfortable .. I can recover feeling.
I know that I should not need to monitor the work that is done by a sperate company -- but I need to ensure that I have visibility of any potential impending disaster.
Questions:-
1) How can I get a report from Legato (I have access to the command line tools)
2) I need to list the failed backups using the above ... is that possible?
3) We often get legato locking files due to overrunning backups .. can this be monitored using the above?
Any information that would help solve the above would be appreciated
Note : Installing the Legato SQL Client is not an option
August 11, 2008 at 2:41 pm
I would not take this approach. You are getting dangerously close to making yourself responsible for a tape backup failing - and you may simply have no control over it.
Rather than doing this, you should request you be notified by their server group in the event of a backup failure on the SQL Servers. Let them figure out how to ensure you get the proper notification.
As it is, they either were notified of the backup failure and ignored it, or they were unaware that something bad had happened. Either way, ensuring their server group has a process in-place to help deal with the issue is a better approach for you and the company.
If you haven't already, don't take the "what's wrong with you guys" direction when asking for this. Ask for it in a way that assumes the server group monitors the processes and understands the importance - if they do not, asking for it will prompt them to begin to understand that it is a problem without making them look bad. As a DBA, your job will be easier if the server group likes you.
August 11, 2008 at 11:08 pm
I'd agree with Michael. You want notification of failed backups from that group. If they don't want to let you know, bring up the issues and show it's needed so you can make another backup to be safe.
August 12, 2008 at 9:49 am
Thanks for the replies,
The issue is more one of needing to know if there is a backup failure so that we can take action fast.. the Issue is with the storage team (whose stock answer is .. we are well within the industry norm)
The server teams and My team work extreemly closely together and have a good raport, however they are in the same boat.
Although I agree that they should be providing the information (and it has been requested on more than one occasion .. and escalated) .. We are still in the position where we need visibility of failures, more to keep track of SLA than anything else
Cheers
Andy
August 12, 2008 at 2:31 pm
I don't know much about legato but is this a tape backup taking flat files or an app that backs up SQL DB's directly to tape, if it is the latter does a record get written to msdb..backupset if so would this give you the info you require. There are plenty of scripts that details this info on this site, 600 instances might get a little tedious but there is also a bunch of SSIS examples that use lookup table to do a dynamic connection so you could just have on SSIS package collecting from all you instances. We monitor 90 + instances this way so we know which backups have not occured.
Andrew
August 12, 2008 at 2:54 pm
You can check yourself to see if backups were run. All backups are recorded in the msdb database.
This query will get you started:
select
a.name,
bs.*,
bmf.*
from
master.dbo.sysdatabases a
left outer join
msdb.dbo.backupset as bs (nolock)
on a.name = bs.database_name
left outer join
msdb.dbo.backupmediafamily as bmf (nolock)
on bmf.media_set_id = bs.media_set_id
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply