Create Linked Server with Postgres
March 10, 2019
Creating a Linked Server with a Postgres Database

In my previous post, I created a Link Server to another SQL Server instance This time, it’s a link to a Postgres database! This guide can be followed from start to finish using the links below. # Install SQL Server (separate post).# Install PostgreSQL (separate post).# Test Connectivity to Remote Server (previous Linked Server post).# […]

Read More
Configuring Linked Servers in SQL Server
March 4, 2019
Configuring Linked Servers in SQL Server

Linked Servers in SQL Server allows you to query external databases, such as remote SQL Server instances, Oracle, ODBC or MS Access databases. This is a quick guide on how to link one SQL Server instance to another, which will include the following: # Test Connectivity to Remote Server# Create Linked Server Login# Create Linked […]

Read More
Database Growth Events in SQL Server
February 23, 2019
Database Growth Events in SQL Server

When a database file gets full and it needs more space, a growth event will happen. The size of said database growth can be set within the database options, or can be done using the ALTER DATABASE statement as shown below. When considering growth event sizes for log (.ldf) files, you may want to have […]

Read More
Set Max Database Size in SQL Server
February 21, 2019
Setting Maximum Database File Sizes in SQL Server

Setting maximum database file sizes in SQL Server is something you’d ideally not have to set, but it’s a good last resort option available if you need it. The reason I say this is because, you never want a production database to stop updating/inserting data due to a size cap on the database. However, if […]

Read More
How to run Update-Help in PowerShell
February 19, 2019
Updating Help Documentation in PowerShell

The Update-Help cmdlet in PowerShell will download the latest help files for PowerShell modules installed on your machine. Running PowerShell as Administrator is a requirement for this one, else you’ll get the following error. “Access is denied” is the key part in this error, amongst a lot of irrelevant info. And when you do run […]

Read More
Moving TempDB Files in SQL Server
February 17, 2019
Moving TempDB Files in SQL Server

It’s a solid best practise to have TempDB files stored in separate drive, away from your C: drive. This is because TempDB can grow very large, very quickly (within minutes); depending on workloads of course, and this can happen on both the TempDB data (.ndf) and log (.ldf) files. This post is a quick tutorial […]

Read More
January 24, 2019
The (SSMS) Import Flat File Feature

The Import Flat File wizard within SSMS was introduced in December 2017 (14.0.17213.0). This is a streamlined version of the Import Data feature and it minimises the user intervention during a file import. Unlike the Import data feature, you don’t have the option to save the import as a .DTS package. This makes it a […]

Read More
January 22, 2019
Attaching SQL Databases Without Log Files

If you’ve lost a database log file and you’re in critical need of the data, well… that would never happen ( ͡° ͜ʖ ͡°) Nevertheless, this is might be a handy random fact to know, that you can attach a data (.mdf) file without the associated log (.ldf) file in SQL Server. This post will […]

Read More
January 20, 2019
Backup & Restore (WITH FILE)

In this post I’m going to show what the WITH FILE = X option does when backing-up or restoring a SQL Server database. microsoft_documentation The first thing we need to know is that a backup file can contain multiple databases, and even multiples of the same database. If you run a quick backup command on a […]

Read More
January 13, 2019
Applying Data Retention to a SQL Table

There are many reasons why you’d need to delete old data from a table within a database, some of which include; # GDPR (adhering to data retention policies)# Ever increasing tables becoming tougher to manage (e.g. a replication refresh is taking 2 days)# SQL Server Express limitations (10GB max database size)# The table is storing application […]

Read More
SSMS Custom Colours
October 22, 2018
Using Custom Connection Colours in SSMS

SSMS has an option that allows you to change the colour of your query banner, which is useful if you want visually mark a server that you work with every-day. A while back, when I was frequently switching between live & test SQL instances, I set them to red/green. Red obviously meaning be cautious! Nowadays, […]

Read More
Count Rows within CSV Files
October 9, 2018
Count rows within CSV files using PowerShell

This post is a quick run-through of using a PowerShell script to count rows within many CSV files, with a quick check of the data afterwards. You might find this useful for verifying data using row counts, if importing multiple CSV files into a database. Count Rows within CSV Files First, open PowerShell ISE on […]

Read More
Export Data from SQL Server
October 2, 2018
Exporting SQL Server Query Results to CSV

This post is a quick guide on how to export data from SQL Server into a CSV file. The default export to settings will try save your query as a column aligned .rpt file, so I'll cover that below while converting to CSV format. Export SQL Server Query to CSV File To export a query […]

Read More
October 1, 2018
Checking Table Sizes in SQL Server

There are many ways to check the size of a table in SQL Server. The way you'll do it will likely depend on what you're task at hand is. For example, a quick look at the Disk Usage by Top Tables report on a database you've never seen before would instantly show you the heavy […]

Read More
September 16, 2018
Backing Up a SQL Server Database

Backing up a database is something I’ve always found easier done within a query window. There’s not much to it; [crayon-5c91415b975c9006712924/] That’s what I’m used to writing. I’ve no need to worry about the copy_only option these days, or check SQL Server Editions to see if I can use compression. Good times that was. Measuring […]

Read More
September 4, 2018
Restoring a SQL Server Database

This is a simple guide to restoring a full backup file in SQL Server, covering a just a few options available during so. As always, have a read through Microsoft’s documentation page before-hand for proper info. 1. Open SSMS and connect to your SQL Server. 2. Right click Databases and select Restore Database. 3. Select […]

Read More
August 30, 2018
Batch Deletions in SQL Server

This script will delete rows within a table that are out-with the specified retention period. This can be useful if you’re running deletes on large tables, and particularly if; # Deletes are taking a long time & you’re finding it difficult to track progress.# You have a short maintenance window each day to perform such […]

Read More
July 9, 2018
Copying Dates into Excel

There’s often a need to quickly copy/paste a database query output into an Excel sheet, rather than exporting as a .csv file. When you do this for a DATETIME column though, it’ll show up in Excel looking something like this. I don’t think Excel likes the milliseconds. This is the data I tried to copy […]

Read More