March 13, 2012 at 7:25 am
I was given a SQL Server 2008 "bak" file.... it would not restore to SQL Server 2005. I then tried to restore to SQL Server 2008 to see if it were from a SQL Server 2008 backup. It restored with no issues. Unfortunately, the restore needs to be on a SQL Server 2005 instance on our cluster. I tried to use an SSIS package, but it complained about the versions. I think tried Export/Import.... still received version errors. I then tried to generate a create script. Still have issues. Can a SQL Server 2008 backup be restored to a SQL Server 2005 instance? Thank you in advance for any information.
Charlie
March 13, 2012 at 7:34 am
Hi Charlie, no. You will have to migrate the structure and data from the 08 to a 05 database.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 23, 2012 at 8:08 am
In SQL Management Studio, right click on the database, click Tasks and then Generate scripts.
Depending on the complexity of your database, generate scripts for tables, views and UDF's first, stored procedures second.
Then generate separate scripts for the data.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 8:26 am
Robin Sasson (4/23/2012)
Then generate separate scripts for the data.
BWAAA-HAAAA! That's a bit like saying "It's easy to get to the moon. Just build a rocket ship." π
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 8:27 am
rummings (3/13/2012)
I was given a SQL Server 2008 "bak" file.... it would not restore to SQL Server 2005. I then tried to restore to SQL Server 2008 to see if it were from a SQL Server 2008 backup. It restored with no issues. Unfortunately, the restore needs to be on a SQL Server 2005 instance on our cluster. I tried to use an SSIS package, but it complained about the versions. I think tried Export/Import.... still received version errors. I then tried to generate a create script. Still have issues. Can a SQL Server 2008 backup be restored to a SQL Server 2005 instance? Thank you in advance for any information.Charlie
How big is the database and how many tables do you have?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 8:29 am
Ok, yes Jeff you have a point but at least its a starting point.:hehe:
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
April 23, 2012 at 8:52 am
April 23, 2012 at 8:55 am
bitbucket-25253 (4/23/2012)
ALTER DATABASE SET SINGLE_USER
Change the compatibility level of the database. -- 2005 level is 90
Put the database in multiuser access mode by using
ALTER DATABASE SET MULTI_USER
BACK UP the altered DB
Attempt to restore the BACK UP of the altered DB to SQL 2005
Will not work.
Compatibility level solely controls how the query processor treats some T-SQL constructs.
A database attached to a SQL 2008 server is a SQL 2008 database and cannot be restored or attached to a lower version.
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
April 24, 2012 at 12:57 am
rummings (3/13/2012)
I tried to use an SSIS package, but it complained about the versions. I think tried Export/Import.... still received version errors. I then tried to generate a create script. Still have issues. Can a SQL Server 2008 backup be restored to a SQL Server 2005 instance?
No you can't restore it, but you could use SMO to move the database objects from 2008 to 2005, google should help you here.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" π
April 25, 2012 at 8:48 am
Alas and alack, a day late and a dollar short, but I ran across this article today, and it might be what is needed.
The author is: By: K. Brian Kelley, and the article shows how to use SSMS to accomplish the task ...
April 29, 2012 at 6:58 am
Its faded memory, I think I did it with Copy Database task in SSMS. I donβt have SS2K5 to test it now, please give it a try. It would be lot easier than Export / Import Data Task.
As a side note, please donβt select detach-attach method (default) in Copy Database wizard.
April 29, 2012 at 7:09 am
Dev (4/29/2012)
It would be lot easier than Export / Import Data Task.
huh, how do you work that out, they both have the same end result, execute an SSIS package!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" π
April 29, 2012 at 7:22 am
Because you don't need to select individual tables in copy database command. Also, it copies everything that belongs to database, not just tables / views and data.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply