Search
  • Filip Holub

Downgrade SQL Server edition by In-Place method!


As SQL Server Administrator you will probably sometimes solve downgrade edition of SQL Server, for example from Enterprise/Standard to Developer edition. There can be a couple of reasons why do that, in my case, it were Licenses. On your Development or UAT environment cannot install Enterprise/Standard, because SQL Server must be licensed. So, I did a few downgrades by InPlace downgrade, which can save a lot of time and work. You don't need to install a new server side-by-side a migrate all databases. In this post you find out do it:

Release note here:

1. Create All Databases Backup (User and System Database)

DECLARE @DBName VARCHAR(255)

DECLARE @PathForBackUp VARCHAR(255)

DECLARE @FileName VARCHAR(255)

DECLARE @DateFile VARCHAR(255)

DECLARE @SQL NVARCHAR(2048)

SET @PathForBackUp = '\\ShareFolder\d$\Backup\'

SET @DateFile = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120) ,' ','T'), ':','')

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases WHERE dbid != 2

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @FileName = @PathForBackUp + @DBName + '_' + @DateFile + '.BAK'

SET @SQL = 'BACKUP DATABASE '+@DBName+ ' TO DISK = '''+@FileName+''' WITH COPY_ONLY, COMPRESSION, CHECKSUM '

PRINT @SQL

EXECUTE sp_executesql @sql

FETCH NEXT FROM db_cursor INTO @DBName

END

CLOSE db_cursor

DEALLOCATE db_cursor

2. Backup SSRS,SSIS and SSAS

-- BACKUP SSRS Encryption key if exists (Manually via Report Server Configuration Manager or via rskeymgmt)

  • Run rskeymgmt.exe

  • rskeymgmt -e -f d:\rsdbkey.snk -p<password>

SSRS Encryption Keys - Back Up and Restore Encryption Keys

-- BACKUP SSIS Catalog, Database, Master key if exists

Backup, Restore, and Move the SSIS Catalog

-- BACKUP SSAS Cubes if exists

Backup and Restore of Analysis Services Databases

Check Collation of SSAS, can be different against DB Engine!

3. Check Enterprise Features and Check Version and Build

-- Check Enterprise Features ( Select should be display nothing )

SELECT * FROM sys.dm_db_persisted_sku_features

-- Check Version and Build and Save the Output

SELECT @@VERSION

-- Check Instance Name and Save the Output

SELECT @@SERVICENAME

-- Check Version and Build and Save the Output

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

-- Check Version and Build and Save the Output

SELECT * FROM sys.configurations

WHERE name LIKE '%stream%'

-- List current file location of all databases and Save the Output

SELECT name, physical_name AS current_file_location

FROM sys.master_files

WHERE database_id != 2

-- Infoimation about SQL Server Services Accounts and Save the Output

SELECT servicename, process_id, startup_type_desc, status_desc,

last_startup_time, service_account, is_clustered, cluster_nodename, [filename]

FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

-- Check Certifications and Keys and save backup

SELECT * FROM sys.asymmetric_keys

SELECT * FROM sys.certificates

SELECT * FROM sys.column_encryption_keys -- Since SQL Server 2016

SELECT * FROM sys.column_encryption_key_values -- Since SQL Server 2016

SELECT * FROM sys.column_master_keys -- Since SQL Server 2016

SELECT * FROM sys.crypt_properties

SELECT * FROM sys.cryptographic_providers

SELECT * FROM sys.dm_database_encryption_keys

SELECT * FROM sys.key_encryptions

SELECT * FROM sys.openkeys

SELECT * FROM sys.security_policies -- Since SQL Server 2016

SELECT * FROM sys.symmetric_keys

USE [master]

GO

SELECT D.name AS 'Database Name'

,c.name AS 'Cert Name'

,E.encryptor_type AS 'Type'

,CASE

WHEN E.encryption_state = 3 THEN 'Encrypted'

WHEN E.encryption_state = 2 THEN 'In Progress'

ELSE 'Not Encrypted'

end as state,

E.encryption_state, E.percent_complete, E.key_algorithm, E.key_length, E.* FROM sys.dm_database_encryption_keys E

right join sys.databases D on D.database_id = E.database_id

left join sys.certificates c ON E.encryptor_thumbprint=c.thumbprint

-- Select port number and Save the Output

SELECT DISTINCT local_tcp_port

FROM sys.dm_exec_connections

WHERE local_tcp_port IS NOT NULL

Or check port number via Configuration Manager, maybe better! Select sometimes show nothing even though port number exists!

Copy Root Directory Folder like below:

4. Shutdown the SQL Server and Copy System Database apart from TempDB

Copy master, model and msdb - Datafile(.mdf) and Log files(.ldf) to New Location for backup, because system databases will be removed after uninstallation from original folder!!!

5. Uninstall SQL Server

Can reserve installed!!! - Shared Components (Client Tools Connectivity, Client Tools backwards Compatibility, Client Tools SDK, Management Tools - Basic/Complet) and SQL Client Connectivity SDK )

6. Restart Server

7. Install Developer Edition and Patches

  • Make sure that Data and Log folder is set to correct location

  • Open SSMS and, Expand databases, Security,Logins, Jobs, etc… Should be looks like a fresh copy of SQL Server.

  • Check Version and Build and Save the Output

SELECT @@VERSION - Compared build number with build number of previous SQL Server and download correct patches/CU and install to same build as it was before!!! Build number must be same as previous SQL server!!!

Microsoft SQL Server Versions List

8. Shutdown the SQL server and copy the system database (Master, Model and MSDB)

  • Copy master, model and msdb - Datafile(.mdf) and Log files(.ldf) to original location where was installed.

9. Start SQL Server

Open management Studio, Expand databases, Security, Logins, Jobs, etc… Everything should look like before uninstallation!!!

10. Restore SSRS, SSIS and SSAS

-- RESTORE SSRS Encryption key if exists (Manually via Report Server Configuration Manager or via rskeymgmt)

  • Run rskeymgmt.exe

  • rskeymgmt -a -f d:\rsdbkey.snk -p<password>

SSRS Encryption Keys - Back Up and Restore Encryption Keys

-- BACKUP SSIS Catalog, Database, Master key if exists

Backup, Restore, and Move the SSIS Catalog

-- BACKUP SSAS Cubes if exists

Backup and Restore of Analysis Services Databases


0 views
  • Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon

© 2020 All rights reserved. PigeonSQL.com | Filip Holub

  • Twitter Social Icon
  • Facebook Social Icon
  • LinkedIn Social Icon