An instance with thirty user databases needs thirty full backups, and writing thirty BACKUP DATABASE statements by hand means a new database is missed the day someone creates it. This T-SQL script reads sys.databases, selects every online user database and writes one compressed, checksummed full backup per database to …

Bringing a database back to a recent point means applying its backups in one order: the full backup, the latest differential if there is one, then every log backup since, oldest first. This script reads that chain out of msdb and then shows the restore sequence, with NORECOVERY on every step except the last. Purpose …

Verify SQL Server Backup Files with RESTORE VERIFYONLY A backup file that cannot be restored is worthless, and the problem often surfaces only when a restore is attempted under pressure. The two scripts below use RESTORE VERIFYONLY to confirm that backup files are complete and readable before a real disaster occurs. …

A DELETE with no WHERE clause runs at 14:28, the mistake is noticed at 14:45, and the last scheduled backup is hours old. Restoring that backup alone throws away everything committed in between. RESTORE LOG ... WITH STOPAT replays the transaction log only up to a chosen time, and the script below lists the backups in …

This script queries sys.dm_exec_query_stats to identify the most resource-intensive queries currently cached in the plan cache, showing logical reads, writes, CPU time, and elapsed time — with the full query text and execution plan for each. Purpose and Overview When a SQL Server instance is under CPU or I/O pressure, …

This T-SQL query lists the rowstore indexes in the current database whose leaf level is fragmented and large enough to be worth maintaining, and returns a ready-to-review ALTER INDEX statement for each one. It reads sys.dm_db_index_physical_stats in SAMPLED mode and filters on avg_fragmentation_in_percent and …

Which databases on this instance hold the most disk, and how large is each one in MB and GB? One query against sys.master_files and sys.databases answers that for every user database, sorted with the largest first. Purpose and Overview Capacity planning starts with a number per database: how much file space each one …

Find Unused Nonclustered Indexes in SQL Server This script queries sys.dm_db_index_usage_stats to identify nonclustered indexes that have never been used by any read operation since the last SQL Server restart, yet still carry write overhead with every INSERT, UPDATE, and DELETE on the table. Purpose and Overview Every …

Blocking happens when one session holds a lock that another session needs, so the second session waits. The query below lists every request that is waiting on another session right now: who is blocked, who is blocking, the wait type and wait time, the database, and the SQL text on both sides. Purpose and Overview The …

SQL Server tracks index recommendations automatically as queries execute, storing them in the missing index DMVs. This T-SQL script queries those views to produce a ranked report of missing indexes, ordered by the estimated improvement they would provide, along with a ready-to-run CREATE INDEX statement for each …