Previous Posts

Backup and Restore
Database in ‘Recovery Pending’ After Transaction Log Backup

Restoring Master – When All else fails and the SQL service won’t start.

Restores using Invalid Backup Default Locations

SSMS- Source Database ‘Restoring’ After Performing a Restore To a New Destination

UNDERCOVER TOOLBOX: Checking Your Backup Paths Exist and Automatically Creating Them if they Don’t

UNDERCOVER TOOLBOX: sp_RestoreScript – A Painless way to generate SQL Server database restore scripts.

UNDERCOVER TOOLBOX: When was my last backup taken?

The dreaded SQL server error 3013! ‘RESTORE LOG is terminating abnormally’ the bane of my life, until recently

Database Administration
7 ways to Query Always On Availability Groups using SQL

Copying Data From One Table To Another – To Disable Indexes or Not To Disable Indexes, That’s the Question.

Creating a SQL Server Test Lab On Your Workstation – Part One, Installing The Domain Controller

Creating a SQL Server Test Lab On Your Workstation – Part Two, Creating a Domain Account and Joining a SQL Server to the Domain

Creating a SQL Server Test Lab On Your Workstation – Part Three, Creating a Windows Failover Cluster and a SQL Server Availability Group

Duplicate Agent jobs – A good reason not to meddle with Msdb

Failed to perform AlwaysOn Manual Failover using the wizard – Availability-group DDL Operations are permitted only when you are using the master database .

Log Shipping – Error: Failed to update database “DATABASE NAME” because the database is read-only

Scripting out SQL Server Objects without the GUI

SQL Mirroring, Preserving the Log Chain During Database Migrations

UNDERCOVER TOOLBOX: Database Files/Sizes and Growth rates

UNDERCOVER TOOLBOX: sp_AGReconfigure – Manage Always on Sync & Failover settings from a single stored procedure.

UNDERCOVER TOOLBOX: sp_ChangeDatabaseOwnership – Producing statements to Alter Authorisation

UNDERCOVER TOOLBOX: sp_ChangeAgentJobOperator – Scripting out change of notification operator , Deleting and/or Creating.

UNDERCOVER TOOLBOX: Sp_ChangeJobOwnerShip – Changing Ownership in bulk.

UNDERCOVER TOOLBOX: Sp_FailedJobs the quick way to check for Failed Agent Jobs.

UNDERCOVER TOOLBOX: Find Untrusted Foreign Keys and Orphaned Key data

UNDERCOVER TOOLBOX: Sp_RunningJobs – See all currently running SQL Agent Jobs

UNDERCOVER TOOLBOX: sp_Snapshot – The Easy Way To Create Database Snapshots for One or Many Database

UNDERCOVER TOOLBOX: sp_WhatsMyAG

Upgrading our Test lab from SQL 2016 to 2017 CTP with Always On Availability Groups

Using PowerShell To Deploy Scripts From GitHub to Multiple SQL Servers

Using SQLCMD Mode for Always On Availability housekeeping.

Using the SQL Error log to help log meaningful errors for your Automated Restore jobs.

View your Failover Cluster from the DMV’s

Who’s Updated My Rows?! Interrogating the Transaction Logs

Hits and Tips
Snippets – A Couple of clicks to reduce repetitive typing.

Internals
Dissecting SQL Server, The Internal Structure of a Row

Podcasts
SQL Undercover Podcast – Feb 2018

Python
Connecting to a Remote Instance of SQL Server Using Python

UNDERCOVER TOOLBOX: Using Python To Copy A SQL Login From One Server To Another

Security
Dude where’s my access?

Secret Codes And SQL Server, Part 1: Writing Our Own Encryption Algorithm and Cracking it.

Secret Codes And SQL Server, Part 2: Pimp Your Encryption Algorithms.

UNDERCOVER TOOLBOX: sp_FailedLogins, Capture those failed logins with ease.

TSQL
Building an IN list using REPLACE

Concatenation Truncation – Are your strings being truncated?

Generating Random Numbers For All Rows in a Query and Other Funky Stuff You Can Do With Them

How Far Has My Update Got? Finding Out How Many Rows Your Long Running Insert, Update or Delete Has Actually Modified So Far

Joining Datasets Side by Side, in Their Natural Order When There’s No Common Key

SQL Smackdown!!! Cursors VS Loops

The Arcane Science of Bitwise Logic and SQL Server

UNDERCOVER TOOLBOX: fn_SplitString, It’s Like STRING_SPLIT But For Luddites (or those who haven’t moved to SQL 2016 yet).

UNDERCOVER TOOLBOX: Generate a Temporary Table Definition to Match the Resultset of a Query

TSQL Tuesday
T-SQL Tuesday – Folks Who Have Made a Difference #TSQL2sday

T-SQL Tuesday #96 How did I get started?

T-SQL Tuesday #96 – Folks Who Have Made a Difference in my career #TSQL2sday

T-SQL Tuesday #99 : Getting Decked Out in Rubber and Lycra, My Non-techy Passion #TSQL2sday

The Cloud
DBs in the Cloud Part 1 – Setting up and Connecting to a SQL Azure Database

Undercover Inspector

The SQLUndercover Inspector is now available!

Getting started with the SQLUndercover Inspector