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.

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: SQL Server FineBuild1-click install and best practice configuration of SQL Server 2016, 2014, 2012, 2008 R2, 2008 and 2005.29 Aug 2016: now over 38,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: "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

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--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!

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--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!