Problem restoring backup to SQL database

I'm having an issue restore a SQL backup to the original database.  We moved our primary database to our vendors cloud, but need to keep a copy locally for our reporting tools.  They are sending a daily backup of the database, but I'm having an issue getting it restored.  I'm on SQL server 2008 R2, but I don't know what version the vendor is using.  I need to get this resolved this weekend and the vendor is not available and says sql questions are beyond their scope.   Here is the error message.  How do I get this to work??

I think the primary message is: The media family on device 'C:\Temp\RE7_21559_db_20180208210001773_COMPRESSED.BAK' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)


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

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query)
   at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd)
   at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv)
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

===================================

The media family on device 'C:\Temp\RE7_21559_db_20180208210001773_COMPRESSED.BAK' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)
cindyfillerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dean ChafeeIT/InfoSec ManagerCommented:
You are restoring from disk file(s) right?  The RESTORE HEADERONLY option is typically used to look at media info for tape, not disk files.

Try this to get the info from the backup file on disk:
RESTORE FILELISTONLY      FROM DISK = 'C:\Temp\RE7_21559_db_20180208210001773_COMPRESSED.BAK'

If that runs, then verify the PhysicalName to see if this is where you want files to be restored to.
0
ste5anSenior DeveloperCommented:
Either the backup file is corrupt, can happen during transfer, or you're trying to restore a backup from a higher SQL Server version. In the first case try to download it again. In the second case you need a newer SQL Server on your server (try a trial version or try to restore it on Azure).
1
Dean ChafeeIT/InfoSec ManagerCommented:
Also, assuming that  'C:\Temp\RE7_21559_db_20180208210001773_COMPRESSED.BAK' is a full backup and you want it restored into a usable mode, and you need to relocate the MDF on your system, use this:

 RESTORE DATABASE RE7(or your DB name)
 FROM DISK = 'C:\Temp\RE7_21559_db_20180208210001773_COMPRESSED.BAK'
  WITH MOVE 'RE7'         TO 'D:\SQLdata\RE7.MDF'
     , RECOVERY, STATS=10, MAXTRANSFERSIZE = 4194304, buffercount = 24;

Run all this stuff in an SSMS query

Best of luck.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cindyfillerAuthor Commented:
Dean, I tried the filelistonly and it gave me the same error as the restore itself.  I didn't try the other section of code in the 2nd comment.  I need to keep the name the same as there are a series of etl packages that take this database and updates a data warehouse.  I remember when I upgraded this server it was a nightmare to find all of the hooks that needed to be changed so it properly updated.  

The support team can't tell me what version of Sql the cloud version is on, so its going to be a guessing game.  I may try and upgrade and see what happens.  I don't know what else to do.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Cindy, I'm almost sure that you're trying to restore a backup from an earlier version of SQL Server.
You really need to get the version of the source database. Do you also know the Cloud solution that is being used?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cindyfillerAuthor Commented:
Support doesn't know and the team that manages the software doesn't work on the weekends.  I've asked another org that has the same software what they know, but so far they are also out of the office.  I think I'll try and upgrade to sql 2012 and see what happens.  I'm wondering if the ETL packages will still work between the 2012 sql server and the 2008 R2 sql server.
0
ste5anSenior DeveloperCommented:
For now, just install the a trial version, restore the database, and transfer it to your server.

When you upgrade, then go for the actual version (2017).

btw, "cloud" sounds like either 2016 or 2017.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Or you wait until Monday or you try to catch somebody available during today.
ETL packages should run on the correct SQL Server version where they've been built.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
I would say the easiest way to find out is to download SQL 2017 Express and then do a

RESTORE HEADERONLY FROM DISK='file path.bak'

Open in new window


and it will tell you the version of the database and you can look up the version here.   https://sqlrus.com/2014/10/compatibility-level-vs-database-version/
0
cindyfillerAuthor Commented:
Here is a further update.  I really can't wait until Monday to do this - we haven't had daily reports for 2 weeks and need to have the data warehouse updated today.  One of the people I've been working with said he'd find out what version of sql they are using, but hasn't responded (that was yesterday).  I contacted another client yesterday and she said they have 2008 (didn't know if it was R2).  I can't download the sql 17 express because I'm on windows 2008 R2 and that doesn't run on 2008.  I had a license for sql 2012 so I went ahead and upgraded out of desperation.  I couldn't get the restore from backup to work - it seems to have another line that it wants added and I don' t know what to put.  But I ran the header command and that also didn't work - with a similar error to what I'd gotten in the 2008 version:  Msg 3241, Level 16, State 0, Line 1.  I have no clue where to go from here...  

The media family on device 'c:\temp\Fe_21559_db_20180209113939687_compressed.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
OK, here is something you can do to see what type of file was sent.  If you open the file in Notepad++ or Notepad (if it not too large) see if the first characters in the file are MSSQLBAK.  If they are then it is a backup file. If not it could be a zipped backup (used by another program to backup and compress)

Just trying to get you a clue of what type of file it is now since you are using 2008R2 it should be able to restore a 2008 backup.
0
cindyfillerAuthor Commented:
It is tyoo large to open.  I want to ask a few more questions...  this is a compressed backup.   I don't see anything that says you have to "unzip" it before restoring it, correct?  It has a .bak extension.
0
ste5anSenior DeveloperCommented:
The .bak extensions could by simply wrong. Use HxD.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you're getting the backup from a cloud solution it's mostly possible that they're using the last version of SQL Server (2017) if not the previous one (2016) so I don't believe that SQL Server 2012 would have a different behaviour.
0
cindyfillerAuthor Commented:
I'm still waiting on the vendor to tell me what version of SQL they are using... I don't understand why this should take so long.  I did get sql 2017 express installed on a different server and ran the restore headeronly - it said it ran successfully but it didn't give me any information.  I'm not sure if I'm missing something or I need to check some other place??  It sounded like this would tell me the version??  

If I can't get the vendor to reply - I'm hoping to get the version another way.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
I should have read the name of the file.  You will want to get the SQL 2017 Developer (it is free too) and install that instead because SQL 2017 Express does not handle compression.
0
cindyfillerAuthor Commented:
I just found out that the vendor is using sql 2014.  I'm going to make a snapshot and try and in place upgrade.  I think someone said that the etl package should still work ok??  any words of wisdom... hopefully this works fine tonight.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
The package should still run. But that explains why it was not restoring in 2008 R2 since it is 2014.
0
cindyfillerAuthor Commented:
Trying to upgrade now and have this error message (failed)... Is there anything I can do to get this upgrade done??  If not, do I install 2014 on the same server and then how do I upgrade the data base?   Looking for options I can do tonight.  Thanks
0
cindyfillerAuthor Commented:
Forgot to include the error:  FeatureUpgradeMatrixCheck failed
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Upgrades in SQL or any land are tough.  They don't always upgrade well, and most of it depends on what features are installed in the current version of SQL.

So if you need to upgrade the database, you can install SQL Server on the same machine although you will end up with a named instance if you don't already have it.  The other problem is whether or not the SQL 2008 will still run after the upgrade attempt.

If you can successfully install SQL 2014 and the SQL 2008 R2 server still runs, then you have a really good shot to migrate everything to the new server.  Let me know the state of your SQL Server (current) and we can go from there.
0
cindyfillerAuthor Commented:
I'm trying not to install 2014 on another server as it feeds our data warehouse and that is a nightmare to get changed to another server.  I think that it isn't upgrading because I have sp3 installed and the articles I've found say you can upgrade sp 2 and below.  So I was trying to remove the sp 3 from home and it wants a cd... just put that sp on this weekend cause I thought that might help.  

Not sure where to go... so desperately need to get our data warehouse updated so I can run mission critical reports.  I'm far from a sql expert...  ugh.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
That is going to be pretty tough through question and answer.
0
cindyfillerAuthor Commented:
I know.... you said if I installed 2014 on the same server that 2008 might not work. That wouldn't be an issue if 2014 worked. It isn't used by staff.  To literally just holds the updated database so the data warehouse can be updated.  I have a clone of the server and a snapshot so I can roll back pretty easy.
0
cindyfillerAuthor Commented:
I bit the bullet and am in the process of setting up a new server and will detach and reattach my databases on the new server.  I couldn't get the in-place update to work.  I just want to go back to my original question.  When I restore from backup, it wants a backup set.  This backup is coming from another location so I don't have a backup set.  How do I restore in this case?
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
You would restore from device and point to the backup file.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Or write the tsql with restore database from disk=‘path to file’ with move ‘internal name’ to ‘new path’ etc.
0
cindyfillerAuthor Commented:
Yup - I had tried that.   I did restore from device and selected the correct file.   But it wants me to enter something for the backup set in 2014 and I don't have a backup set.  It won't let me get any further without that... and I don't know a way around it.  The backup set wasn't required on 2008 R2.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
That sounds like the backup file has more than one backup in it. There isn’t a drop down for the backup set?
0
cindyfillerAuthor Commented:
No drop down for the backup set.  It is like it is assuming the backup came from my server and there should be a backup set and if it can't find a backup set it won't let me restore.  I've also tried typing in a name, but it won't accept something manually typed.  I've googled it and couldn't find a match for this issue.  

Really do appreciate your help!  I know this has gotten alot more complicated than I ever imagined.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
Can you show me a screen shot of the window that is giving you the error?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're using the GUI and sometimes isn't very intuitive.
Instead of restoring backups you can also detach the database from the old SQL Server instance, copy the data and transaction log files to the new server and attach the database in the new SQL Server instance. Do not forget to change the compatibility level to 12 (SQL Server 2014).
You can go through all the process by reading my articles on how to migrate SQL Server databases:
SQL Server database migration - The Detach/Attach method
SQL Server database migration - The Backup & Restore method
SQL Server database migration - The Copy Database Wizard method
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
So when you go into the restore dialog and select the Device, do you see the Restore Plan below with the words "Backup sets to restore" and below there is a list of backups that have checkboxes beside them to check to be able to choose to restore?  These are the backup sets that it is talking about.  If there is an error that there are no backup sets, then the backup file does not contain a backup.
0
cindyfillerAuthor Commented:
I want to thank everyone for their assistance in this.   I finally have the backups working, but have one final question if anyone feels like answering it.  I was doing a restore / replace database and it never updated the database.  The vendor told me I'd have to detach the old database and then attach the backup copy instead of doing a restore/replace.  Is there a reason why the restore didn't work?  Was it because the backup came from another server?  

I will go through and award points and mark this closed later this weekend - I just wanted to thank everyone now.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was doing a restore / replace database and it never updated the database.  
How are you performing the Restore?

Is there a reason why the restore didn't work?  
Why didn't work? It gave an error?
0
cindyfillerAuthor Commented:
I was using sql management console.  I'd right click on the DB and do a restore and then select restore from backup disk.  I clicked on the options and selected replace.  It said it was replaced and the job log showed it was replaced, but there was never any new data in the db.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That means the backup you used to restore, it doesn't have any data, i.e. your restoring from an empty database backup.
0
ste5anSenior DeveloperCommented:
[..] but there was never any new data in the db.
Why should there be new data? Just because you restore a backup does is not a reason for this expectation.
0
cindyfillerAuthor Commented:
There is data in the backup - when I did the detach and reattached it all of the new data was there....  It was our cloud based database including all of the new data entered that day.  No new data was listed when I did the restore, but new data was there when I attached it.  It's just bizarre...  it's faster to do the restore, but not if it doesn't work.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
When the backup was made? You can see that in the backup file date.
0
cindyfillerAuthor Commented:
I wish I could mark all of these as the best solution, but I can't.  You all provided the solution to 2 separate problems - initially the sql version and then more on the backup issue.  I selected the first answer for the sql solution as the best as I couldn't figure out another way to do it.

Again, thanks to all of you - this was a long problem that is finally resolved because of your assistance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.