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 need to take a copy of a SQL Server database. The database has approx 40 views that link to other databases, and I need sample data from those views too. Also, the views are encrypted so when I try and generate a script from one of the views this is the error:

Property TextHeader is not available for View '[dbo].[TABLE1]'. This property may not 
exist for this object, or may not be retrievable due to insufficient access rights.  The 
text is encrypted. (Microsoft.SqlServer.Smo)

What would be the best way to get a copy of this database containing data from the views? I was thinking about creating extra tables and populating with sample data but that seems long-winded, so is there a better way?

share|improve this question
    
What version of SQL Server are you using for this database? –  Jeff Jun 10 '11 at 15:15
add comment

3 Answers 3

Here is one more solution that works on SQL Server 2012. You'll get the body of the view in the results.

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

HTH

share|improve this answer
add comment

Encrypted stored procs and views can be decrypted pretty easily:

http://www.mssqltips.com/tip.asp?tip=1046

That'll give you the unencrypted text of the stored procedures and views. Just change the CREATE statement to be an ALTER, and it'll overwrite the encrypted versions with decrypted ones.

Don't do this in production - do it in a restored backup of the database. That way if it's a third party vendor app, you'll still be supported in production.

share|improve this answer
3  
Applies to SQL Server 2000 and earlier. It's tricker with SQL Server 2005+. And the tools linked also are obsolete or non-existent –  gbn Aug 7 '11 at 15:12
add comment

A view is only a metadata definition (if it's not an indexed view, of course). So copying the database would get you only the encrypted definition of that view.

I think that your best bet is to create new tables with current data from those views and so you'll have your data sample.

share|improve this answer
add comment

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.