Click here to Skip to main content
15,850,685 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all,
I'm having a little trouble restoring a database backup.

I've got the following queries for backing up my database:
SQL
-- Full backup every night at 01:00.
DECLARE @FileName VARCHAR(256)
SET @FileName = 'C:\Backups\mydb_backup_' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) + '_full.bak'
BACKUP DATABASE [mydb]
TO DISK = @FileName

-- Differential every three hours daily starting at 00:00.
DECLARE @FileName VARCHAR(256)
SET @FileName = 'C:\Backups\mydb_backup_' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) + '_diff.bak'
BACKUP DATABASE [mydb]
WITH DIFFERENTIAL
TO DISK = @FileName

-- Every 5 minutes starting daily at 07:00.
DECLARE @FileName VARCHAR(256)
SET @FileName = 'C:\Backups\mydb_backup_' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) + '_tran.bak'
BACKUP LOG [mydb]
TO DISK = @FileName


So that gives me three files a day, a full backup, a differential backup and a transaction log backup.

Now when I try to restore (on a different server) I simply right click on 'Databases' in SSMS and then I choose 'Restore Database'. I choose 'From device' and add my three backup files.
I get the following error message though:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The media loaded on "C:\Backups\mydb_backup_20150715_tran.bak" is formatted to support 1 media families, but 3 media families are expected according to the backup device specification.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3231)
I've Googled this message and I've found lots of pages addressing this problem... But for some reason I can't find what's causing this problem and how I can solve it.

One way to get around the problem is by restoring the files one by one and leaving the database in a restore state. This works for the full and differential backups, but when I get to the transaction logs I get the following error:
Restore failed for Server 'myserver'.  (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 101105000000049300001, which is too recent to apply to the database. An earlier log backup that includes LSN 101097000000212100001 can be restored. (Microsoft.SqlServer.Smo)
I know what it means, but not why I get it.

So I seem to be missing something...
Any tips are welcome.
Thanks.
Posted

1 solution

This is based on a (faint) memory but if I remember correctly, when you define multiple devices, it means that the backup is distributed on those media. Based on that you should use only one media on the restore since you have all the data in one file.

However I believe that it would be easiest to leave the database in restoring state and restore it file by file.

What comes to the log restore problem it looks like you have simply started from too recent log backup. Try previous one.
 
Share this answer
 
Comments
Sander Rossel 15-Jul-15 11:23am    
Thanks for your reply.
I'm not sure I get what you mean. What do you mean with 'device'? The data isn't in one file, right? It's in three files, the full, diff and tran files.

File by file would work, but I don't understand the error I get.
If I simply get all the latest files it should be alright, right?
Wendelius 15-Jul-15 13:05pm    
I'm sorry, it's a bit hard to find the correct terms. In your question you said that you selected 'From device' and defined the files to use. What I'm after is that is SQL Server expecting that the backup is distributed across the files.

Please have a look at https://msdn.microsoft.com/en-us/library/ms178062.aspx[^]
Sander Rossel 16-Jul-15 4:21am    
That was one of the resources I found, but didn't fully understand.
Man, making a simple backup sure isn't simple :-p

I got why the log backup isn't working though, so that's something I can fix.
Thanks for your help!
Wendelius 15-Jul-15 13:07pm    
About the last question, are the latest files sufficient, latest full and differential yes but then you need all the log backups in order to recover the db since the log backup contains only changes starting from previous log backup.
Sander Rossel 16-Jul-15 4:06am    
Oh, right! I think what I failed to mention is that I delete all files after they've been moved to another location, so each backup creates a new file.
That's fine for the full and differential backup, but I shouldn't delete the log backup file and instead just append to it each time (until a new full backup is taken).
That's why it's giving me the error (I only have the very latest log backup).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900