SQL Server 2000 backup report

Question

Hi All,

Can anyone provide me with a script that fetches the backup status for all the databases in a server and send a mail having all the required fields regarding the last backup date, database name

But I want this in SQL Server 2000

Answer 1

select d.name as db_name
  , ( select top 1 backup_start_date 
        from msdb.[dbo].[backupset] 
        where database_name = d.name
        order by backup_start_date desc 
    ) as last_backup_start_date
from sysdatabases d

---

http://www.t-sql.ru

Answer 2

SQL Mail:

http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html

http://support.microsoft.com/kb/870675/en-us

---

http://www.t-sql.ru

Answer 3

Hi Alexey,

Thank you for your reply, but I was looking for a script which fetches backup report for unlimited servers (we need to specify servers for which we need backup report) at a time and sends the mail.

I was able to write the scpirt thank you...

1. I used temp table to note all the servers name
2. used commad shell scipting to fetch backup report from all the servers and store that in a centralized table in one of the server
3. used SSIS to fetch the details from centalized table and send a mail in CSV format... :)

---

Ananth Kamath