Tim's Devops Patterns

MSSQL Bulk Import / Export

Tim van Kooten Niekerk / MSSQL Server / 2016-02-23

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 Configure Database Encryption

Tim van Kooten Niekerk / MSSQL Server / 2016-02-23

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

Certificate database Encryption Key MSSQL

MSSQL master Database Maintenance

Tim van Kooten Niekerk / MSSQL Server / 2016-02-23

Command line options to start SQL server in single user mode for master database restore. Add -f for a minimal setup.

sqlservr.exe -c -m –sINSTANCENAME

Setup option to recreate the master database.

./setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DOMAIN\adminaccount

Master MSSQL recreate Restore Single

MSSQL Enable/Disable Indexes and Constraints (QuickRef)

Tim van Kooten Niekerk / MSSQL Server / 2016-02-23

Enable or disable a foreign key constraint.

ALTER TABLE dbo.TableName [NO]CHECK 
CONSTRAINT [FK_TableName_FTableName_IDColumn|ALL];

Enable or disable an index.

ALTER INDEX [IX_TableName_IndexName|ALL] 
ON dbo.TableName [DISABLE|REBUILD];

Recreate an index

CREATE INDEX IX_TableName_IndexName ON dbo.TableName(ColumnName) 
WITH ( DROP_EXISTING = ON );

Constraint Index MSSQL

MSSQL Create Database With Multiple Filegroups (QuickRef)

Tim van Kooten Niekerk / MSSQL Server / 2016-02-22

Query to create a database with multiple filegroups.

CREATE DATABASE [DatabaseName]
ON PRIMARY
  ( Name = 'DBNamePFG', FILENAME = 'D:\DEF\DBNamePFG.mdf', SIZE = 100 MB, FILEGROWTH = 100 MB ),
FILEGROUP [FileGroup2]
  ( Name = 'DBNameFG2a', FILENAME = 'E:\DEF\DBNAmeFG2a.ndf', SIZE = 100 MB, FILEGROWTH = 100 MB ),
  ( Name = 'DBNameFG2b', FILENAME = 'F:\DEF\DBNAmeFG2b.ndf', SIZE = 100 MB, FILEGROWTH = 100 MB )
LOG ON
  ( Name = 'DBNameLog', FILENAME = 'L:\DEF\DBNameLog.ldf', SIZE = 1 GB, FILEGROWTH = 500 MB )

Create database File Filegroup

MSSQL Set All Databases to FULL Recovery

Tim van Kooten Niekerk / MSSQL Server / 2015-08-26

This script sets all databases to FULL recovery (except for master, model and tempdb). We can avoid using a cursor by making use of a temporary table. First a temptable gets filled with execution commands, and then the script executes each row until the record id equals the total number of records.

-- Declarations and temptables...
DECLARE @iNumRecs int, @iRecNum int = 1, @vCommand nvarchar(max)
CREATE TABLE #tCommands (ID int IDENTITY(1,1), Command nvarchar(max))

-- Insert Commands into temptable...
INSERT INTO #tCommands (Command)
SELECT N'ALTER DATABASE [' + name + '] SET RECOVERY FULL WITH NO_WAIT' 
FROM [master].[sys].[databases]
WHERE recovery_model_desc = 'SIMPLE' AND name NOT in ('master', 'msdb', 'tempdb')

-- Execute commands from temptable...
SELECT @iNumRecs = COUNT(*) FROM #tCommands
WHILE @iRecNum <= @iNumRecs
BEGIN
 SELECT @vCommand = Command FROM #tCommands
 WHERE ID = @iRecNum
 -- Execute command and increase record number...
 -- EXECUTE(@vCommand) --Alternative, Not preferred...
 EXECUTE sys.sp_executesql @stmt = @vCommand
 SET @iRecNum += 1
END

-- Cleanup...
DROP TABLE #tCommands

Cursor Recovery Temptable

PowerShell Remove Old Files From a Directory

Tim van Kooten Niekerk / PowerShell Scripting / 2015-06-10

Script for removal of old backup or logfiles from a specific directory. The example below removes all files from a directory that are older than 90 days. A log of all files that are removed is written to the windows eventlog.

# Global config settings...
[string]$sDirName = "D:\LogFiles"
[string]$sFileNameMask = "*.log"
[string]$sGCIMask = $sDirName + "\" + $sFileNameMask
[string]$sEventLogMessage = "### Cleanup Script Started... ###`r`n"

# Functions and Procedures...
function fGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

# Start Main script...
if (Test-Path $sDirName) {
get-childitem $sGCIMask | 
    % { 
        # Get File Last Write Time...
        [datetime]$dFileModTime = fGetFileModTime($_.fullname)
        if ($dFileModTime -lt (Get-date).AddDays(-90)) {
        remove-item $_.fullname
        $sEventLogMessage = $sEventLogMessage + "File " + $_.fullname + " (" + $dFileModTime.DateTime + ") removed...`r`n"
        }
    }
}

# Finish and Cleanup...
[string]$sEventLogMessage = $sEventLogMessage + "### Cleanup Script Finished... ###"
write-eventLog -LogName "Windows PowerShell" -Source "PowerShell" -EventID 30001 -Message $sEventLogMessage -EntryType Information

Cleanup File

GNU/Linux Find and Remove Old Files From Directory

Tim van Kooten Niekerk / GNU/Linux / 2015-06-10

You can use find to script removal of old backup or logfiles from a specific directory. The example below removes all files from a directory that are older than 14 days.

find /var/local/ucs/*.ucs -type f -mtime +14 -exec rm {} \;

Cleanup File

PowerShell Check File Modified Time

Tim van Kooten Niekerk / PowerShell Scripting / 2015-04-08

Script to check is a specific file exists and its modified time is less than 1 day in the past. This script uses some custum functions from a imported module.

# Import DBA modules...
Import-Module 'D:\Path\To\DefaultFunctionsModule.psm1'

# Global config settings...
[string]$sSubject1 = "SBO:CheckLogFiles Errors Found..."
[string]$sSMTPServer1 = "smtp.example.com"
[string]$sToAddress1 = "mail@example.com"
[string]$sEventLogSource = "{eventlogsource}"

# Functions en Procedures...
function fnGetFileModTime([string]$sFileName) {
  $vFileInfo = get-childitem $sFileName
  return $vFileInfo.lastwritetime 
}

# Start Main script...
if ((Test-Path $args[0]) -and ($args[1])) {
  # Request file modified time...
  [datetime]$dFileModTime = fnGetFileModTime("$($args[0])")
  # Alert if file modified time is older than 1 day...
  if ((Get-date).AddDays(-1) -gt $dFileModTime) {
  $sMessage1 = "ERROR: File modified time is older than 1 day...`r`n`r`nFilename: " + $args[0] + "`r`nService: " + $args[1]
  fnWriteEventLog -sNode (fnGetFQDN) -sMessage $sMessage1
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sMessage1
  }
} else {
  if (($args[1])) {
    # Alert if file does not exist...
    $sMessage1 = "ERROR: File does not exist...`r`n`r`nFilename: " + $args[0] + "`r`nService: " + $args[1]
    fnWriteEventLog -sNode (fnGetFQDN) -sMessage $sMessage1
    fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sMessage1
  } else {
      $sMessage1 = "ERROR: Missing argument... [CheckModTime.ps1 `"c:\path\to\file`" `"service`"]`r`n`r`nFilename: " + $args[0]  + "`r`nService: " + $args[1]    
      echo $sMessage1
  }
}

EventLog File Modified

GIT Version Control Basics

Tim van Kooten Niekerk / GNU/Linux / 2015-04-07

Clone a Repository, add files, commit and push to master

git clone [--recurse-submodules] ssh://git@gitlab.timvkn.nl:22/Project.git
git add [filename|-A]
git commit -a -m "Initial upload..."
git push -u origin master

Create a branch for the new release and check-out to the new branch

git branch release-4.1.11
git checkout release-4.1.11
git submodule update --init --recursive

Make the changes to the files and commit the changes

git commit -a -m "Change description..."

Mark as executable

git update-index --chmod=+x build.sh

Push to server

git push -u origin release-4.1.11

Merge changes with master branch

A) Merge with master takes place on server. Pull new master branch from server...
git checkout master
git pull -a

B) Merge all changes to the master locally and push to server...
git checkout master
git merge release-4.1.11
git push -u origin master

Delete old branch

git branch -d release-4.1.11
* Option -D deletes a not merged branch...

Display commits and force to specific commit

git reflog
git reset --hard e072e63

File GIT Scripts Version Control