Script to Restore Latest Full Backup with Move Files

Last weekend I met a couple buddies for some early morning muskie fishing at Pewaukee Lake. We arrived at about 6:15 AM, and there was a steady but not hard rain falling. It was dead calm and about 70 degrees out. I consider this fairly pleasant weather to fish in. I’d rather be rained on than baked by the sun on a hot, humid, windless day. In the first half hour of our trip we managed one follow-up, a 33 – 35 inch muskie that followed to the boat before turning around. Then at about 6:45 AM, we had a durastic weather change. The wind picked up to 20 – 25 mph and the temperature dropped about 15 degrees. The changed happened so abruptly we could actually see it blowing across the lake. I’ve never seen a weather change like that before. Fishing under a cold front is tough, and we didn’t see any more fish for the rest of the day.
As a DBA, using scripts that create TSQL is a great way to make large changes quickly. In practicing my database migration, I ran into a situation where, after restoring the system databases on the new server, all other databases are left in a Restore Pending status. This is because the master database has records of the user databases, but they haven’t been restored or moved yet. Since this is just practice, I don’t actually want to move the user database files, so instead I’ll just restore the latest full backup of each. However, I have several user databases to restore, and I don’t want to write out each script or run through the RESTORE wizard for each one. So I created the following tsql script that will create the restore script for each user database. One complication was that the folder structure on the new server was different than on the old server. I used the first common table expression to get all the full backups of each user database. I use the ROW_NUMBER window function to make sure I get the latest backup first. I used the second table expression to create the MOVE part of the script for each database file. I had to put them in a common delimited list (as explained here).

WITH    latestbackups
          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
               WHERE    bu.database_name NOT IN ( 'master', 'msdb', 'model' )
                        AND bmf.device_type <> 7
                        AND bu.TYPE = 'D'
          AS ( SELECT   database_id ,
                myText = STUFF(( SELECT ', ' + name
                                FROM   ( SELECT DISTINCT
                                                    WHEN TYPE = 1
                                                    THEN ' MOVE N''' + name + ''' TO N''E:\LogFiles\' + RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name), 1) - 1) + ''''
                                                    ELSE ' MOVE N''' + name + ''' TO N''D:\DataFiles\' + RIGHT(physical_name, CHARINDEX('\', REVERSE(physical_name), 1) - 1) + ''''
                                                END name
                                        FROM      sys.master_files b
                                        WHERE     a.database_id = b.database_id
                                    ) x
                                XML PATH('')
                                ), 1, 2, '')
               FROM     sys.master_files a
               GROUP BY a.database_id
    SELECT  'RESTORE DATABASE [' + latestbackups.database_name
            + '] FROM DISK = N''' + latestbackups.physical_device_name
            + ''' WITH FILE = 1, ' + movefiles.myText
            + ', NOUNLOAD, REPLACE, STATS = 5;' + CHAR(10) + 'GO' 
    FROM    msdb.dbo.backupset bs
            INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
            INNER JOIN latestbackups ON latestbackups.backup_set_id = bs.backup_set_id
                                        AND latestbackups.database_name = bs.database_name
                                        AND latestbackups.rn = 1
            INNER JOIN movefiles ON DB_NAME(movefiles.database_id) = latestbackups.database_name; 

Here are the results:

20150610 Restore Latest Full Backup With Move Script Results

Using this script I was able to significantly reduce time to set up my RESTORES and can now restore all the user databases with the latest FULL backup whenever I want with little effort.