All posts containing MSSQL-related scripts and patterns.
A basic update trigger. This specific trigger can be used for audit purposes.
CREATE TRIGGER dbo.TriggerDesc_UPDATE ON dbo.TableName FOR UPDATE AS BEGIN IF UPDATE(ColumnName) BEGIN INSERT INTO dbo.AuditLogTable (IDColumn, OldValue, NewValue) SELECT i.IDColumn, d.Value, i.Value FROM inserted I JOIN deleted d ON i.IDColumn = d.IDColumn END END
MSSQL Create Update Trigger (QuickRef) Read More »
Get info on non-sleeping sessions or open open transactions.
SELECT DB_NAME(database_id) AS dbname, * FROM sys.dm_exec_sessions WHERE 1=1 AND is_user_process > 0 AND ( open_transaction_count > 0 OR status <> 'sleeping' ) ORDER BY database_id
MSSQL Query Session Information Read More »
Create a format file using BCP command line tool.
bcp DatabaseName.dbo.TableName format nul -S ServerName -T -c -t ',' -r '\n' -x -f X:\Path\To\FormatFile.xml
Export a table to a CSV-File using a format file (BCP).
bcp DatabaseName.dbo.TableName out X:\Path\To\ExportFile.csv -S ServerName -T -f X:\Path\To\FormatFile.xml
Insert data from a CSV-File using a format file (OPENROWSET).
INSERT INTO dbo.TableName SELECT * FROM OPENROWSET (BULK X:\Path\To\ExportFile.csv', FORMATFILE = X:\Path\To\FormatFile.xml') AS rows WHERE IDColumn < 200;
Insert data from a CSV-File (BULK INSERT).
BULK INSERT dbo.TableName FROM 'X:\Path\To\ExportFile.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR ='\n');
MSSQL Bulk Import / Export Read More »
Configure database encryption and backup keys en certificates. This manual shows the complete chain from Service Master Key to the Database Encryption Key. Please remember to at least backup the Server Certificate (marked: “[*** IMPORTANT ***]”) and store these files in a secure location. Without this certificate you cannot access your data when it’s moved or restored to another server. You should substitute all (random generated) passwords with your own.
STEP 1 – Backup and restore (for verification purposes) Service Master Key.
USE master; GO -- Backup Service Master Key... BACKUP SERVICE MASTER KEY TO FILE = 'X:\Path\To\ServiceMaster.key' ENCRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t' -- Restore Service Master Key... RESTORE SERVICE MASTER KEY FROM FILE = 'X:\Path\To\ServiceMaster.key' DECRYPTION BY PASSWORD = 'ooxuRai4shaid0AvnieLoh6t' FORCE
STEP 2 – Create, backup and restore (for verification purposes) Database Master Key and Server Certificate.
USE master; GO -- Create Database Master Key... CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah'; GO -- Backup Database Master Key... BACKUP MASTER KEY TO FILE = 'X:\Path\To\Master.key' ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah' GO -- Drop Database Master Key for restore test... DROP MASTER KEY GO -- Restore Database Master Key... RESTORE MASTER KEY FROM FILE = 'X:\Path\To\Master.key' DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah' ENCRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah' GO -- Open key and let Service Master Key decrypt (for transparent encryption)... OPEN MASTER KEY DECRYPTION BY PASSWORD = 'eeLohz8iJuapo5mekohng0Ah' GO ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO -- Create Server Certificate... CREATE CERTIFICATE ServerCertDefault WITH SUBJECT = 'Server Certificate Default' GO -- Backup Server Certificate and Key [*** IMPORTANT ***]... BACKUP CERTIFICATE ServerCertDefault TO FILE = 'X:\Path\To\ServerCertDefault.cer' WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key', ENCRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' ); GO -- Remove Server Certificate for restore test.... DROP CERTIFICATE ServerCertDefault GO -- Restore Server Certificate with Key... CREATE CERTIFICATE ServerCertDefault FROM FILE = 'X:\Path\To\ServerCertDefault.cer' WITH PRIVATE KEY ( FILE = 'X:\Path\To\ServerCertDefault.key' , DECRYPTION BY PASSWORD = 'Eeh3aeb0EiTh2ohZneil8ueS' ); GO
STEP 3 – Turn on database encryption in database.
USE [Database] GO -- Create Database Encryption Key... CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE ServerCertDefault GO -- Enable Encryption... ALTER DATABASE [Database] SET ENCRYPTION ON GO -- Check if database encryption is turned on... SELECT name, is_encrypted FROM sys.databases WHERE is_encrypted = 1 Results: name is_encrypted -------------------------------------------------------------- ------------ DatabaseName 1
MSSQL Configure Database Encryption Read More »
