SQL Server 2016: Check Encryped Database with RESTORE FILELISTONLY

  

 ----****Decrypt
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
EXEC(@SQL) 

Select * from #backupdetails

Drop table #backupdetails

 
Advertisements

New Table Valued Function STRING_SPLIT in SQL Server 2016

New Table Valued Function STRING_SPLIT in SQL Server 2016

Syntax:
STRING_SPLIT ( string , separator )

The Separator is length 1 single character or space.

Returns single column table with the column name: value.

https://msdn.microsoft.com/en-us/library/mt684588.aspx