Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question came from our site for professional and enthusiast programmers.

1

A quick search should yield plenty of solutions for decrypting your objects. You won't be able to copy them over as is, but you can certainly extract the scripts for them and then re-create them on the new server that way. Oh, and are you really downgrading from 2008 to 2005? Why?
–
Aaron Bertrand♦Aug 4 '12 at 16:14

I ran into this problem a few years back myself. However because SQL Server doesn't actually "encrypt" the objects that you create (it obfuscates them) you can quite easily reverse the process of obfuscation to get the definition back.I use the following procedure to script out encrypted objects:

I couldn't get this one to work on SQL 2008 or SQL 2012 (@d is always NULL), but the other one posted by the same person, in that same thread, and also proposed as an answer, did work on both versions.
–
Aaron Bertrand♦Aug 4 '12 at 18:16

I did some digging around based of the "obfuscation" info and found a extremely useful tool called: dbForge SQL Decryptor Essentially it uses GUI interface similar to SSMS and can batch decrypt and replace all encrypted procedures, views etc. I had a few that didn't decrypt due to diff naming (eg instead of dbo. they are ncash.) so had to convert and replace manually, only 4 tho.
–
AgonyAug 5 '12 at 2:09

@AaronBertrand I believe that you have to use the DAC to get that procedure to work under SQL 2008 or higher.
–
mrdenny♦Aug 5 '12 at 3:19

@mrdenny yep, I'm using the DAC, did you try it? If I weren't using the DAC I'd just get the PRINT output.
–
Aaron Bertrand♦Aug 5 '12 at 3:51

the tool i posted doesnt need DAC. It uses SSMS style interface to login to ur instance , then ether open them up and remake them in the ssms/sqlcmd or use the replace option, that decrypts and replaces them automatically.
–
AgonyAug 5 '12 at 12:26