Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005?

Leave a comment (0) Go to comments

Yesterday, this question was asked by one of the training participant, Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005?

No, It is not possible to restore a database from a backup of a newer version because SQL Server 2008 database backups are not backward compatible with SQL Server 2005.

When participant tried to perform restore a SQL Server 2008 backup file on SQL Server 2005, he got the following error message

Error Message: “The media family on device ‘%ls’ is incorrectly formed. SQL Server cannot process this media family.”

Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005? The media family on device %ls is incorrectly formed. SQL Server cannot process this media family SQL Server error 3241 SQL Server 2008 database restore on SQL Server 2005 SQL Server 2008 database onto an instance of SQL Server 2005 sql database backup restore database on sql server 2005 error 3241

Here are the Key points for migrating backup between versions:

  1. In SQL Server 2008, you can restore a database backup that was created by using SQL Server 2000, SQL Server 2005, or SQL Server 2008.
  2. Backups of master, model and msdb that were created by using SQL Server 2000 or SQL Server 2005 cannot be restored by SQL Server 2008.
  3. SQL Server 2008 backups cannot be restored by any earlier version of SQL Server.
  4. SQL Server backup and restore work across all supported operating systems, whether they are 64-bit or 32-bit systems.

But, If you need to copy the database from SQL Server 2008 to SQL Server 2005, then you can do a work around which is data copy or data export


Steps for Exporting Data from SQL Server 2008 to SQL Server 2005 or 2000:

  1. Generate Database Script : To create the scripts, run the "Generate SQL Server Scripts" Wizard in SQL Server Management Studio by right clicking on the database and selecting "Tasks –> Generate Scripts."
    It shows the initial dialog to the wizard. Click "Next" and complete the following steps in the wizard to back up the database on SQL Server 2008 and restore it to SQL Server 2005:
    1. Click "Script all objects in the selected database", and then click "Next."
    2. Change the following script options:
      1. set "Script for Server Version" to "SQL Server 2005"
        1. and set "Script Data" to "True".
        2. If you are putting the database on a new instance for the first time, make sure the "Script Database Create" option is set to "True." And Click "Next"
        3. Select "Script to file"
        4. Select the file name
        5. and choose "Single file"
        6. Click "Next" for a summary
        7. Now click on "Finish" to get progress messages while the script runs and completes
        8. If the generation process fails, then you can use the "Report" option to see why.
      2. When the scripting is completed, look for the following lines:

        CREATE DATABASE [Northwind]

        ON PRIMARY

        (NAME = N'Northwind', FILENAME =N'C:\MSSQL\DATA\Northwnd.mdf' ,

        SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

        LOG ON

        (NAME = N'Northwind_log', FILENAME = N'C:\MSSQL\DATA\Northwnd.ldf' ,

        SIZE = 1024KB ,FILEGROWTH = 10%)

        GO

        *** You will need to amend the paths to a valid path.

      3. You also need to comment out the following lines like this:

        --EXEC sys.sp_db_vardecimal_storage_format N’Northwind’, N’ON’

        --GO

      4. Once you have made these changes, you can run the script in SQL Server 2005 Management Studio to recreate the database in your development environment.

      5. Once this is being Done, Start Exporting the Data using the Import and Export Wizard.

EOF - Is it possible to restore a backup of a SQL Server 2008 database onto an instance of SQL Server 2005?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.