Sql-server – Restore transaction log backup – media family error

sql serversql-server-2012transaction-log

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.

Best Answer

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
Related Topic