Script when database backup fails

Hello,

I would like to have a script , that sends a mail to the dba mail box when the database backup fails . The mail should be sent to the SMTP server.

I have the script which gives the whole output of the backup status but I would like it to change so that it fires only when a backup fails. Please suggest me what to do..

select
bmf.physical_device_name,
RIGHT(bmf.physical_device_name, CHARINDEX('\', REVERSE(bmf.physical_device_name))-1) as physical_device_file,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bs.first_lsn,
bs.last_lsn,
bs.checkpoint_lsn,
bs.database_backup_lsn
into #backup
from
msdb.dbo.backupset bs,
msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and bs.backup_finish_date is not null
AND bs.type = 'D'
AND bs.backup_start_date = (select max(backup_start_date) from msdb.dbo.backupset WHERE type = bs.type and database_name = bs.database_name)
order by bs.database_name, bs.backup_start_date asc

select @.message = @.message + char(13) + Char(13) + 'Backup Status' + Char(13)

DECLARE GetBackup CURSOR FOR
select database_name, backup_finish_date from #backup order by database_name

OPEN GetBackup
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status

WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.message = @.message + @.dbname + ' backup up on ' + @.Status + Char(13)
FETCH NEXT FROM GetBackup INTO @.dbname, @.Status
END
Close GetBackup
Deallocate GetBackup

drop table #backup

print @.message

EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of sqlserver!',
@.type = N'text/html',
@.message = @.message

Thanks

How are you performing the backup? If you are using say SQLAgent jobs then why don't you just include an additional step at the end after the backup step which fires only if the backup fails. This can email the details and you don't even have to use extended SPs etc. I believe that SQLAgent has ways to send emails. Otherwise, you will have to check for status of backup also in the msdb tables since you cannot create triggers on system tables.

Script or Stored Procedure that Collects Information from a Database

Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollinger
If you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger

Script or Stored Procedure that Collects Information from a Database

Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollingerIf you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger

script .bak restore w/ .trn files

I'm the DBA in an environment with approximately 240 identical (structurally) MSSQL 2000 databases in an environment. Each one of these databases are different business units around the country. For each of these DBs, I have my backup schedule set up to do a full backup every friday night, along w/ doing a transactional every 4 hours in between.

Every once in a while (about once every 3 weeks), I get the call to do a restore to pull some piece of data that some end user accidentally deleted or broke. Now most of the time it isn't a big deal, because I can use enterprise mgr to restore to a new DB from the live backup set of the database in question.

But, several of the databases seem to have an issue where (for some reason) my predecessor renamed the logical file names of the data and log files to various goofy things. It doesn't affect their operation, but when I try to do a 'restore from database,' it gives me a bunch of big fat errors. This is also the case when the backup I want to restore something that has been written to tape and has been rolled out of the backup history.

SO... basically for these databases, now I go into Enterprise mgr and first restore the base .bak file to a new database, leaving it marked 'read only' and 'able to restore additional transactions.' I then have to do the same thing with restoring each additional TRN file, all the while making sure I don't forget to set the 'able to restore additional transactions.' Whenever I forget to set that I have to start all over. And honestly, it happens more often than not when I have to restore 5 days out from the .bak file, which means that I have restore 30 trn files on top of the .bak. Not hard, but incredibly tedious.

So here's my idea. I know I can script restores thru QA. I've been looking quite a bit at this MS doc (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#E2AA) and have come up with some good ideas. What I want to eventually do is create a script that you can just paste the .bak file and the list of .trn files. The script basically creates a temp table w/ those file names and cursors thru the restore process. The cursor part is easy for me, I'm just not sure about scripting the trn restore. The MS doc is a bit vague and really only shows the Enterprise Mgr method.

Here's what I've been playing w/ so far for restoring the base .bak file:

restore database restore_dbname --NAME OF NEW DB
from disk = 's:\mssql\backup\user\dbname\dbname_db_20060120184 1.bak' --PATH TO BAK FILE
with
move 'pm65mr1_default_data_OldDBName_Data' --LOGICAL FILE NAME OF ORIGINAL MDF
to 'I:\MSSQL$MSSQLSERVER2\Data\restore_dbname.mdf' --PATH TO NEW MDF
move 'pm65mr1_default_data_OldDBName_Log' to --LOGICAL FILE NAME OF ORIGINAL LDF
'U:\MSSQL$MSSQLSERVER2\Log\restore_dbname.ldf' --PATH TO NEW LDF
partial, recovery

I think I'm pretty close with this, but I'm not really sure how I can stack the trn restores on top of that. Any ideas? I've been putzing around the web and so far haven't been able to come up w/ anything really useful.Here is a template for your log restore:


restore log DBAOnly
from disk = 'D:\MSSQL\Backup\InterchangeBTM\InterchangeBTM'
, with NORECOVERY

-- change to 'with RECOVERY' for final log


Now if you are going to use a cursor to restore your logs, just use NORECOVERY in the cursor loop, then when your fetch <> 0 and you exit and deallocate the cursor, build and execute a line like this:

RESTORE database <DBName> WITH RECOVERY

that will finish the roll forward and rollback and make the database available.|||awesome! thanks... I figured it would be something simple like that. I'm gonna have a go at it later today and see if I can get a manual DB restored.