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>
-- BACKUP SSIS Catalog, Database, Master key if exists
-- BACKUP SSAS Cubes if exists
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!!!
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>
-- BACKUP SSIS Catalog, Database, Master key if exists
-- BACKUP SSAS Cubes if exists