SQL Server 2016: Check Encryped Database with RESTORE FILELISTONLY
Posted: March 10, 2016 Filed under: SQL Server 2016, Uncategorized Leave a comment----****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
New Table Valued Function STRING_SPLIT in SQL Server 2016
Posted: March 8, 2016 Filed under: SQL Server 2016 Leave a commentNew 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