November 4, 2015 at 7:53 am
Hi,
Hoping someone can help with this, I am currently having to downgrade my SQL Server in my test environment from Enterprise Edition to Standard Edition. I backed up all the databases that I wanted to keep as well as the system databases, installed Standard edition and then wanted to restore the master database this is where I have the issue and from what I have read I'm not the only one. I going to try and use this approach https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/ but I have already uninstalled my test environment so don't have the mdf and ldf to hand unless I some how restore the backup file to another server running the same version as previous.
I am was on this Version
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
And I want to go to this Version
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Can anyone offer any help of how I can achieve getting the master database from the Enterprise edition on to the Standard edition?
Thanks Phil
November 4, 2015 at 8:30 am
i was always under the impression that to downgrade, you have to uninstall and reinstall.
especially master and the hidden resource databases, which cannot be restored to any lower version service pack wise.
In your case, I think you want to go to the trouble of scripting out the things you want to keep in master so you can run the script to put them back: linked servers, logins,credentials, certificates, master keys etc, then you add each database individually to the newly installed instance.
it's a good practice run for disaster recovery, since that's really what it is.
Lowell
November 4, 2015 at 8:42 am
Hi Lowell,
To be honest that is what I thought until I started reading and some people have managed to do it so I thought I would give it a go, spent the whole day messing around with it and loosing the plot. I think I will go down the route as you suggested as it will make planning for my production downgrade easier to manage.
Thanks again for the reply, Phil
November 4, 2015 at 9:31 am
As an aside, and I hope you dont mind me asking, but why go for Standard and not Developer edition, which will have significant savings on Standard licensing, while still giving you the Enterprise features.
As to me a testing server is the same as a development box (not sure if MS has the same definitions though :-D)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 4, 2015 at 2:21 pm
Very good question 🙂 I don't believe you can use the developer edition in a production live environment. At the moment I am trying to replicate what I will have to do on my live server, on my test server so I can sort out all the bugs before going live 🙂 causing lots of trouble though......
November 4, 2015 at 4:06 pm
tress (11/4/2015)
Very good question 🙂 I don't believe you can use the developer edition in a production live environment. At the moment I am trying to replicate what I will have to do on my live server, on my test server so I can sort out all the bugs before going live 🙂 causing lots of trouble though......
It's a test server... not a production server. All you need to do is buy 1 license for each person using the test server and you can use the Developer's Edition. At 30-35$ a pop through a volume distributor, it won't even come close to the licensing required for even the Standard Edition.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2015 at 4:33 pm
tress (11/4/2015)
Hi,Hoping someone can help with this, I am currently having to downgrade my SQL Server in my test environment from Enterprise Edition to Standard Edition. I backed up all the databases that I wanted to keep as well as the system databases, installed Standard edition and then wanted to restore the master database this is where I have the issue and from what I have read I'm not the only one. I going to try and use this approach https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/ but I have already uninstalled my test environment so don't have the mdf and ldf to hand unless I some how restore the backup file to another server running the same version as previous.
I am was on this Version
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
And I want to go to this Version
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Can anyone offer any help of how I can achieve getting the master database from the Enterprise edition on to the Standard edition?
Thanks Phil
Don't restore system databases, especially across editions.
Script the objects out and apply them to the new instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 4, 2015 at 4:33 pm
Hi, we are already licenced for the standard edition by our head office so no extra costs and wanted to keep both my test and live environments as similar as possible.
Thanks Phil
November 4, 2015 at 4:36 pm
Hi Perry,
That's what I have decided to do, is it possible to script the passwords in anyway, unfortunately many of the accounts were created before my time and nobody kept a list of passwords and these accounts are now being used in other systems?
Thanks P
November 4, 2015 at 4:44 pm
Hi Perry,
Please scrap my last question, I have worked out how to do it 🙂
Thanks P
November 6, 2015 at 7:06 am
tress (11/4/2015)
Hi Perry,That's what I have decided to do, is it possible to script the passwords in anyway, unfortunately many of the accounts were created before my time and nobody kept a list of passwords and these accounts are now being used in other systems?
Thanks P
Yes, here's a quick script that I use myself to script sql logins and their passwords
SELECT'CREATE LOGIN [' + name + '] WITH PASSWORD = ' +
sys.fn_varbintohexstr(password_hash) +
' HASHED, SID = ' + sys.fn_varbintohexstr(sid) +
', DEFAULT_DATABASE = ' + QUOTENAME(default_database_name) +
', DEFAULT_LANGUAGE = ' + default_language_name +
', CHECK_EXPIRATION = ' +
CASE
WHEN is_expiration_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
', CHECK_POLICY = ' +
CASE
WHEN is_policy_checked = 0 THEN 'OFF'
ELSE 'ON'
END +
CASE is_disabled
WHEN 0 THEN ''
ELSE '; ALTER LOGIN [' + name + '][ DISABLE;'
END
FROM master.sys.sql_logins
WHERE name not like '##%' and name <> 'sa'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply