Peter Larsson Blog

Patron Saint of Lost Yaks

Curiosity about SQL Server 2008 R2 installation

I installed SQL Server Enterprise 2008 R2 on my laptop as a new instance. I also have SQL Server Developer 2008 as default instance.

For the default instance, I have a database named Test on a separate partition of my laptop harddrive.

After installing R2, I tried to attach the Test database, and failed, because I didn't detach the database from the default instance.

So now I detached the Test database from the default instance but didn't make it because it was marked as suspect, even if the icon displayed good. So I took the Test database Offline and then detached it (there is nothing important in the database and I have backup). Now I tried to attach the database to the default instance just to make sure everything is ok, but got an error "Error 5: Failed to attached". It seems to be a OS permission level!

I switched to the new R2 instance and tried to attach the database there, and to my surprise it worked!

Isn't it strange the default instance denies access of file with Error 5, the new R2 instance accepts the database attach? Both instances should be able to do attach, right?

After some fiddling with the OS file permissions, I get a more descriptive error message from the default instance:

The database 'Test' cannot be opened because it is version 660. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database 'Test'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

I verified this by executing "SELECT * FROM SYSDATABASES".
So the internal version number has changed, but the compatibility level is still the same (100). It would have been better if R2 instances had a compatibility level of 105, for more visual distinguishment.