restoring a backup from sql server 2008 to 2005

  • AmitCBR

    SSC Enthusiast

    Points: 122

    hi,

    i'm trying to copy a database made in sql server 2008 to a server with sql server 2005.

    i get an error:

    restore headeronly is terminating abnormally error: 3241.

    can the management tool do this type of action ?

  • Lowell

    SSC Guru

    Points: 323347

    a backup created on 2008 can only be restored on a 2008 instance; theactual format of the file is different due to the new features.

    this is true of all backups...while you can restore an earlier version backup to a newer, like restore a 2000 backup on 2005 or 2008, they simply get upgraded to the server's version... you can't go in the reverse.

    use a connection/import/export to copy the objects, and then the data to 2005 instead

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • AmitCBR

    SSC Enthusiast

    Points: 122

    Thanks.

    i've started with 2005 so i didn't know the upgrade issues..

    Amit

  • SQLSandwiches

    SSCrazy

    Points: 2141

    What if you have a DB that is in 2005 (90) compatibility level on a 2008 server?

    Can you then back up the 2005 DB and restore it on a different 2005 instance?

    I tired it and it's not working so I assume the answer is no.

  • Lowell

    SSC Guru

    Points: 323347

    compatibility level just decides which syntax rules will be applied against any queries hitting the database...it does not change the behind-the-scenes way the database is stored/saved.

    that's why you can switch compatibility levels instantly...it just affects what query structures are permitted...like switching to version 70 means you can't create a function, even though functions might exist in the database when it was 80 or 90 compatibility....you just limited their use in queries

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • SQLSandwiches

    SSCrazy

    Points: 2141

    Exactly the answer I was looking for. Thanks for explaining (and the fast reply). That helps a lot.

  • forsqlserver

    SSCoach

    Points: 18900

    Awesome response Lowell

    Thanks

  • Romah

    SSC Veteran

    Points: 268

    Yes, as Lowell said "a backup created on 2008 can only be restored on a 2008 instance"

    If you need to copy the database from sql server 2008 to sql server 2005, I found another way to copy the database by creating a script.

    Steps:

    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:

    Click "Script all objects in the selected database", and then click "Next."

    Change the following script options:

    set "Script for Server Version" to "SQL Server 2005"

    and set "Script Data" to "True".

    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"

    Select "Script to file"

    Select the file name

    and choose "Single file"

    Click "Next" for a summary

    Now click on "Finish" to get progress messages while the script runs and completes

    If the generation process fails, then you can use the "Report" option to see why.

    When the scripting is completed, look for the following lines:

    CREATE DATABASE [Northwind] ON PRIMARY

    (NAME = N'Northwind', FILENAME =

    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.mdf' ,

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

    LOG ON

    (NAME = N'Northwind_log', FILENAME =

    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.ldf' ,

    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    GO

    You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:

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

    --GO

    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.

    Thanks !!!

  • mpeters-878100

    Right there with Babe

    Points: 716

    While you guys are on the topic of copying a 2008 database to an older version, I am currently putting together a failover plan after upgrading from 2000 to 2008. I will be doing a side by side installation and will be keeping the 2000 server intact, however i'm not sure how to keep the 2000 server up to date so we can roll back to it in case we run into errors. We have a 10gb + database so importing and exporting data would tkae a long long time, an ideas?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Export/import, transactional replication, custom solution using Change Tracking or Change Data Capture. None of them are going to be pleasant. Backups cannot be restored to SQL 2000, that includes transaction logs for log shipping.

    Please in future start a new thread for a new question. Thanks.

    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
  • Rahul The Dba

    SSChasing Mays

    Points: 647

    romah (12/21/2009)


    Yes, as Lowell said "a backup created on 2008 can only be restored on a 2008 instance"

    If you need to copy the database from sql server 2008 to sql server 2005, I found another way to copy the database by creating a script.

    Steps:

    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:

    Click "Script all objects in the selected database", and then click "Next."

    Change the following script options:

    set "Script for Server Version" to "SQL Server 2005"

    and set "Script Data" to "True".

    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"

    Select "Script to file"

    Select the file name

    and choose "Single file"

    Click "Next" for a summary

    Now click on "Finish" to get progress messages while the script runs and completes

    If the generation process fails, then you can use the "Report" option to see why.

    When the scripting is completed, look for the following lines:

    CREATE DATABASE [Northwind] ON PRIMARY

    (NAME = N'Northwind', FILENAME =

    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.mdf' ,

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

    LOG ON

    (NAME = N'Northwind_log', FILENAME =

    N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATAorthwnd.ldf' ,

    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    GO

    You will need to amend the paths to a valid path for the computer on which you are running. You also will need to comment out the following lines like this:

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

    --GO

    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.

    Thanks !!!

    thanks a lot tbis one saved lot of time

    [font="Comic Sans MS"]Rahul:-P[/font]

  • jasonmorris

    SSCrazy

    Points: 2492

    Final 2 cents worth is that you cannot restore a SQL 2008 R2 database onto SQL 2008 which I think is a fun one.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Same reason. R2 is a different version, it has a different internal database version and hence cannot be downgraded. It is not a service pack.

    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
  • Sameer Narkar

    SSC Rookie

    Points: 35

    Well, its pretty simple.

    1. Instead of creating a backup. go to generate script wizard.

    2. select 'script entire database and all database objects' and click next

    3. Then click on advanced tab

    4. Under Advanced tab change script for server version from SQL Server 2008 R2 to SQL Server 2005 and

    5. Types of data to script from Schema only to Schema and Data...

    Now once the script is generated create a blank database and delete the portion where create database is written and run the script...

  • zabronm

    Newbie

    Points: 7

    Grasshoper: Thanks very much to you for this post, it really saved my day-long search, trial-n-errors.:-)

Viewing 15 posts - 1 through 15 (of 15 total)

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