Solved

Error restoring database from backup file.

Posted on 2015-02-17
17
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 42

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 42

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 50

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 42

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 50

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 42

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 50

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 42

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 42

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

726 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