Solved

Error restoring database from backup file.

Posted on 2015-02-17
17
117 Views
Last Modified: 2015-02-18
Hi Experts,
I am trying to restore a database from a backup file, an action that already successful did numerous times, however this time i am getting an error.
See attached.
untitled.bmp
untitled2.bmp
0
Comment
Question by:bfuchs
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 84 total points
ID: 40615320
This link: https://social.msdn.microsoft.com/forums/sqlserver/en-US/b3f9a97c-f0ec-4cf7-b21d-44d28bd40526/move-db-the-media-family-on-device-is-incorrectly-formed

Suggests a SQL Server version mismatch between the backup and the instance you're trying to restore it onto.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40615388
@SimonAdept,
If that is the case, is there a way of creating a backup with later version and specifying an earlier format?
(Like a can create a word document using Office 2013 and save it in 2000/3 format..)
Actually I am performing those backups with 2008, while I need them restored in 2005.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 249 total points
ID: 40615481
You could try to change the compatibility level in Database Properties - Options to SQL 2005. If this does not help then you may create a script which will script the database with data:
http://blog.sqlauthority.com/2012/07/18/sql-server-generate-script-for-schema-and-data-sql-in-sixty-seconds-021-video/

Better option would be to upgrade the SQL 2005 to 2008.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 42 total points
ID: 40615682
If that is the case, is there a way of creating a backup with later version and specifying an earlier format?
Nope.  All you can do is export all the data from SQL Server New Version to SQL Server Older version.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40615825
@pcelba,

Attached are the options I have for the database including compatibility level, do you think its necessary to change something?
Untitled.png
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 84 total points
ID: 40615981
I'd say your best option (as possibly suggested by pcelba, though I haven't visited the blog link) is to use the Generate Scripts task by right clicking on the database node, choosing the option to include data as well as structure and then run the script on your 2005 instance.
You compatibility level setting (2000) is fine.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 249 total points
ID: 40616085
If you have compatibility set to SQL 2000 already then the switch to 2005 cannot help. It was just a suggestion because I did not test it yet.

Other two options (the script or target db upgrade) should be OK.

(The link in my previous answer contains a video tutorial describing what is stated in SimonAdept's post.)
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40616358
You shouldn't have any issue in restoring a SQL Server 2000 database backup into a SQL Server 2005 instance. You may have a corrupted backup and that's why you are getting the error. Try to perform a new backup and restore.
Also, which release of SQL Server 2000 do you have?
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 249 total points
ID: 40616471
But this seems to be SQL 2008 database with SQL 2000 compatibility level. This setting does not ensure the file format just restricts some commands, data types etc.
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40616488
By the screen the instance is SQL Server release 9.0.3042 (MSSQL 2005 SP2) but SQL Server 2008 can also read SQL Server 2000 databases.
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 249 total points
ID: 40616509
Yes, 9.0.3042 is the target SQL 2005 machine which is attempting to read the backup created on the SQL 2008 (with 2000 compatibility set). You may see different server names on the pictures.
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40616523
You're right. I've missed this comment: "Actually I am performing those backups with 2008, while I need them restored in 2005"
Thanks for pointing me that.

So, in this case it can't be "downgraded". Once a database is attached/restored in an higher version it can be undone.
Use the Copy Database Wizard instead.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40617453
@ All Experts,

So what you're saying is, I should do the following:
A-Restore the database from backup file in SSMS 2008
B-Run the wizard that generate scripts for re-creating that database including all data (also in 2008).
C-Run those scripts generated in SSMS 2005

Is that correct?
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 249 total points
ID: 40617497
If the backup was created in SQL 2008 then yes, this is correct.
The script creation can be a long process, so I would recommend to test it on a few tables first. Of course, if your database is not large then you may do it in one step.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40617580
@pcelba,
What is considered large database? and how long can this process take for a DB of 3.5 GB? (not the log file).
Actually, what I am looking here is to restore a record accidentally deleted from a table, so I guess I can just select that particular table, and I will end up with a DB containing just that one table, correct?
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 249 total points
ID: 40617609
Well this depends on your computer speed... But if you are talking about one table then the script speed will be OK (if the table isn't the only one in the 3.5 GB database :-).

So one table is fain, the whole database scripting could take 10 - 30 minutes or even more.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40617731
That worked fine.
Thanks to all participants.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now