SQL Server 2016: Check Encryped Database with RESTORE FILELISTONLY


OPEN Master Key Decryption  BY PASSWORD = 'thisisMySecretKey$'

declare @SQL NVARCHAR(1000),  @fullbackup NVARCHAR(2000)
Set  @fullbackup   = N'C:\Backup\myEncryptedDBbackup.bak'

CREATE TABLE #backupdetails
     LogicalName          NVARCHAR(255),
     PhysicalName         NVARCHAR(255),
     Type                 NVARCHAR(1),
     FileGroupName        NVARCHAR(255),
     Size                 BIGINT,
     MaxSize              BIGINT,
     FileId               INT NULL,
     CreateLSN            NUMERIC(25, 0) NULL,
     DropLSN              NUMERIC(25, 0) NULL,
     UniqueFileId         UNIQUEIDENTIFIER NULL,
     readonlyLSN          NUMERIC(25, 0) NULL,
     readwriteLSN         NUMERIC(25, 0) NULL,
     BackupSizeInBytes    BIGINT NULL,
     SourceBlkSize        INT NULL,
     FileGroupId          INT NULL,
     LogGroupGuid         UNIQUEIDENTIFIER NULL,
     DifferentialBaseLsn  NUMERIC(25, 0) NULL,
     DifferentialBaseGuid UNIQUEIDENTIFIER NULL,
     IsReadOnly           BIT NULL,
     IsPresent            BIT NULL,
     --SQL Server 2008, 2012,2014 Only
     TDEThumbprint        VARBINARY(32) NULL

	 --  --SQL Server 2016 Only
	 ,SnapshotUrl  NVARCHAR(360)

SET @SQL ='RESTORE FILELISTONLY FROM DISK=''' + @fullbackup + ''''

INSERT #backupdetails

Select * from #backupdetails

Drop table #backupdetails


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s