Hi all,
I'm having a little trouble restoring a database backup.
I've got the following queries for backing up my database:
DECLARE @FileName VARCHAR(256)
SET @FileName = 'C:\Backups\mydb_backup_' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) + '_full.bak'
BACKUP DATABASE [mydb]
TO DISK = @FileName
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
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.