Restore transaction log backup - media family error

Question

Our backup procedure is as follows: midnight full backup, every 8 hour differential backups, and every 5 minutes transaction log backups. Maintenance plans keep everything rolling and tidy.

To restore to a specific point in time (let's say yesterday at 2:05pm). On restoring with SQL Server Management Studio 2012, we right-click the database we want to restore to > Tasks > Restore > Database. We then restore the full backup with no recovery (and overwrite existing data). This leaves the database in a "Restoring..." state. We then go to > Tasks > Restore > Files and Filegroups. Select our closest diff backup (noon). Restore with no recovery.

Now for the transaction logs - Tasks > Restore > Transaction Log. On that dialog we select "From file or tape", and click the button to choose files. Add, and select the 5 minute transaction logs from 12:00PM to 14:05PM (25 of them). When we click "OK" on the dialog box ... SSMS crashes and gives the following error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The media loaded on "C:...\backup_2012_12_22_120500_4174134.trn" is formatted to support 1 media families, but 25 media families are expected according to the backup device specification. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3231)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2218&EvtSrc=MSSQLServer&EvtID=3231&LinkId=20476

If I recall this is the exact procedure we used in SQL Server 2008 and 2005, so I cannot see why this is failing. Is this something specific to 2012? Is it a bug? I couldn't find any information on this online. We are not using tape backups at all and most of the stuff I read about media families has to do with tape backups.

Troubleshooting: Selecting each transaction log (one at a time) in sequence and restoring works. However this can easily take 15+ minutes with so many transaction logs.

I haven't tried, but I think if we wrote plain old TSQL in the form:

RESTORE DATABASE [OurDB] FILE = N'db_dat' FROM  DISK = N'C:\...\diff\backup_2012_12_23_085000_4627006.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

And ran this for each xaction log - it should work. I can write some powershell or something to restore the database ... but shouldn't the "Management Studio" be able to handle this? This answer seems to point to "No". https://dba.stackexchange.com/questions/1021/how-to-restore-multiple-backups ... however the GUI appears to hint at allowing multiple restorations "Specify the source and location of the transaction log backups" - not to mention it lets you select multiple files in the first place.

Solution 1

A media family has more than one file in it when you stripe your backups. So, if you call your backup like so:

backup log [OurDb] to file = 'c:\file1', file='c:\file2'

In my example, the backup would have been striped across two files (that is to say that half of the backup would have gone to file1 and half to file2) and both files would be needed for a restore. To see what files are needed, take a look at backupmediafamily in msdb. The following query should get you there:

select family_sequence_number, physical_device_name
from backupmediafamily
where media_family_id = (
   select media_family_id
   from backupmediafamily
   where physical_device_name = N'C:\...\diff\backup_2012_12_23_085000_4627006.bak'
)

Then, to restore, you'd have to do something like this for each media family that you want to restore:

restore database [OurDb] from file = ''c:\file1', file='c:\file2', ... with norecovery

Finally, when you're at the point you want to finally bring the database online (i.e. you're done restoring log files), you do:

restore database [OurDb] with recovery

Solution 2

I found a way to resolve this issue

From the Management Studio:

1. Right click the database you want to restore the trn files
2. Select Tasks
3. Select Restore
4. Select Databases... yup, that one
5. Click on Device
6. Click on ellipse
7. Add File
8. Browse to the location of your transaction log files. Make sure they are sorted by date time in the backup location if not it will fail, out of sequence
9. Select the first file, hold down the shift key on your keyboard and select the last file.
10. Click Add
11. Click OK
12. Pick your destination Database. You might have to go to the Files and change the Restore As location to reflect your destination MDF and LDF files.
13. Then click OK

Good luck.

Comments

[mdpc]

Specifically your question?

[MikeMurko]

@mdpc what do you mean by that?