How to downgrade SQL Server 2008 R2 from Enterprise Edition to Developer Edition?

We had few Non-Production servers which were running on Enterprise editions of SQL Server 2008/SQL Server 2008 R2. For obvious reasons we had to downgrade these editions to developer editions of respective versions. Unlike adding new features to existing instance or upgrading existing instance from 2008 to R2 or upgrading from Standard to Enterprise edition there is no direct way(atleast I have not found) to perform a downgrade. Yes that’s true there is no direct way of doing it.

Though I have learnt 2 ways of doing edition downgrade: one is the mostly followed and lengthy processes and second one is less-time and less-known method.

First Method: You can try this for your first time:

This is a complete removal of existing instance and re-installing the instance of required edition. Most of the blogs\articles\forums suggest\prefer this.

How to do it?

First of all, as a first responsibility of every DBA what you do? Yes, take backup of all databases(User DB’s and System DB’s).

1.    Make copy of current mdf\ldf files of all system databases to other location.

2.    Make note of current version of SQL server instance like Service Packs, Hot Fixes, CU# installed.

3.    Script out all the server level permissions of logins.

4.    Script out all database level permissions of users.

5.    Make note of all SQL users credentials like username and passwords.

6.    Make note of Network configuration details like TCP\IP, VIA so on and port number of the SQL instance. You can get it from SQL Configuration manager.

7.    Account details on which SQL Services are running.

8.    In addition you can make a note of mdf\ldf file locations of all databases(system and user). This is required if your databases are spread across different drives.

9.    Also make note of enterprise features that are being used. (Anyway you can’t use them anymore J)

Now go to control panel and un-install the instance let’s say I want to downgrade my ‘Tester’ instance from Enterprise to Developer. So un-install Tester instance completely from the machine using Add\Remove programs of Control panel. Once un-installation completes reboot the server, once server is back verify and make sure instance got removed successfully.

Now place the CD of Developer edition and start installing. Now give the same instance name and if you have DB’s spread across different drives you can specify those file locations while installation, use the details you took in Point 8. Also configure the account for the services during installation itself with the details you have in Point 7. Also add yourself as admin while installation. Then proceed clicking Next and also mainly make sure you are installing only the features which were being used\installed earlier. Once installation completes, install all the required Service packs, CU# and hot fixes required to bring the edition to same version number as earlier(we took in Point 2) .

After bringing the newly installed Tester instance to same version number as point 2, stop the SQL services and replace the mdf\ldf files of master and msdb databases of the new instance with the mdf\ldf files of old instance(we took copy in Point 1). Once after replacing the master and msdb mdf\ldf files, start the sql services.

Now verify the instance and you should be able to see all the jobs and logins back. Now you have to restore all the user databases to the new instance in fact you can attach them back if you have taken mdf\ldf file copies of user databases already. Configure all the logins\users and their permissions(Step 3 and 4). Then configure configuration manager same as previous instance(Step 6 and 7).

Now re-start the SQL services and verify if everything is working as expected. Also confirm now you have same instance running on Developer edition with same version number as earlier.

Note:

                                                                                                                                                                                          

·         You need to replace mdf\ldf files of ‘model’ DB as well if your model DB is customized.

·         After re-installation VERSION number should be same as previous.

Second Method:

Why I mentioned this method as less-known because I have not found any blog mentioning they tried this method. Only place I found is in the comments of this link.

The method says making the registry changes will downgrade the edition without need to re-installation. I have to downgrade anyways in non-production environment so thought of trying this and IT WORKED!

Even though it worked for me, every time I try this I make sure I have the backups of all DB’s and above mentioned backups from Step 1 to 9.

Once you have backups, go to registry.

1.    Type regedit.msc

2.    Got to SQL installation section in registry by default it will be here:
HKLM(HKEY_LOCAL_MACHINE) -> SOFTWARE -> Microsoft -> Microsoft SQL Server ->

3.    Here you have to select the instance you want to downgrade if you have multiple instances.

Expand required instance folder.

Eg: In my case it is ‘Tester’ instance so

MSSQL10_50.Tester -> Setup

4.    Click on ‘Setup’

5.    Here You need to change one by one all below entries.

AGTGroup

DigitalProductID

Edition

EditionType

ProductCode

ProductID

SQLGroup

FDHostGroup

Checksum

Take these values from any already installed Developer edition instance and replace here. Make sure from where you are taking these values have the same features installed and same version and all.

6.    Now click on ‘SQL_Engine_Core_Inst’ and change the entries here as well.

·         ProductCode

7.    Now re-start the SQL Services and verify everything is in place and working as normal.

That’s it you are done.

Note:

·         Second method requires registry changes so please take backup of registry before making any changes.

·         Most important do not try this in your sensitive servers unless you tried it once in your less critical environments and it worked.

·         Make sure you have backup of everything.

     DO NOT MAKE ANY CHANGES TO THE REGISTRY WITHOUT BEING SURE WHAT YOU ARE DOING!!

Thanks!!

How to Un-install a clustered instance from SQL Server 2008 R2 cluster?

We have a 2 node cluster(Active/Passive) and has 2 SQL Server 2008 R2 instances running. We need to Un-install one of the running instance from this cluster.

Normal SQL Server Un-installation we can do directly from Add/Remove programs of Windows server but to perform the same in cluster environment is bit different.

For removing clustered instance first we need to copy the set up files of SQL Server to local drive of Passive node and run ‘Setup.exe’.

Select ‘Maintenance’ option in the below window and click on ‘Remove node from a SQL Server failover cluster’

 this will start ‘Setup Support Rules’ window:

This will verify all the required rules and will give detailed report:

If any failures noticed, need to fix them before proceeding further. 

Next window will show available cluster nodes and SQL instances currently running:

Make sure correct instance is selected in ‘SQL Server instance name’ section. We need to select the instance (in the highlighted section below) name which we want to remove from cluster 

after clicking on next it will shows us the currently installed features of selected instance:

To start removal of instance click on ‘Remove’ button. Removal process will start:

Once instance removal completes, re-start the passive node. Now login to the active node and follow the above same steps. After removing the instance from Active node we need to reboot the active node as well to complete the Un-installation.

Note: Before rebooting current Active node make sure to failover the other running instance of active node to the passive.

Thanks!!

How to take PARTIAL or Filegroup backup? How to perform restore of PARTIAL backup file?

In SQL Server we have an option of taking backup of only certain part of the database, something like if we have a database which has multiple file groups and we want to take backup of only a single FileGroup that can be achieved. It is termed as Filegroup backup or Partial backup.

This kind of backups are needed in situations like if we have a large table in separate file group of production database and that table is not required to be available in test environment, so to save space in test environment and to reduce backup/restore times we can only take the backup of required file group and restore it to test environment.

FILEGROUP BACKUP:

BACKUP DATABASE AdventureWorks2012

FILEGROUP = 'PRIMARY'

TO DISK = 'C:\SQL2K8\Backups\AdventureWorks2012.bak'

WITH INIT,COMPRESSION

The backup command is almost same as normal backup command it will have section named ‘FILEGROUP’, in this section we need to mention the file group name which we want to backup. With this command SQL engine will back up only the file group name we specify, in above case only PRIMARY file group will be backed up.

PARTIAL RESTORE:

RESTORE DATABASE AdventureWorks2012_Test

FROM DISK = 'C:\SQL2K8\Backups\AdventureWorks2012.bak'

WITH PARTIAL,

MOVE 'AdventureWorks2012_Data' TO 'C:\SQL2K8\Backups\AdventureWorks2012_Data.mdf',

MOVE 'AdventureWorks2012_Log' TO 'C:\SQL2K8\Backups\AdventureWorks2012_log.ldf',

RECOVERY,REPLACE,STATS=10

While restoring the PARTIAL backups the only difference from normal restore command is we need to specify the option ‘PARTIAL’ in the restore command and rest of the command is same.

Points To Remember:

·        Database should be in FULL recovery in order to perform PARTIAL backup.

·        In partial restore one thing to note is, if the database has 2 file groups like PRIMARY and SECONDARY and we restored a PARTIAL backup which has backup of only PRIMARY file group, after restore through GUI we can still see the SECONDARY file group which was non-existent in backup file we used. But if we try to query the objects of that FG it throws error. Explained in below captures. So, even though we can see the FG through GUI it doesn't exist physically.

Performed a PARTIAL restore as shown in above capture using backup file which has only PRIMARY file group backup.

After restore when I verified properties of database it still shows SECONDARY FG.

Now I try to query the ‘SalesOrderDetail’ table which resides in SECONDARY FG, as the FG is not part of the partial backup we used the query throws below error:

Thanks!!