Restoring SQL 2008 to SQL 2005

Question

Restoring SQL 2008 to SQL 2005

jai_sl2000

SSC Veteran

Points: 284
More actions
May 5, 2009 at 5:07 am

#204016

Hi All,
Is it possible restore database SQL 2008 backup to SQL 2005?. I try to restore sql2008 backup to SQL 2005 server, but it failed.

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

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

You can't directly restore a 2008 database to 2005, but you can use the generate scripts wizard to restore a 2008 database on 2005. Here is an article that describes how to do it.

http://www.devx.com/dbzone/Article/40531

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

There's no way to downgrade database versions. A DB on SQL 2008 has a slightly different file structure and different system tables, and that's regardless of the compatibility level. SQL 2005 does not understand that structure and cannot restore or attach a 2008 database

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

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 3

We cant downgrade the database versions.

We can generate the script and excute on the lower versions

- Pradyothana http://www.msqlserver.com

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

jai_sl2000 (5/5/2009)
Hi All,
Is it possible restore database SQL 2008 backup to SQL 2005?. I try to restore sql2008 backup to SQL 2005 server, but it failed.

AS others have suggested u cannot downgrade a database,, even if u get to change the compaibility level you are welcoming troubles. Same thing with 2005 to 2000 too..

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

Ken Simmons-546488 (5/5/2009)
You can't directly restore a 2008 database to 2005, but you can use the generate scripts wizard to restore a 2008 database on 2005. Here is an article that describes how to do it.
http://www.devx.com/dbzone/Article/40531

I had tried scripting a database and the script file was 5GB.And when I tried to execute the script in management studio it seemed to have crashed:-(

Dugi SSCoach Points: 17998 More actions · Answer 6

chandan_jha18 (1/17/2010)
Ken Simmons-546488 (5/5/2009)
You can't directly restore a 2008 database to 2005, but you can use the generate scripts wizard to restore a 2008 database on 2005. Here is an article that describes how to do it.
http://www.devx.com/dbzone/Article/40531
I had tried scripting a database and the script file was 5GB.And when I tried to execute the script in management studio it seemed to have crashed:-(

As you can see from the posts above no downgrade the database version! Except scripting, try to use Export/Import tool to export all objects from SQL Server 2008 to SQL Server 2005 and let me know if it works!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

chandan_jha18 SSChampion Points: 11330 More actions · Answer 7

I tried executing the script from the command line utility and it works well:-)

I have a question about import and export wizard.Does it copies all the objects in the database(functions,stored procs etc.,schemas) and does it create any issues if there are primary and foreign keys in the source?

Dugi SSCoach Points: 17998 More actions · Answer 8

chandan_jha18 (1/18/2010)
I tried executing the script from the command line utility and it works well:-)
I have a question about import and export wizard.Does it copies all the objects in the database(functions,stored procs etc.,schemas) and does it create any issues if there are primary and foreign keys in the source?

Well, you have found the solution, so about import and export wizard using SSMS - it will copy (Tables and Views) in the database. I thought that you need your data and this is one way how to take data from SQL Server 2008 to SQL Server 2005, and sure there will be the issue with you PKey and FKey, but you can avoid it during the export / import wizard because you have possibility to edit SQL and put the PKeys, FKeys, Identities or the script of your tables one by one. I think if you use the SSIS using the Visual Studio you have more possibility for export / import all objects and all relations between them including PKeys, FKeys, UDF, SPs, Sch etc.

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

chandan_jha18 SSChampion Points: 11330 More actions · Answer 9

Thanks for your advice.I guess by scripting the table structures from source db,one can spend sometime and create them in the new tables.Its time consuming but acceptable given the strange things we encounter while moving from 2008 to previous versions.Thanks:-)

Dugi SSCoach Points: 17998 More actions · Answer 10

chandan_jha18 (1/18/2010)
Thanks for your advice.I guess by scripting the table structures from source db,one can spend sometime and create them in the new tables.Its time consuming but acceptable given the strange things we encounter while moving from 2008 to previous versions.Thanks:-)

Yes, every time we are looking forward for the new version and how to do the upgrade, but as you can see we can have situation that we should go downgrade with DB version and problems are there, but sure that they have solution!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

homebrew01 SSC Guru Points: 55544 More actions · Answer 11

chandan_jha18 (1/17/2010)
Ken Simmons-546488 (5/5/2009)
You can't directly restore a 2008 database to 2005, but you can use the generate scripts wizard to restore a 2008 database on 2005. Here is an article that describes how to do it.
http://www.devx.com/dbzone/Article/40531
I had tried scripting a database and the script file was 5GB.And when I tried to execute the script in management studio it seemed to have crashed:-(

What method of script did you do ? 5GB is very big ? You can use the export wizard to select all the tables and push the data to the other server. Perhaps you should create 1 scripts for all the stored procedures, another for all the views ....