Backup & Recovery from First Principles, Part 2: Anatomy of a Backup
A backup file is not a copy of your MDF. It is a container with a precise contract: enough pages and enough log to reconstruct a transactionally consistent database as of the moment the backup…
Backup & Recovery from First Principles, Part 1: Recovery Models Actually Explained
Every SQL Server database has a recovery model, and most databases got theirs by inheritance rather than by decision: whatever model had at CREATE DATABASE time is what you are running today. That default determines…
Pick a Convention and Stick With It
You know what’s worse than a bad naming convention? Two naming conventions in the same database. I ran into this last week while writing a verification query for a PostgreSQL database. The query is a…
Transaction Isolation Levels and sp_executesql
SQL Server has several transaction isolation levels; probably the one most people are familiar with is the default of “read committed”. However, you may not realize it, but running dynamic code via sys.sp_executesql doesn’t necessarily…
What SQL Text is that Session Running?
Use the code below if you have a long, complicated, stored procedure or piece of dynamic SQL running on a server, and you’d like to see exactly which piece of SQL Text, or code, is…
On Default Schemas and “Search Paths”
Default schemas in SQL Server can be a blessing, since they reduce the need to specify the schema when creating DDL statements in T-SQL. However, relying on the default schema when creating DML statements can…