다음을 통해 공유

Unable to restore a backup - Msg 3241

  • 아티클

I worked on an interesting issue today where a user couldn’t restore a backup.   Here is what this customer did:

  1. backed up a database from an on-premises server (2008 R2)
  2. copied the file to an Azure VM
  3. tried to restore the backup on the Azure VM (2008 R2 with exact same build#)

But he got the following error:

Msg 3241, Level 16, State 0, Line 4
The media family on device 'c:\temp\test.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 4
RESTORE HEADERONLY is terminating abnormally.

We verified that he could restore the same backup on the local machine (on-premises).  Initially I thought the file must have been corrupt during transferring.   We used different method to transfer file and zipped the file.  The behavior is the same.   When we backed up a database from the same Azure VM and tried to restore, it was successful.

 

We were at a point I thought there might be a bug and I was planning to get the backup in house to reproduce the problem until this customer told me that they are using a tool called “Microsoft SQL Server Backup to Microsoft Azure Tool” which is only necessary to use for SQL 2008 R2 or below because SQL 2012 and above have builtin functionality to backup to and restore from Azure blob storage.    Then I said how about we stop that service to see what happens.   After stopping that service (screenshot below), restore now works perfectly fine.   After restarting it, the same error.

image

 

 

After a little research, I found out more about this tool.  The backup tool basically is a filter driver that watches any files with certain extension you have configured when SQL Server tries to access the file.

  1. when SQL Server does a backup, the tool will redirect the file to Azure blob storage and leaves a small stub file on local computer
  2. when SQL Server does a restore, the tool will try to access the same file from Azure blog stroage and give it to SQL Server

Now you can see the problem.  In this customer’s scenario, the *.bak file was transferred from on-premise server directly to the Azure VM.  there is no corresponding file on the Azure blob storage.  Since the tool can’t find the file to provide content to SQL Server, the restore fails with the error.

 

Here are ways how you fix the issue

The cleanest way is to configure the rule to watch files only to specific path instead of watching the whole computer.  By default, all path on the local machine is checked.  But you can add multiple paths to watch.  If you do this, you can simply put the backup you copied from remote machine (which is not part of the backup from the server watched by the tool) to a different folder and perform restore.

image

 

Alternatively, you can stop this tool when you restore a backup that you copied from a different machine.

You can also use a different file extension for the file you copied from remote machine and try to restore

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

  • Anonymous
    April 13, 2017
    Amazing post, thanks.
  • Anonymous
    April 26, 2017
    Jack, I am a big fan of your posts. Thank you for sharing
  • Anonymous
    June 23, 2017
    Jack - great read, glad you fixed it :)