A few days ago, we were doing restore practice drills with a client. I had tested the stuff before this, so the practice was more for the client’s DBAs to test various restore scenarios, with me being able to point to the right direction (when needed), supplement the run-book and stuff like that. Always fun, I like these drills!
Anyhow, This client does regular SQL Server backups to disk (full, and for some databases also log) at 19:00. They also snap the machines every night at 04:00. We don’t want to have dependencies on the machine snap, but it is nice to have in case a machine it totaled and we now can restore from such a snapshot. The issue is that this machine snapshot is seen as a full backup by SQL Server. We all know that a full backup do not affect the log backup chain, but the restore GUI doesn’t care about that!
So the restore GUI suggest that you restore from 04:00 full backup (which isn’t a restoreable backup as it was a snapshot) and then the subsequent log backups. What we need to do is to restore from earlier 19:00 full backup, and then all log backups – ignoring the 04:00 snapshot backup.
Fortunately, my client by themselves (without my intervention) did the restore using T-SQL commands, knowing what backup exists, and figuring out what to restore. But I also wanted them to test the GUI, just so they know how that look like. Of course, you can do a restore from 19:00 to 03:55, and script that to a query window. Then then from 04:00 to current time (or whatever) and script that too,. And then stitch these together. But just typing (with some copy-paste) the commands are much easier.
My point? Test your restores. Do not expect anything. A production situation is not the right time to try to figure these things and trying to cope with it.
About this snapshot software: The next version is expected to have an option to produce the snapshot as a COPY_ONLY backup. Isn’t that great? Now we expect the restore GUI to skip this COPY_ONLY backup, right? No, that was not that I saw. Having an option to produce the backup as COPY_ONLY finally allow us to implement differential backups, but it (from my tests) won’t help with the restore GUI issues. Btw, here is a related post.
Here’s a query that might be helpful if you want to see what type of backups are produced. (I appreciate feedback from anybody if you can see if a snapshot backup sets 1 in the is_snapshot column – I don’t have environment to test at the moment…)
SELECT TOP(100)
database_name
,CASE bs.TYPE
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file '
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type
,bs.is_copy_only
,bs.is_snapshot
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_time_sec
,mf.physical_device_name
,bs.database_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS mf ON bs.media_set_id = mf.media_set_id
ORDER BY backup_finish_date DESC;