Insert Triggers always run

Who'da thunk it.
I have an insert trigger that does some data validation. no big deal. The validation that it's performing is this:

if not exists (
select 1
from <My other Database>
where column =(select columndata from inserted)
) raiserror (whatever)


looks simple enough, but there are times when I will have an insert statement that isn't inserting any rows. Apparently triggers get fired whether or not there is any data actually being inserted. They get fired anytime there is an insert statement. So, I had to modify the code to have the following

if (exists (select 1 from inserted) begin
if not exists (
select 1
from <My other Database>
where column =(select columndata from inserted)
) raiserror (whatever)
end


Happy SQLing.. (;

Transaction Log Backup script

So, Like a good boy, I have my SQL Server databases in full recovery model. Because of this, I also employ an aggressive backup strategy. The databases get a full backup weekly to tape, differential backup daily to tape, and transaction log backup to disk every 15 minutes.
We have a few robust SQL Server boxes each of which house lots of databases. I want the same backup strategy applied to each one. For the tape backups, we're employing HP OpenView Storage DataProtector which has a nice interface for doing just such a task.
There isn't a nice little tool (that I've found) in SQL Server to do this however. So, I crafted my own.

I originally had the transaction log files all going to the same file for a particular database. Since transaction logs are based on the last full or differential backup, this made the restores really complicated. I wanted to make sure that there was only 1 transaction log backup file per differential or full backup. So I baked that into the script as well.

Now, since I'm backing up the transaction logs to disk and I want those stored onto tape, there is a job in our tape backup that will also backup all the Transaction Log Backup Files. Finally, I wanted to delete the transaction log backup file has been archived to tape.

Hopefully all this makes sense. And now... the code:



/*
Transaction Log Maintenance Script.sql
Rob Bittner
August 5, 2009

Script to back up all the transaction logs on a database and warn if there
are file backups instead of tape backups

1) For Each Database:
- If the last backup was not a tape backup email the help desk.
- Otherwise, back up the transaction log.
2) Go through all the existing Transaction Log Backup files and delete those
that have been backed up to tape through the nightly file backup.

Modifications:

November 18, 2009 (RB) : Changed Email to go to the help desk instead of me

*/
declare cur cursor for
select name
from sys.databases
where state_desc='online' and recovery_model_desc='full' and name <> 'model'
open cur
declare @backuppath varchar(255)
declare @name varchar(255)
declare @filename varchar(255)
declare @lastbackupdate datetime
set @backuppath='e:\sqldata\Backup\'

fetch next from cur into @name

while @@fetch_status = 0 begin
print 'Backing up ' + @name

select @lastbackupdate=max(backup_start_date) from msdb.dbo.backupset where type in ('i','d') and database_name=@name
set @filename= @backuppath + @name + ' Transaction Log Backup ' + replace(convert(varchar,@lastbackupdate,120),':','') + '.bak'

if not exists ( -- see if there was no tape backup the last time is was backed up
select 1
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
and device_type = 7) begin
if not exists (
select 1 -- See if there already was a backup attempt (to prevent multiple emails)
from msdb..sysjobhistory
where job_id = (select job_id from msdb..sysjobs where name='Transaction Log Maintenance')
and step_id=0 -- job finished
and CONVERT -- date started
( DATETIME, RTRIM(run_date) ) +
( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 ) / 216e4
> @lastbackupdate
) begin


declare @body varchar(max)
declare @path varchar(max)
declare @subject varchar(255)

select @path=bmf.physical_device_name
from msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
where database_name=@name and backup_start_date=@lastbackupdate
order by backup_start_date

set @subject='Transaction log backup failed on ' + @@servername

set @body=
'<html><head><style type="text/css">body{font-family: calibri;} h1,h2,h3 { margin:0}</style></head>'+
'<body>'+
'<h1>Error when backing up transaction log</h1>' +
'<h2>Most recent backup on the database is a file backup</h2><hr />' +
'<h3>Server: ' + @@servername + '</h3>' +
'<h3>Last Backup Path: ' + @path + '</h3>' +
'<h3>Last Backup Time: ' + convert(varchar,@lastbackupdate,109) + '</h3>' +
'<p style="font-weight:bold">To preserve the integrity of our database backup process, a full backup of ' + @name + ' must be performed utilizing Dataprotector ASAP!</p>'+
'<h2>Failure to back up this database to tape will cause the transaction log to continue to grow</h2>' +
'<p> - SQL Server Database Mailer</p>' +
'</body>' +
'</html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients='email address here',
@subject = @subject,
@body = @body,
@body_format = 'HTML',
@profile_name='HelpDesk' ;
end -- Only email once if statement
end else begin
backup log @name to disk=@filename
end


fetch next from cur into @name
end

close cur

deallocate cur

declare @deletestatement varchar(255)
set @deletestatement = 'del ' + @backuppath + '* /a-a /q'
print @deletestatement
EXEC master..xp_cmdshell @deletestatement



Custom LINQ orderby expression

LINQ expressions are so slick that I don't know how I lived without them for so long. I had a little issue with a custom orderby, however, and found a pretty neat solution.

Here's the objects:


Festival{ String FestivalName, IEnumerable Events ...}
Event { String Type, DateTime Date ...}


A festival has many different Events: Start, Finish, Performances, etc. I want a list of festivals in order according to the start date. I was going to expose a specific property in Festival that returned the date of the Start event if it existed, but I wanted to generalize it. I knew about FirstOrDefault and came up with the following:


List Festival=
(from d in FestivalList
orderby s.Events.FirstOrDefault(q => q.Type=="Start").Date
select d)


I ended up getting a NullReferenceException because not all festivals were properly entered and some didn't have Start events. I decided that these should show up first. So I had to use a different approach with the orderby by using another inline LINQ query.


List Festival=
(from d in FestivalList
orderby
(from e in s.Events
where e.Type == "Start"
select e
).DefaultIfEmpty(new Event()).First().Date
select d)


The DefaultIfEmpty specifies how we should handle when there isn't an Event. In this case, I'm returning an empty event which will return DateTime.MinValue (This is part of the Event.Date definition). Slicker than snot.