Solved

Error restoring database from backup file.

Posted on 2015-02-17
17
110 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:
SimonAdept earned 84 total points
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@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
Comment Utility
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 42 total points
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@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:SimonAdept
SimonAdept earned 84 total points
Comment Utility
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
Comment Utility
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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 249 total points
Comment Utility
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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
Comment Utility
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
Comment Utility
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 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@ 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
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@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
Comment Utility
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 3

Author Closing Comment

by:bfuchs
Comment Utility
That worked fine.
Thanks to all participants.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

771 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

12 Experts available now in Live!

Get 1:1 Help Now