none
Problem in restoring back up to Sql server 2008 R2 RRS feed

  • Question

  • In SQL Server 2008 R2 and Windows 7 Home Premium, Im receiving the following error when I try to restore the backup which is taken from SQL Server 2008.

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    ADDITIONAL INFORMATION:

    The media family on device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Loan.bak' is incorrectly formed. SQL Server cannot process this media family.

    RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=3241&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

     

    So that I failed to get restored my DB.. 

    Anyone please help me to get this work done, Any help appreciated

     

    Thanks in advance.

    Vimal

    ------------------------------


    Saturday, June 12, 2010 9:44 AM

Answers

  • Sankar,

    Thing is I took the backup with the default(Task ---> Backup) options there I do not know with the default options what is the mode being used. May know how I can check for the mode of the backup?

     Regards,

    Vimal


    Vimal, Looks like you are just using the SSMS to do the backup and there is NO FTP transfer here. In that case, re-take the backup of your old system again and by the posts your current backup may be corrupt and will not be of any help.


    http://SankarReddy.com/
    • Marked as answer by Roshe Sunday, June 13, 2010 4:02 PM
    Sunday, June 13, 2010 3:43 PM
    Moderator

All replies

  • Vimal,

    As far as I can tell, your backup may have been corrupted while transferring. Did you use FTP to transfer this backup from server to server then may be you used the incorrect text mode instead of binary?

     



    http://SankarReddy.com/
    Saturday, June 12, 2010 12:51 PM
    Moderator
  • Sankar,

    Thanks for your reply, I think that you are saying it correct. 

    So what I can do in oder to get it done with the existing backup since I down the earlier server machine? will it be working if I use Sql server 2008? if so then how I can configure the server to get the backup in FTP binary mode?

    Vimal.

    Saturday, June 12, 2010 2:36 PM
  • Vimal,

    How did you get this backup and you haven't confirmed if you used FTP to transfer the backup? If you used FTP text mode earlier to transfer the backup then I would try with FTP binary mode to see if it works otherwise get a new backup of your old database again.


    http://SankarReddy.com/
    Saturday, June 12, 2010 10:50 PM
    Moderator
  • Sankar,

    Thing is I took the backup with the default(Task ---> Backup) options there I do not know with the default options what is the mode being used. May know how I can check for the mode of the backup?

     

    Regards,

    Vimal

    Sunday, June 13, 2010 11:24 AM
  • Could you paste the result of the queries

     

    RESTORE

     

    LABELONLY

    FROM

     

    DISK='backfilepath\backupname.BAK'

     

    RESTORE

     

    HEADERONLY

    FROM

     

    DISK='backfilepath\backupname.BAK'


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Sunday, June 13, 2010 2:01 PM
  • Query which I executed:

    RESTORE

    LABELONLY

    FROM

    DISK='C:\DRIVE - COLLECTION\Docs\Loan.BAK'

     

    RESTORE

    HEADERONLY

    FROM

    DISK='C:\DRIVE - COLLECTION\Docs\Loan.BAK'

     

    Result was:

    Msg 3241, Level 16, State 7, Line 1

    The media family on device 'C:\DRIVE - COLLECTION\Docs\Loan.BAK' is incorrectly formed. SQL Server cannot process this media family.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE LABELONLY is terminating abnormally.

    Msg 3241, Level 16, State 7, Line 15

    The media family on device 'C:\DRIVE - COLLECTION\Docs\Loan.BAK' is incorrectly formed. SQL Server cannot process this media family.

    Msg 3013, Level 16, State 1, Line 15

    RESTORE HEADERONLY is terminating abnormally.

    Sunday, June 13, 2010 3:36 PM
  • Sankar,

    Thing is I took the backup with the default(Task ---> Backup) options there I do not know with the default options what is the mode being used. May know how I can check for the mode of the backup?

     Regards,

    Vimal


    Vimal, Looks like you are just using the SSMS to do the backup and there is NO FTP transfer here. In that case, re-take the backup of your old system again and by the posts your current backup may be corrupt and will not be of any help.


    http://SankarReddy.com/
    • Marked as answer by Roshe Sunday, June 13, 2010 4:02 PM
    Sunday, June 13, 2010 3:43 PM
    Moderator
  • It seems there is some incompatibility between sql express 2008 and the R2 version.  I have tried serval times to backup a db on the r2 version and restore to an earlier version...it never works, althought I can restore the r2 .bak to the r2 version.  Everything I've read here and elsewhere is totally wrong.  I don't understand why MS is not weighting in on this issue. Does anyone have a clue?
    Tuesday, June 29, 2010 3:34 AM
  • Christoper,

    SQL Server has internal database version and this is different from the SQL Server version (SQL Server 6.5, SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2)

    In every version, SQL Server maintains lot of meta-data and the schema of these meta-data structures change a lot. When you restore a lower version onto a higher version server then SQL Server automatically upgrades the internal database version and in this case you don’t have to do anything specific. These meta-data structures aren’t backward compatible and that means you can’t restore a db that is created in a higher version onto a lower version.

    In your above case, SQL Server 2008 R2 bumps the internal database version and is different from SQL Server 2008. That's why you can't take a backup in SQL Server 2008 R2 and restore on SQL Server 2008.

    Your options to export the data from R2 and re-insert backup into SQL Server 2008. 

    I wrote some notes on this topic at 

    http://sankarreddy.com/2010/05/database-internal-version-create-version-and-current-version/

    Reference:

    http://sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-R2-bumps-the-database-version.aspx


    http://SankarReddy.com/
    Tuesday, June 29, 2010 3:51 AM
    Moderator
  • Try running a Restore Headeronly to find your file names and then placing those filenames with the example below.  Use caution when using the REPLACE command.  You don't want to replace your production database with something that is not what you want.  http://msdn.microsoft.com/en-us/library/ms186858.aspx

    RESTORE DATABASE TestDB
       FROM AdventureWorksBackups
       WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MySQLServer\testdb.mdf',
       MOVE 'AdventureWorks2012_Log' TO 'C:\MySQLServer\testdb.ldf',REPLACE;
    GO


    R, J

    Friday, October 5, 2012 7:12 PM