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


New Table Valued Function STRING_SPLIT in SQL Server 2016

New Table Valued Function STRING_SPLIT in SQL Server 2016

STRING_SPLIT ( string , separator )

The Separator is length 1 single character or space.

Returns single column table with the column name: value.