Suggestion: ALLFILES option for RESTORE

re: Suggestion: ALLFILES option for RESTORE

Ian Yates — Wed, 31 Oct 2012 05:41:03 GMT

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

re: Suggestion: ALLFILES option for RESTORE

RichB — Wed, 31 Oct 2012 12:12:00 GMT

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

re: Suggestion: ALLFILES option for RESTORE

Stephen Morris — Fri, 02 Nov 2012 08:45:14 GMT

easiest to read the history from MSDB and drive it from there IMHO

re: Suggestion: ALLFILES option for RESTORE

Greg Low — Mon, 12 Nov 2012 01:02:50 GMT

Hi Stephen,

That assumes though that it's on the same server that backed it up. It often isn't, so the entries aren't in msdb.

re: Suggestion: ALLFILES option for RESTORE

Luke Campbell — Thu, 14 Feb 2013 17:52:06 GMT

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.