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 […]
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 […]
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, […]
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 […]
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 […]
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 […]
Backing up a database is something I’ve always found easier done within a query window. There’s not much to it; [crayon-5cc16aad0b486868125741/] 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 […]
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. How to Restore a SQL Server Database 1. Open SSMS and connect to your SQL Server. 2. Right click […]
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 […]
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 […]
The Stack Exchange Data Explorer is an amazing place where you’ll find all the data that is stored for those Q&A’s they’ve got going. The interesting stuff is down here though 😉 (number 24 in the list) You can view other folk’s queries on each database, as well as create a query of your own… […]
While looking at an old script today, one that kills SPIDs on a SQL Server database... I had an instant urge to try kill a system SPIDs, just to see what would happen. And the result was… That’s a good thing though right? One less thing to be worrying about. You can’t kill system SPIDs. […]
