THE SQL Server Blog Spot on the Web

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

The Bit Bucket (Greg Low): IDisposable

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

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.

If you agree, you know what to do: please vote:  

https://connect.microsoft.com/SQLServer/feedback/details/769204/option-to-restore-all-backups-files-within-a-media-set

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.

 

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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement