?
Solved

Error restoring database from backup file.

Posted on 2015-02-17
17
Medium Priority
?
153 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 336 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 43

Assisted Solution

by:pcelba
pcelba earned 996 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 168 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 336 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 43

Assisted Solution

by:pcelba
pcelba earned 996 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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 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 43

Assisted Solution

by:pcelba
pcelba earned 996 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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 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 43

Assisted Solution

by:pcelba
pcelba earned 996 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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 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 43

Assisted Solution

by:pcelba
pcelba earned 996 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 43

Assisted Solution

by:pcelba
pcelba earned 996 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Viewers will learn how the fundamental information of how to create a table.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

594 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