Setup
I found this error while I was following the process to change the edition of a database server from Enterprise to Developer Edition.
The process I was following is described in SQL SERVER – How to Downgrade SQL Server Edition?
Although it is not the exact situation as described there but it was really helpful.
So summarizing and for your convenience here you have the steps I followed:
- Make sure you are not using any feature which is not there in destination edition. This can be done by using catalog view dm_db_persisted_sku_fearures. This should be run on each database to find features.
- Backup ALL the databases before uninstall.
- Make a note of select @@version, sp_configure
- Uninstall the SQL server enterprise edition and install the developer edition. While installing the new edition I changed the instance name.
- Ran select @@version, sp_configure and got a small difference. CLR was not enabled so I ran the next script to enable it.
sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
sp_configure 'clr enabled',1
GO
RECONFIGURE
GO
- The next step is to attach the already existing databases and here is where the problems come:
Confrontation
I was suspecting that the previous SQL Server installation was owning these files so next logical step is to take ownership of those files and have full access to them.
To make this task easier I used the command line as administrator.
To take ownership
takeown /F *.*
And to give me full access to those files
icacls * /grant:r DOMAIN\myuser:F
Conclusion
After run the above commands I went to SQL Server Management Studio and using the menu I could attach all the databases successfully.