March 17, 2010 at 8:08 am
I am asked to downgrade an entire instance with several user databases from the current environment of SQL-2008 EE 64-bit down to sql-2005 EE 32-bit.
the detach/attach did not work.
The error is "the database cannot be opened because it is version 655. This server support version 611 and earlier. A downgrade path is not supported"
the backup/restore did not work either.
the error is "the media family ... is incorrectly formed. SQL Server cannot process this media family"
Is there a way to accomplish this? Thanks.
March 17, 2010 at 8:19 am
Its not issue of 32 or 64 bit.
You can not restore 2008 database on 2005 server.
You can try doing one thing-
Change database compatibility level to 2005, then take back up and then try to restore it on 2005.
This wont work if you are using SQL Server 2008 specific features
March 17, 2010 at 8:41 am
Nope, that won't work. Compatibility mode has nothing to do with the database version.
You cannot restore back to SQL 2005 from 2008. Your only option is to script all of the schema's from the 2008 databases into SQL 2005 equivalents and then import the data using something like SSIS.
You could reduce the amount of leg work by using a 3rd party tool to compare and sync schema and data between databases. There are several vendors that produce software of this type including Idera and Red-Gate.
March 17, 2010 at 8:55 am
As mentioned, you can't go backwards. It's not the 32 v 64, it's the versions of the software. The compatibility level of the database doesn't matter.
The way you do this is
- script all schema and objects, run on the 2005 instance
- script all permissions, apply to 2005 server
- export all data, import into 2005 instance
You can do this yourself, but you might have FK issues, and it will take time. SQL Compare/Data Compare from Red Gate (I work for them), or any other set of compare tools, are likely worth the money if your time is valuable to get this done. However it's not a hard process, just complicated. It just has a lot of things to manage.
March 17, 2010 at 9:16 am
Thank you all for your responses.
I found those out too, going through each of them.
luckily, I have and will try the Red Gate X-SQL bundle.
Hopefully, it will save me some time.
again, thanks.
This is a wonderful site.
March 17, 2010 at 6:47 pm
I have to ask why this requirement to downgrade the system? Is this for a customer that has not upgraded yet or is there some other reason why you have to do this?
Normally, changing the compatibility mode will allow the clients and code to run successfully. Is that not an option here?
Just curious as to the reasons behind having to do this.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 17, 2010 at 7:27 pm
this is for a customer that is anxious to try sql2008, but the app is not quite 100% upgradable yet.
March 17, 2010 at 7:31 pm
tvu4251 (3/17/2010)
this is for a customer that is anxious to try sql2008, but the app is not quite 100% upgradable yet.
So, one of your customers upgraded to SQL Server 2008 and broke the application? I get that - so, are you charging them a lot of money to fix the problem? :w00t:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 17, 2010 at 10:17 pm
Jeffrey Williams-493691 (3/17/2010)
tvu4251 (3/17/2010)
this is for a customer that is anxious to try sql2008, but the app is not quite 100% upgradable yet.So, one of your customers upgraded to SQL Server 2008 and broke the application? I get that - so, are you charging them a lot of money to fix the problem? :w00t:
I would sure hope so.:w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 10:37 pm
tvu4251 (3/17/2010)
Thank you all for your responses.I found those out too, going through each of them.
luckily, I have and will try the Red Gate X-SQL bundle.
Hopefully, it will save me some time.
again, thanks.
This is a wonderful site.
The SQL Compare will be pretty quick. The data compare can get bogged down and be pretty slow.
Just an FYI.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 24, 2010 at 11:51 am
if you script this yourself just remember to disable or remove any foreign keys, triggers, etc first. Create the base schema (Tables) and load all the data then apply any referential objects, triggers, etc afterwards.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply