Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

Error restoring database from backup file.

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
bfuchs
Asked:
bfuchs
  • 6
  • 5
  • 3
  • +2
12 Solutions
 
SimonCommented:
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
 
bfuchsAuthor Commented:
@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
 
pcelbaCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Anthony PerkinsCommented:
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
 
bfuchsAuthor Commented:
@pcelba,

Attached are the options I have for the database including compatibility level, do you think its necessary to change something?
Untitled.png
0
 
SimonCommented:
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
 
pcelbaCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
pcelbaCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
pcelbaCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
bfuchsAuthor Commented:
@ 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
 
pcelbaCommented:
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
 
bfuchsAuthor Commented:
@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
 
pcelbaCommented:
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
 
bfuchsAuthor Commented:
That worked fine.
Thanks to all participants.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now