restoring a backup from sql server 2008 to 2005

Question

restoring a backup from sql server 2008 to 2005

AmitCBR

SSC Veteran

Points: 262
More actions
October 12, 2008 at 7:00 am

#383041

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 ?

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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 Veteran Points: 262 More actions · Answer 2

Thanks.

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

Amit

SQLSandwiches SSCrazy Points: 2143 More actions · Answer 3

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: 323504 More actions · Answer 4

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: 2143 More actions · Answer 5

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

forsqlserver SSCoach Points: 18902 More actions · Answer 6

forsqlserver

SSCoach

Points: 18902

March 4, 2009 at 10:55 pm

#953792

Awesome response Lowell

Thanks

Romah Old Hand Points: 368 More actions · Answer 7

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 More actions · Answer 8

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: 1004504 More actions · Answer 9

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 More actions · Answer 10

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 More actions · Answer 11

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: 1004504 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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