Downgrade from 2008R2 to 2008

Question

Downgrade from 2008R2 to 2008

Ant-Green

SSC Guru

Points: 113445
More actions
September 4, 2012 at 2:57 am

#281066

Hi All
Having an issue when downgrading from 2008R2 to 2008.
Using the copy database wizard to perform the copy works well for all bar 4 databases. The reason, all stored procedures, functions, etc etc are all encrypted so the CDW cannot read the definition to be able to recreate the objects on the other side.
Is there a way to easily decrypt all objects in a DB or a way to get the DB's over from R2 to 2008 without having to go in on the DAC.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

EdVassie SSC Guru Points: 60445 More actions · Answer 1

To decrypt your 'encrypted' SPs and views, google 'sql server decrypt stored procedure' and take your pick.

You might want to challenge why the daatbase objects are encrypted. It offers next to no protection over finding out what the source text is. Its main function seems to be to make database maintenance operations more complex than they need to be.

You may also want to challenge why the target system needs to be SQL 2008, and get a justification why it cannot be upgraded to SQL 2008 R2 to match the source system. All the extra work you are having to do is costing your organisation, and a good way to eliminate these costs is to get the SQL versions matching.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

Thanks Ed, this is a Dev server which was upgraded to R2 for a reason which no one knows, so to get Dev / Test / Live all in sync we are downgrading Dev to 2008 as everything else in the organisation is 2008.

The DB's in question are the aspdotnetstorefront databases and they are provided to you in the scipts as encrypted, the person who put the DB's in has now left and no one remembers the password to login and get the scripts so that they can be recreated without the encryption clause.

Lowell SSC Guru Points: 323504 More actions · Answer 3

Anthony I've seen executables that decrypt without having to go into the DAC, but When I did the research once before, they seemed to be crippleware to try and get money from you.

I participated in a different thread that did require the DAC to get in, and put some screenshots there, and the link to the Decryptor procedure in this thread:

http://www.sqlservercentral.com/Forums/Topic1238608-100-1.aspx

not sure if that helps, though, since you wanted to avoid the DAC.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

Thanks Lowell, tried them procedures but they fail with an error, cant remember which, will try again and post.

Want to try and avoide DAC as it kicks everyone out and we are on a but of a tight schedule to get a piece of work out the door so cant impare the developers at the moment.

As all environments share the same schema, for now I have just backed up and restored test onto dev for the DB's in question and used the import export wizard to create a extract package to pump the data from R2 to R1.

If there are problems with the above looks like I will have to put in a few late nights using DAC to script out the objects.

Lowell SSC Guru Points: 323504 More actions · Answer 5

ok just downloaded this freeware :

http://download.cnet.com/Optillect-SQL-Decryptor/3000-10254_4-75300271.html

i installed it and it decrypts perfectly, without needing the DAC.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!