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 […]
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 […]
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 a bunch of CSV files. You might find this useful for verifying row counts, if importing multiple CSV files into a database. First, open PowerShell ISE on your machine. Have a quick look at your directory. Now, I’ll count the […]
Exporting data out of SQL is an easy task, but there’s always a few snags that’ll crop up here and there, like always. This is just a quick note on the default export file options when using SQL Server Management Studio. To export a query the simplest way, it would be for us to click […]
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-5c47f749cfde4722039665/] 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. 1. Open SSMS and connect to your SQL Server. 2. Right click Databases and select Restore Database. 3. Select […]
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 […]
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… […]
