Transfer SQL Objects Task with compatibility level error.

Question

Post reply

Transfer SQL Objects Task with compatibility level error.

Duran

SSChampion

Points: 13259
More actions
November 23, 2010 at 4:46 am

#231584

Hello,
I am trying to move a database with its data and everything that goes with it to an instance of SQL 2008 R2. The reason I am doing this instead of using the copy database wizard is because the wizard fails as there are roles at database level which need to be created as well and copy database does not deal with those.
When I execute the SSIS take it fails with Error: Execution Failed with the the following error:"Version90 database compatibility level is not supported.".
I cannot upgrade the level of the 2005 database at this time, I read that if I put the compatibility level of the model database on the 2008 R2 machine it would work, it doesnt. And tried the same on the master database, but this also did not work either. I'm kind of stuck, is there any way around this?
As usual, any help of advice in most appreciated.
Kind Regards,
D.

Viewing 5 posts - 1 through 4 (of 4 total)

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

iaminyourhead SSC Eights! Points: 810 More actions · Answer 1

Restore your database on SQL 2008 instance and then change the compatibility level to 90

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

It sounds as if there's something in Visual Studio 2008 or Integration Services 2008 that is insisting on a SQL Server 2008 database. What is the task that is failing? Do you have a PC or server with Integration Services / Visual Studio 2005 on it, so that you can rewrite your package on that?

John

iaminyourhead SSC Eights! Points: 810 More actions · Answer 3

For me it's failing on transfer/copy database objects task.

Duran SSChampion Points: 13259 More actions · Answer 4

Thanks for getting back everyone,

I did try to restore the a backup to 2008 (which worked of course), I then tried to use the Transfer SQL Objects Task to copy across all the rest that I need, such as the schemas etc, and go the same error, I think all I can do is script everything out individually and then create them on the other instance.

But it basically means that transfer sql objects task is useless, unless the SQL versions (or the compatibility levels) are the same. And the copy database wizard is no good as it does not copy Roles. So near, yet so far.

Regards,

D.