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' ), movefiles AS ( SELECT database_id , myText = STUFF(( SELECT ', ' + name FROM ( SELECT DISTINCT CASE 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 FOR 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:
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.