-- check for missing full backup or broken transaction log chain
		IF @bkpType <> 'FULL'
		BEGIN
			SELECT @missingFull =
				CASE 
					WHEN last_log_backup_lsn IS NULL THEN 1 
					ELSE 0 
				END
			FROM master.sys.database_recovery_status 
			WHERE database_id = DB_ID(@dbName)

			-- Database could be in SIMPLE recovery model, so above could show a broken
			-- transaction log chain
			IF @missingFull = 1
				SELECT @missingFull = 
					CASE
						WHEN backup_date IS NULL THEN 1
						WHEN backup_date < restore_date THEN 1
						WHEN backup_date < create_date THEN 1
						ELSE 0
					END
				FROM
				(
					SELECT
						create_date, 
						restore_date = 
							(
								SELECT MAX(restore_date) AS restore_date
								FROM msdb.dbo.restorehistory
								WHERE destination_database_name = @dbName
							),
						backup_date = 
							(
								SELECT MAX(backup_start_date) AS backup_date
								FROM msdb.dbo.backupset
								WHERE database_name = @dbName AND type = 'D' --full backup
							)
					FROM master.sys.databases
					WHERE name = @dbName
				) t
		END

quote:

---

Originally posted by Kristen

That's pretty cool Tara

---