Sql-server – Truncated copy of a DB for a developer

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.

Transaction log is not written along with full backup ?

This is not correct. In FULL recovery mode – the database backup contains all the log necessary to make the restored database transactionally consistent as of the end of the backup operation.

Refer myths around full database backups from Paul Randal. Below is an excerpt

*A full database backup, when restored either explicitly using WITH RECOVERY, or when a recovery option is not specified ALWAYS results in a transactionally-consistent database. The point-in-time at which the database is restored to is the point at which the data-portion-reading part of the backup operation completed. All database backups include transaction log, otherwise there would be no way to rollback transactions that were active at the time the data-reading portion of the backup ended.*

Do I lose transaction log on a database when I do full backup ( is log truncated on full backup ) ?

No you do not loose T-Log when you do full database backup. Refer to

Misconceptions around the log and log backups: how to convince yourself for understanding how log backups work.

If I want to have data backup and transaction logs backup what should I do ?

If you want to have data backup and T-log backups, you have to first perform FULL database backup and then take subsequent T-log backups. So when you restore your database, you can restore a full backup and then subsequent T-log backups to do a Point-in-Time recovery.