Take the 2-minute tour ×
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.

I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.

Neither do I have older 2005 backup of the database.

I do not have decrypted procedures to restore manually.

Is there any way to just copy/move them over?

share|improve this question

migrated from stackoverflow.com Aug 4 '12 at 14:52

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

2 Answers 2

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:

CREATE PROCEDURE dbo.ShowDecrypted(@ProcName SYSNAME = NULL)
AS
--Jon Gurgul 27/09/2010
--Adapted idea/code from shoeboy/joseph gama
SET NOCOUNT ON
IF EXISTS 
(
SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e 
on (ec.[endpoint_id]=e.[endpoint_id]) 
WHERE e.[name]='Dedicated Admin Connection' 
AND ec.[session_id] = @@SPID
)
BEGIN

DECLARE @i BIGINT,@a NVARCHAR(MAX),@b NVARCHAR(MAX),@d NVARCHAR(MAX),@c NVARCHAR(MAX)
SET @a=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1)
SET @b='ALTER PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS '+REPLICATE('-', 8000)

    BEGIN TRANSACTION
        EXECUTE (@b)
        SET @c=(SELECT [imageval] FROM [sys].[sysobjvalues] WHERE [objid] = OBJECT_ID(@ProcName) and [valclass] = 1 and [subobjid] = 1) 
    ROLLBACK TRANSACTION

SET @d = REPLICATE(N'A', (DATALENGTH(@a) /2 ))
SET @i=1
WHILE @i<=(DATALENGTH(@a)/2)
BEGIN
SET @d = STUFF(@d, @i, 1,NCHAR(UNICODE(SUBSTRING(@a, @i, 1)) ^(UNICODE(SUBSTRING('CREATE PROCEDURE '+ @ProcName +' WITH ENCRYPTION AS ' + REPLICATE('-', 8000), @i, 1)) ^UNICODE(SUBSTRING(@c, @i, 1)))))
SET @i=@i+1
END

SELECT @d [StoredProcedure]

END
ELSE
BEGIN
    PRINT 'Use a DAC Connection'
END

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The original article where this can be found is http://social.msdn.microsoft.com/Forums/en/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330

I hope this is of help to you.

share|improve this answer
    
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. –  Agony Aug 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. –  Agony Aug 5 '12 at 12:26

If you have the database running in a SQL Server instance today, why not just backup the database and restore it to the new instance? Then everything will come across as expected.

share|improve this answer
    
Seems like the source is 2008 and the destination is 2005, so backup / restore is not an option. –  Aaron Bertrand Aug 5 '12 at 16:37
    
Ah, didn't catch that in the question. Never mind then. –  mrdenny Aug 5 '12 at 17:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.