THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Can you restore from your backups? Are you sure?

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...)


'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file '
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_time_sec
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;



Published Monday, November 3, 2014 7:52 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Charles Kincaid said:

Great idea.  We all need to practice doing restores.  We have our clients send us backups frequently and we have our staff doing restores to our local machines.  We do trend analysis on their data.

Because we aree not restoring to the original box we don't have the backup history.  Also since we do so many restores the backup history on our servers is trash.  We have learned not to rely on it.

Also since the GUI is kind of brain dead in all but the simplest cases I have taught that we don't use it in any case.  Writing our the restore command and then (this is important) explain it to someone else before you run it is the best way.

November 9, 2014 12:45 PM

TiborKaraszi said:

I agree, Charles! The funny thing is that the restore commands aren't that difficult to learn, and even easier to understand. And you still want to verify that you do the right thing, even is you use the GUI. So, IMO, the value of the GUI here is questionable - even if I understand that the product need to have it. I always recommend my students that even if you use the GUI, then script the commands and at least read that script as a double check that you are about to do what you expect!

November 11, 2014 3:11 AM

ElviB said:

Thank you for sharing.

I can see a 'is_snapshot=1' in my environment.

I have also added backup_finished_date in the select statement to see in the results the date and time also. ( not just order ).


model Full 2014-11-12 17:25:00.000 1 1 4 {..} 1 model

November 21, 2014 9:02 AM

Horia N. said:

Hi, thanks for sharing. Also a good idea in my opinion is to get in place a restore script generator, which can be based on either sys tables or file system. It's always a good idea to have in place standard names for backups and even include timestamp, and you can base a restore script generator on that. In my case I have a full backups folder, same for differential and log backups. I scan through the files and take the ones needed and build the restore statements. Once that's in place, I only have to call a stored proc (with few params if needed) and execute the script. Even more, i can copy the files down on another server through a job and automatically run the restores, overnight.

November 21, 2014 2:43 PM

Sean McCown said:

I never push my customers to look at anything in the GUI.  This whole thing would have been avoided had the GUI just been by-passed to begin with.

February 13, 2015 11:43 AM

TiborKaraszi said:

I agree, Sean. However, there need to be some GUI in the product, methinks. And when teaching entry-level admin course, you just don't get away with not showing the GUI. But I always start with the commands, making it easier to then explain what the GUI does and some of its ...oddness.

February 14, 2015 2:29 AM

Leave a Comment


This Blog


Privacy Statement