Last weekend was the 2015 Rhinelander Hodag Muskie Challenge, the first fishing tournament I’ve ever competed in. It was a cool experience, and although we didn’t record a fish, I plan to keep trying. My team included myself and my brother-in-law, Jason. We came up two days early to prefish the lakes and try to identify a pattern that would work once the tournament started. Jason caught two fish during the tournament, a 42″ on Friday and a huge 48″ on Thursday. The bigger one was an enormous fish, and neither words nor the picture below really do it justice.
It was one of those things where you just had to be their understand the real size of the fish and the excitement of the moment. Unfortunately, once the tournament started on Friday morning we only managed to get follows, no biters. The winning team ended up landing three fish, including two legal (40″+) ones. Congrats to them, that is a great accomplishment. It was cool to see how the weather really affected the fishing. We were faced with high pressure on Saturday and Sunday, and by Sunday it really appeared to have slowed down the fish. There were about 30 fish caught on Saturday during the 10 hours of fishing and only 6 on Sunday during the 5 hours of fishing. 36 fish between 80 teams isn’t that many, and it helps to realize that even some of the best muskie fishermen out there struggle under tough conditions.
I recently set up a HA passive node for our production 2008 R2 SQL Server. It is recommended by Microsoft to only mirror at most 10 databases per server because the mirror process takes up processor threads. I mirrored our 10 most important databases, but in the event of an outage we would probably want some of the other databases to be current on the backup server as well. So I created a script to create the restore process for any other databases. This script finds the latest full backup and each transaction log backup after that full backup. It is important to note that each restore except the last should be WITH NORECOVERY while the last should be WITH RECOVERY. I prefer very frequent TLOG backups for minimum data loss, so the potentially large number of transaction logs to restore makes the script necessary to get the latest version of the databases without spending a ton of time setting up the restores. The script will place all the restore statements in the correct order in the RestoreStatements field, and the database_name field is just used for sorting and can be disregarded. This script also requires that you restore msdb to the mirror and will only generate backup statements for tlog backups taken before the last msdb backup. It will also work best if the backups are stored on a network location that is accessible from both the principal and mirrored servers.
WITH latestfullbackups
AS ( SELECT bu.database_name ,
bu.backup_set_id ,
bmf.physical_device_name ,
bu.backup_start_date ,
ROW_NUMBER() OVER ( PARTITION BY bu.database_name ORDER BY bu.backup_set_id DESC ) rn
FROM msdb.dbo.backupset bu
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bu.media_set_id
WHERE bu.database_name NOT IN ( 'master', 'msdb', 'model','anymirroreddbs')
AND bmf.device_type 7
AND bu.TYPE = 'D'
),
alltlogsafterlastfull
AS ( SELECT bu.database_name ,
bu.backup_set_id ,
bmf.physical_device_name ,
ROW_NUMBER() OVER ( PARTITION BY bu.database_name ORDER BY bu.backup_set_id DESC ) rn
FROM msdb.dbo.backupset bu
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bu.media_set_id
INNER JOIN latestfullbackups ON latestfullbackups.database_name = bu.database_name
WHERE bmf.device_type 7
AND bu.TYPE = 'L'
AND bu.backup_start_date > latestfullbackups.backup_start_date
AND latestfullbackups.rn = 1
)
--Latest full backup
SELECT 'RESTORE DATABASE [' + latestfullbackups.database_name
+ '] FROM DISK = N''' + latestfullbackups.physical_device_name
+ ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
+ CHAR(10) + 'GO' AS RestoreStatements ,
latestfullbackups.database_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
INNER JOIN latestfullbackups ON latestfullbackups.backup_set_id = bs.backup_set_id
AND latestfullbackups.database_name = bs.database_name
AND latestfullbackups.rn = 1
UNION
--All tlog backups after latest full except the last one
SELECT 'RESTORE LOG [' + alltlogsafterlastfull.database_name
+ '] FROM DISK = N''' + alltlogsafterlastfull.physical_device_name
+ ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
+ CHAR(10) + 'GO' ,
alltlogsafterlastfull.database_name
FROM alltlogsafterlastfull
WHERE alltlogsafterlastfull.rn 1
UNION
--Last tlog backup - recover the database
SELECT 'RESTORE LOG [' + alltlogsafterlastfull.database_name
+ '] FROM DISK = N''' + alltlogsafterlastfull.physical_device_name
+ ''' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE, STATS = 5;'
+ CHAR(10) + 'GO' ,
alltlogsafterlastfull.database_name
FROM alltlogsafterlastfull
WHERE alltlogsafterlastfull.rn = 1
ORDER BY 2 ,
1
The Fisherman DBA 