Monior database Backups across environment

Question

Hi

Is there any free tool or scripts that can monitor backups across servers in an environment?

Regards,

Arun

---

Arun SQL DBA

Answer 1

You can use the Central Management Server feature in Management Studio and use the following script to get backup report

http://msdn.microsoft.com/en-us/library/bb934126.aspx

SELECT msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
END AS
backup_type,msdb.dbo.backupset.backup_size,msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,msdb.dbo.backupset.name ASbackupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date,102) >=GETDATE()- 7)
ORDER BY
msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_finish_date desc

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Do you mean if the job is failed you want to get an alert?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

But instance numbers are limited.I have more than 100 instances

---

Arun

Answer 4

Not all jobs..Just backups..I would like to have an alert which triggers once a week which can monitor the status of a list of sql instances

---

Arun

Answer 5

Arun

Then you need to know all jobs that do backups and check out the job's status, otherwise

SELECT 
    backup_finish_date,
    database_creation_date
FROM msdb..backupset
WHERE 
   backup_set_id = (
       SELECT MAX(backup_set_id)
       FROM msdb..backupset
       WHERE database_name = 'TC_IscarData' AND
       [type] = 'D' )

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

Is there anyway i can monitor this from one place? i have more than 100 instances,

Thanks

---

Arun

Answer 7

Yes if you use SS2008  and onwards.

http://msdn.microsoft.com/en-us/library/bb934126.aspx

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 8

CMS have only limited number of instances

---

Arun

Answer 9

CMS have only limited number of instances

---

Arun

what do you mean..???

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 10

How do we monitor it in 2008 R2,Is there any other way than central management server as i have more number of instances

---

Arun

Answer 11

Hi Arun,

I think you could use linked server.

Create Linked Server: http://msdn.microsoft.com/en-us/library/ff772782.aspx .

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 12

Hi Maggie,

its disabled across environment due to policy.Also it seems its impractical to enable it on more than 100 servers having each having more than 3 instances.

I would like to see whether any vbs or ps to just get the update of backup jobs by taking the server names from a text file or so.

Thanks

---

Best Regards, Arun http://whynotsql.blogspot.com/

Answer 13

I have implemented this, its a lot of work you can try.

create a Login and user with read access to backup tables [backupset] tables on all servers, then using OPENROWSET you can query multiple instances and get their result using UNION in a single query, you can even setup mailer for same. the only required is the network connectivity for all instances from that server.

Please remember to mark the replies as answers if they help