Restore latest backup and all transaction logs

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.

Jason's 48

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s