Suggestion: ALLFILES option for RESTORE

The default action when performing a backup is to append to the backup file yet the default action when restoring a backup is to restore just the first file.

I constantly come across customer situations where they are puzzled that they seem to have lost data after they have completed a restore. Invariably, it's just that they haven't restored all the backups contained within a single OS file. This happens most commonly with log backups but also happens when they have not restored the most recent database backup file.

It is not trivial to achieve this within simple T-SQL scripts, when the number of backup files within the OS file is unknown. It really should be.

I'd like to see a FILES=ALLFILES option on the RESTORE command. For RESTORE DATABASE, it should restore the most recent database backup plus any subsequent log files. For RESTORE LOG (which is the most important missing option), it should just restore all relevant log backups that are contained.

Alternately, how would you write a T-SQL command to restore all log backups within a single OS file where the number of files is unknown? Would love to hear creative solutions because all the ones that I think of are pretty messy and need dynamic SQL.

Comment Notification

Comments

Good suggestion - this is a messy area. I wrote a script, which needs a lot of refinement, to look in a folder for the latest full backup, then the latest diff backup, then all subsequent log backups.. It was annoying enough getting the FILELISTONLY out of a backup file without having to deal with multiple backups within a single OS file. Something which let you just specify a folder for the MOVE command without having to list each file would be nice (I appreciate it's not all roses if you want database and log files on different drives, but for small sites that I deal with I never get that sort of luxury)

I quite like the improvements in the GUI for restoration with SQL 2012 management studio though :)

Hmm, nice idea, but it would need to be fairly intelligent wouldn't it.

Pick the last clean, full backup, the latest viable diff, then only subsequent logs. Would still need the stopat clause, so all of this picking would need to be dependent on that datetime too.

But why stop there, instead of/as well as parallel backup files, it should take a list of sequential backup files too - or fulls, diffs and logs in separate files, and all for the _right_ log sequence chain if you restore it elsewhere but back that up to the same file etc...

The permutations of getting this right can be pretty complex - at a guess not really something MS would really want to support! Cost benefit and all...

I've written a script similar to what Ian suggests in his post. It's not pretty but does the job. Basically imports the contents of FILELISTONLY into a temp table and generates the restore script using the move option to a specified location. There are also a few good powershell scripts out there that accomplish the same task.