THE SQL Server Blog Spot on the Web

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

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Suggestion: ALLFILES option for RESTORE

This blog has moved! You can find this content at the following new location:

http://greglow.com/index.php/2012/10/31/suggestion-allfiles-option-for-restore/

Published Wednesday, October 31, 2012 11:36 AM by Greg Low

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

Comments

 

Ian Yates said:

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

October 31, 2012 1:41 AM
 

RichB said:

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

October 31, 2012 8:12 AM
 

Stephen Morris said:

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

November 2, 2012 4:45 AM
 

Greg Low said:

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.

November 11, 2012 8:02 PM
 

Luke Campbell said:

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.

February 14, 2013 12:52 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement