A database snapshot in SQL Server provides an instant, read-only view of your data at a specific point in time without blocking the live workload. This post explores how snapshots interact with high-availability technologies—Always On Availability Groups, log shipping, and transactional replication—highlighting that creating a snapshot is always safe but restoring it severs existing sync mechanisms. You’ll learn the steps required to roll back to a snapshot, including AG reseeding, log-shipping reinitialization, or replication re-initialization, and why planning for these operations is essential. The post also covers practical tips on monitoring sparse-file growth and choosing between snapshots and full backups for longer-term retention.
Author: Stephen Planck
Working with Database Snapshots in SQL Server
Database snapshots are one of those features that’s been around forever, but still solves real-world problems with very little setup. In a single statement you can capture a point-in-time, read-only copy of any user database, use it for reporting or off-load testing, and—if disaster strikes—revert the source back to that snapshot in minutes. This guide explains how snapshots work under the hood, walks through day-to-day tasks (including creating the original database), and highlights the pitfalls you should plan for before using them in production.
Restoring a Single Data Page in SQL Server: A DBAs Guide
Usually corruption in SQL Server is either nonexistent or so widespread that you have no choice but to perform a file or full‑database restore. Yet an awkward middle ground exists: a handful of pages—perhaps only one—become unreadable while the rest of the database remains perfectly healthy. A full restore would repair the damage, but at the cost of rolling back hours of work and locking users out of an otherwise functional system. That’s precisely why Microsoft built RESTORE … PAGE. You can surgically overwrite just the bad 8‑KB chunks, roll them forward with transaction‑log backups, and return the database to service in minutes rather than hours.
Introduction to Temporal Tables: Seamless Record Versioning in SQL Server
Have you ever wanted to see exactly how a row looked in your SQL Server database a week or a month ago—without writing complicated auditing code or triggers? With temporal tables (also known as system-versioned tables), you can do precisely that. Temporal tables automatically track historical data changes by maintaining multiple versions of each row in a separate history table. Let’s explore how they work, why you might use them, how to set them up, and what best practices to follow.
Remembering Yvonne Vanslageren
As some of you may have noticed, our last blog post was on February 18th. On February 19th, 2025, Yvonne Vanslageren, the cofounder of this blog and my very dear friend, passed away after a short battle with illness. She was 55 years old. Her unexpected loss has devastated her family, colleagues and myself. When I first got the news I couldn’t bring myself to touch the blog which is why I’m notifying you all of this so late.
Lock Escalation in SQL Server: Understanding and Troubleshooting for Performance
Lock escalation is a built-in mechanism in SQL Server designed to optimize how the database engine manages locks. By reducing thousands of fine-grained locks (such as row or page locks) to a single table-level lock, SQL Server aims to lower overhead in the lock manager. In practice, lock escalation saves system resources but can also introduce challenges in high-concurrency environments. This post explores why lock escalation happens, how it works under the hood, the problems it can cause, and strategies to prevent or mitigate unwanted escalations.
Understanding the “mssqlsystemresource” (Resource) Database in SQL Server
This blog post explores the critical role of the hidden mssqlsystemresource database in SQL Server. It explains how this read-only database stores essential system object definitions and simplifies patch management and upgrades. DBAs will gain valuable insights on best practices, versioning, and troubleshooting to keep their SQL Server environment secure and efficient.
Exploring Programming Constructs in T-SQL – Part 4: Common Table Expressions (CTEs) and Recursive Queries
Welcome to Part 4 of our ongoing series on T-SQL programming constructs. So far, we’ve discussed variables, conditional IF statements, loops, CASE expressions, and covered essential concepts like error handling and transaction management. Now, we turn to Common Table Expressions (CTEs)—a powerful feature in T-SQL that can simplify complex queries and enable recursion.
Understanding and Managing the Model, MSDB, and Master Databases in SQL Server
It’s easy to focus on user databases and overlook the system databases at the core of every SQL Server instance. However, the master, msdb, and model databases form the backbone of your environment. Understanding each database’s specific responsibilities, how to safely customize them, and how to protect them from data loss or corruption is important for maintaining a stable server. In this post we take a look at all three databases to better understand their purposes and proper maintenance.
Comparative Analysis: SQL Server on Linux vs. Windows for Production
For years, SQL Server was synonymous with the Windows operating system, a pairing that brought deep integration and mature ecosystem support. The arrival of SQL Server on Linux introduced a broader choice, allowing organizations to deploy Microsoft’s flagship relational database on an entirely different platform. Although both versions share the same core engine, technical differences persist in the way each handles process scheduling, I/O, security, and administration.