Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Saturday, 22 April 2017

Downgrade of Enterprise Edition to Standard Edition Conversion DEMO in SQL Server


Ref:
https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/

Demo:

Now we are going to install SQL Standard Edition from setup file as see below


Now we give named instance name as see below


you can see Server version info and db info  and log info and see below




database settings info as see below



db path info as see below


system db info as see below


system db path info as see below


User database path info as see below




Now we are going to take s p_help rev login script as see below

first we need to create s p_help rev login 



now we scripted out s p_help rev login on Enterprise Edition ( source) as see below


now we are going to take linked server so we can see linked server source info as see below




Linked servers are working fine


Job and maintenance plan also working fine 



Now we need to script out as see below







we are taken into separate folder of screen shots and scripted as see below



SQL Backup Job info





Now we need to take backup to another location




we should need to stop SQL Services to copy system db to other location

Now we are going to copy system db to another location as see below

system db location of instance







I moved system db to another location




Uninstall SQL Server from Control Panel





You only need to uninstall the Instance (includes SS IS, SS AS, SS RS, SS D T). There is no need to uninstall the Shared Components (SS MS, Client Tools, and Connectivity).





SQL 2012 ENTERPRISE EDITION Removed successfully


you can see system db removed from default location that s why we moved to another location



but you can see user database and temp db did not remove from default location as see below


Now Installing SQL Standard Edition















so we need to point it into correct location  or  we need to create folder



there is no such folder exists so we need to create it to solve this issue

now it success and going for next step as see below














old location user database 


Now we are going to move user database to new location













if we use copy restore or M D F/L D F Attach detach method it will work fine

we can analyze event viewer to see error 

RUN command type EVENTVWR



After we moved file to above path we can start services




also we changed log on to local to take effect
















if you see any db in recovery pending failed we need to alert to correct path

USE master;
GO
ALTER DATABASE databasename
MODIFY FILE (NAME = dbdev, FILENAME = 'D:\DBSource\db.mdf');
GO
ALTER DATABASE databasename
MODIFY FILE (NAME = dbplog, FILENAME = 'D:\DBSource\dblog.ldf');



so now it all seems to be fine

we successfully downgraded edition