Post reply

How to restore 90 compatible database from 2008 to 2005?

  • November 24, 2009 at 4:49 am

    #216992

    I have restored few SQL Server 2005 databases to SQL Server 2008 server last month. I have not changed the database compatibility level. Database compatibility level is still 90.

    Now I need to revert to a SQL Server 2005 server. But the backup taken from SQL Server 2008 server is not getting restored. Restore command gives following error.

    The media family on device 'FileName.bak' is incorrectly formed. SQL Server cannot process this media family.

    Is there any solution for this problem?

  • November 24, 2009 at 5:02 am

    #1083368

    If you search the forums for 'backup 2005 2008' you'll find a couple of articles that explain why you can't do this via the normal backup routines.

    Scripting the database and data back appears to be the only alternative.

    BrainDonor

    Steve Hall
    Linkedin
    Blog Site

  • November 24, 2009 at 5:16 am

    #1083371

    Thanks BrainDonor for your quick reply. I thought there is some way to restore as the compatibility is not changed. It is unfortunate that it is not possible.

    Detach/attach is also not working.

    Scripting is taking long time and I have many databases.:crying:

  • November 24, 2009 at 5:42 am

    #1083385

    Scripting is also failing due to violation of constraint (foreign key, I guess).

    Is this a problem without any solution?

  • November 24, 2009 at 7:20 am

    #1083438

    You should be able to script your database for SQL Server 2005 to recreate the database structure in a SQL Server 2005 database and then use export data to transfer data from SQL Server 2008 to SQL Server 2005.

  • November 24, 2009 at 7:27 am

    #1083442

    I had/have a similar situation and I am implementing snapshot replication as the solution.

    You might face few issues, but end of the day your task will be done.

  • November 24, 2009 at 8:18 am

    #1083478

    dba_pkashyap (11/24/2009)

    I had/have a similar situation and I am implementing snapshot replication as the solution.

    You might face few issues, but end of the day your task will be done.

    Is replication from SQL 2008 to 2005 supported? If yes, it will be better option than scripting.

  • November 24, 2009 at 8:39 am

    #1083495

    Suresh B. (11/24/2009)

    I thought there is some way to restore as the compatibility is not changed.

    Compatibility level has nothing to do with database version. The compatibility level just affects how certain T-SQL constructs are interpreted and which ones are valid. The database version is solely affected by the version of server the DB is attached to. A database attached to a SQL 2008 instance is a SQL 2008 database, the internal database version, file structure and system tables are for SQL 2008 and lower versions cannot attach or restore them.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 24, 2009 at 9:44 am

    #1083543

    Thanks Gail.

  • November 25, 2009 at 2:13 am

    #1083871

    dba_pkashyap (11/24/2009)

    I had/have a similar situation and I am implementing snapshot replication as the solution.

    You might face few issues, but end of the day your task will be done.

    Thanks Kashyap,

    You have saved the day!

  • November 25, 2009 at 6:57 am

    #1083969

    No Probs Suresh.

    Good Luck!

    Pavan.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply