SQL Server Scripts
open-menu closeme
Home
All SQLServer70 Scripts
Scripts & Commands
Links icon
SQL Server Documentation SQL Server Mgmnt Studio
About
Contact Us
Privacy Statement
rss

  • Detect Suspect Database Pages with msdb.dbo.suspect_pages

    calendar Jul 2, 2026 / Jul 3, 2026 · 9 min read · sql server suspect pages msdb database corruption page corruption dbcc checkdb integrity check maintenance storage error scripts  ·
    Share on: twitter facebook linkedin copy

    Storage errors do not always surface as immediate failures. When SQL Server encounters an 823, 824, or 825 error reading or writing a database page, it records the offending page in msdb.dbo.suspect_pages — a system table that accumulates page-level I/O failures across service restarts. Querying this table is the …


    Read More

  • Configure Database Mail and Send a Test Message

    calendar Jul 1, 2026 / Jul 3, 2026 · 11 min read · sql server database mail sp_send_dbmail sysmail_add_account_sp sysmail_event_log sysmail_mailitems sysmail_account sysmail_profile msdb sql server agent administration scripts  ·
    Share on: twitter facebook linkedin copy

    Database Mail ships disabled; enabling it and wiring up an SMTP account, a profile, and a public principal grant are the steps that stand between a fresh SQL Server instance and sp_send_dbmail working reliably. The configuration script below executes all four setup calls inside msdb, fires a test message, and queries …


    Read More

  • SQL Server Copy-Only Backup Script for Dev Restore

    calendar Apr 20, 2026 / Jun 2, 2026 · 6 min read · sql server backup recovery copy only backup t-sql database administration msdb sql scripts dev restore  ·
    Share on: twitter facebook linkedin copy

    A copy-only backup is a special SQL Server backup that is independent of the normal backup sequence. It does not reset the differential base or interrupt the transaction log backup chain, making it the correct choice when you need an ad-hoc backup for a development or test environment restore without affecting …


    Read More

  • SQL Server: Find Databases With No Recent Backup

    calendar Apr 19, 2026 / Jun 2, 2026 · 6 min read · sql server backup recovery msdb monitoring database administration sql scripts backup audit sys.databases  ·
    Share on: twitter facebook linkedin copy

    Finding databases that have not been backed up recently is one of the most important checks a DBA can run. A database that grows unnoticed without a backup is a data loss event waiting to happen. This script queries msdb.dbo.backupset against sys.databases to surface any online user database with no full backup in the …


    Read More

  • SQL Server Backup Compression: Enable and Verify

    calendar Apr 17, 2026 / Jun 2, 2026 · 7 min read · sql server backup recovery backup compression sp_configure msdb sql scripts database administration maintenance scripts  ·
    Share on: twitter facebook linkedin copy

    Backup compression reduces the on-disk size of SQL Server backup files and can significantly shorten backup duration by reducing the amount of data written to disk. This post shows how to enable compression at the server level, verify the setting is active, and confirm compression ratios from backup history using the …


    Read More

  • SQL Server Backup History: Duration and Size Report

    calendar Apr 13, 2026 / Jun 2, 2026 · 6 min read · sql server backup recovery msdb sql scripts database administration backup monitoring maintenance scripts reporting sys.databases backupset  ·
    Share on: twitter facebook linkedin copy

    Query Backup Duration and File Size from SQL Server History This script queries SQL Server's msdb backup history to report the backup type, start and finish time, duration in minutes, and file size in both megabytes and gigabytes for every backup across all user databases on the server. Purpose and Overview Knowing …


    Read More

  • SQL Server Agent Job Failure History Report

    calendar Apr 11, 2026 / Jun 2, 2026 · 8 min read · sql server sql server agent msdb sql scripts database administration job monitoring maintenance scripts reporting sysjobs sysjobhistory  ·
    Share on: twitter facebook linkedin copy

    Report SQL Server Agent Job Status and Failure History This script queries the SQL Server Agent job history stored in msdb to produce a detailed report of each job's most recent run status, failure and success counts over the last 60 days, total lifetime run counts, and job step information including subsystem type and …


    Read More

  • SQL Server Backup Status Report: All Databases

    calendar Apr 10, 2026 / Jun 2, 2026 · 7 min read · sql server database administration backup recovery maintenance scripts msdb sql scripts database management backup monitoring full backup differential backup transaction log backup sys.databases  ·
    Share on: twitter facebook linkedin copy

    Check SQL Server Backup Age Across All Databases This script queries the SQL Server backup history catalog to report the most recent full, differential, and transaction log backup for every database, including how many hours and minutes ago each backup completed. Purpose and Overview Database backups are your last line …


    Read More

  • SQL Server Verify Backup Files: RESTORE VERIFYONLY

    calendar Apr 6, 2026 / Jun 2, 2026 · 8 min read · sql server backup recovery database administration maintenance scripts restore verifyonly msdb sql scripts database management backup validation msdb.dbo.backupset msdb.dbo.backupmediafamily  ·
    Share on: twitter facebook linkedin copy

    Verify SQL Server Backup Files with RESTORE VERIFYONLY Backup files that cannot be restored are worthless, and many DBAs discover problems only when they attempt an actual restore under pressure. The scripts in this article use RESTORE VERIFYONLY to confirm that backup files are readable and internally consistent …


    Read More

  • SQL Server Point-in-Time Restore with STOPAT

    calendar Apr 5, 2026 / Jun 2, 2026 · 8 min read · sql server restore backup recovery transaction log msdb database maintenance DBA scripts point in time restore sys.databases  ·
    Share on: twitter facebook linkedin copy

    Recover a SQL Server Database to a Specific Point in Time SQL Server's RESTORE LOG ... WITH STOPAT lets you roll a database back to any exact moment covered by your backup chain. This guide walks through querying backup history in msdb, executing the restore sequence, and verifying the result. Purpose and Overview Data …


    Read More

SQLServer70.com

Your Source for SQL Server Scripts
Read More

Recent Posts

  • Measure Disk I/O Performance per Database File
  • Detect Suspect Database Pages with msdb.dbo.suspect_pages
  • Configure Database Mail and Send a Test Message
  • Apply Row and Page Compression to Tables and Indexes
  • List All Tables Across All Databases in SQL Server
  • System-Versioned (Temporal) Tables in SQL Server
  • SQL Server Database Schema and Data Dictionary Queries
  • How to Update Statistics in SQL Server

Categories

SCRIPTS 64 SQL SERVER MAINTENANCE 14 SQL SERVER ADMINISTRATION 12 SQL SERVER STRUCTURE 11 SQL SERVER PERFORMANCE 9 SQL SERVER MONITORING 8 SQL SERVER BACKUP RECOVERY 7 SQL SERVER SECURITY 3

Series

SQL SERVER MAINTENANCE 14 SQL SERVER ADMINISTRATION 12 SQL SERVER STRUCTURE 11 SQL SERVER PERFORMANCE 9 SQL SERVER MONITORING 8 SQL SERVER BACKUP RECOVERY 7 SQL SERVER SECURITY 3

Tags

SQL SERVER 63 DATABASE ADMINISTRATION 39 SQL SCRIPTS 39 MAINTENANCE SCRIPTS 19 PERFORMANCE 17 SYS.TABLES 16 SYS.OBJECTS 15 SCRIPTS 14 DATABASE MAINTENANCE 13 DBCC COMMANDS 11 SYS.DATABASES 11 MONITORING 10 MSDB 10 SYS.INDEXES 10
All Tags
ACTIVE SESSIONS2 ADMINISTRATION4 ALTER INDEX1 ANSI STANDARD1 AUTOMATION2 BACKUP1 BACKUP AUDIT1 BACKUP COMPRESSION1 BACKUP MONITORING2 BACKUP RECOVERY6 BACKUP VALIDATION1 BACKUPSET1 BLOCKING2 BOTTLENECK ANALYSIS1 CAPACITY PLANNING2 CARDINALITY ESTIMATION1 CATALOG VIEWS6 CLUSTERED INDEXES1 COLUMNS1 COMPATIBILITY VIEWS1 CONSISTENCY CHECKS2 CONSTRAINT REPORTING1 CONTENTION1 COPY ONLY BACKUP1 CPU CONFIGURATION1 CPU HISTORY1 CPU UTILIZATION1 CROSS DATABASE1 CURSOR PROGRAMMING1 DATA AUDIT1 DATA COMPRESSION1 DATA DICTIONARY1 DATA TYPES1 DATABASE1 DATABASE ADMINISTRATION39 DATABASE CONNECTIONS1 DATABASE CORRUPTION2 DATABASE FILES2 DATABASE INTEGRITY1 DATABASE MAIL1 DATABASE MAINTENANCE13 DATABASE MANAGEMENT3 DATABASE MONITORING2 DATABASE OBJECTS1 DATABASE OPTIMIZATION1 DATABASE SCHEMA1 DATABASE SCRIPTS1 DATABASE SECURITY1 DATABASE STRUCTURE9 DBA SCRIPTS9 DBA TOOLS1 DBCC1 DBCC CHECKDB4 DBCC CHECKIDENT2 DBCC COMMANDS11 DBCC DBINFO1 DBCC DBREINDEX2 DBCC FREEPROCCACHE1 DBCC LOGINFO1 DBCC SHRINKFILE2 DEPENDENCIES1 DEV RESTORE1 DIAGNOSTICS1 DIFFERENTIAL BACKUP1 DISK IO1 DISK LATENCY1 DISK SPACE2 DM DB MISSING INDEX DETAILS1 DM DB MISSING INDEX GROUP STATS1 DM DB SESSION SPACE USAGE1 DM EXEC REQUESTS2 DM EXEC SESSIONS3 DM EXEC SQL TEXT1 DM OS WAIT STATS1 DMV1 DOCUMENTATION1 DUPLICATE INDEXES1 DYNAMIC SQL3 ERROR LOG1 EXECUTION PLAN1 FILEPROPERTY1 FOR SYSTEM_TIME1 FOREIGN KEYS2 FRAGMENTATION2 FULL BACKUP1 GRANT STATEMENTS1 HEAP TABLES2 HISTORY TABLE1 IDENTITY COLUMNS2 IDENTITY RESEED1 IMPACT ANALYSIS1 INDEX FRAGMENTATION2 INDEX MAINTENANCE5 INDEX OPTIMIZATION1 INDEX REBUILDING1 INDEX SIZE1 INDEX TUNING2 INDEXES4 INDEXING1 INFORMATION_SCHEMA2 INFORMATION_SCHEMA.TABLE_CONSTRAINTS1 INFORMATION_SCHEMA.TABLES1 INTEGRITY CHECK2 IO STALL1 JOB MONITORING1 KILL SPID1 LOG MANAGEMENT1 LOGIN MANAGEMENT1 MAINTENANCE2 MAINTENANCE SCRIPTS19 MAX SERVER MEMORY1 MAXDOP1 MEMORY CONFIGURATION1 METADATA7 MISSING INDEXES1 MONITORING10 MSDB10 MSDB.DBO.BACKUPMEDIAFAMILY1 MSDB.DBO.BACKUPSET1 NONCLUSTERED INDEXES1 NUMA1 OBJECT DEPENDENCIES1 OBJECT SEARCH1 OPEN TRANSACTIONS1 OPTIMIZATION2 ORPHAN USERS1 PAGE COMPRESSION1 PAGE CORRUPTION1 PARAMETER SNIFFING1 PERFORMANCE17 PERFORMANCE MONITORING1 PERFORMANCE TUNING7 PERMISSIONS2 PLAN CACHE2 POINT IN TIME QUERY1 POINT IN TIME RESTORE1 PRIMARY KEYS3 QUERY OPTIMIZATION2 QUERY OPTIMIZER1 QUERY PERFORMANCE1 QUERY TUNING3 REBUILD REORGANIZE1 RECOVERY1 REFACTORING1 REFERENTIAL INTEGRITY1 REPORTING3 RESTORE1 RESTORE VERIFYONLY1 RING BUFFER1 ROLE MEMBERSHIPS1 ROW COMPRESSION1 SCHEMA ANALYSIS2 SCHEMA AUDIT1 SCHEMA DOCUMENTATION1 SCHEMA MANAGEMENT1 SCRIPTS14 SECURITY3 SECURITY AUDIT2 SERVER MONITORING1 SESSION MANAGEMENT1 SP CONFIGURE1 SP_CHANGE_USERS_LOGIN1 SP_CONFIGURE2 SP_EXECUTESQL1 SP_PKEYS2 SP_READERRORLOG1 SP_SEND_DBMAIL1 SP_SPACEUSED1 SP_UPDATESTATS1 SPACE MANAGEMENT1 SPACE USAGE1 SQL1 SQL SCRIPTS39 SQL SERVER63 SQL SERVER AGENT2 SQL SERVER MONITORING1 SQLSERVER PROCESS UTILIZATION1 STATISTICS2 STORAGE2 STORAGE ERROR1 STORED PROCEDURES1 SUSPECT PAGES1 SYS.ALL_COLUMNS1 SYS.ALL_OBJECTS1 SYS.ALLOCATION_UNITS3 SYS.COLUMNS7 SYS.COMPUTED_COLUMNS1 SYS.CONFIGURATIONS1 SYS.DATABASE_FILES2 SYS.DATABASE_PERMISSIONS1 SYS.DATABASE_PRINCIPALS2 SYS.DATABASE_ROLE_MEMBERS1 SYS.DATABASES11 SYS.DM_DB_INDEX_PHYSICAL_STATS8 SYS.DM_DB_INDEX_USAGE_STATS5 SYS.DM_DB_MISSING_INDEX_DETAILS1 SYS.DM_DB_MISSING_INDEX_GROUP_STATS1 SYS.DM_DB_MISSING_INDEX_GROUPS1 SYS.DM_DB_PARTITION_STATS2 SYS.DM_DB_STATS_PROPERTIES1 SYS.DM_EXEC_CACHED_PLANS1 SYS.DM_EXEC_QUERY_PLAN1 SYS.DM_EXEC_QUERY_STATS1 SYS.DM_EXEC_REQUESTS2 SYS.DM_EXEC_SESSIONS2 SYS.DM_EXEC_SQL_TEXT4 SYS.DM_IO_VIRTUAL_FILE_STATS1 SYS.DM_OS_PERFORMANCE_COUNTERS1 SYS.DM_OS_RING_BUFFERS1 SYS.DM_OS_SYS_INFO1 SYS.DM_OS_VOLUME_STATS1 SYS.DM_OS_WAIT_STATS1 SYS.DM_OS_WAITING_TASKS1 SYS.DM_SQL_REFERENCED_ENTITIES1 SYS.DM_SQL_REFERENCING_ENTITIES1 SYS.DM_TRAN_ACTIVE_TRANSACTIONS1 SYS.DM_TRAN_LOCKS1 SYS.FOREIGN_KEY_COLUMNS1 SYS.FOREIGN_KEYS4 SYS.IDENTITY_COLUMNS1 SYS.INDEX_COLUMNS5 SYS.INDEXES10 SYS.KEY_CONSTRAINTS2 SYS.MASTER_FILES3 SYS.OBJECTS15 SYS.PARTITIONS6 SYS.SCHEMAS7 SYS.SERVER_PRINCIPALS1 SYS.SP_ENUMERRORLOGS1 SYS.SQL_EXPRESSION_DEPENDENCIES1 SYS.SQL_MODULES2 SYS.STATS1 SYS.TABLES16 SYS.TYPES2 SYS.VIEWS1 SYSCOLUMNS1 SYSDATABASES1 SYSINDEXES3 SYSJOBHISTORY1 SYSJOBS1 SYSMAIL_ACCOUNT1 SYSMAIL_ADD_ACCOUNT_SP1 SYSMAIL_EVENT_LOG1 SYSMAIL_MAILITEMS1 SYSMAIL_PROFILE1 SYSOBJECTS10 SYSTEM IDLE PROCESS1 SYSTEM TABLES4 SYSTEM VERSIONING1 T-SQL3 TABLE ANALYSIS1 TABLE INVENTORY1 TABLE MAINTENANCE1 TABLE SIZE1 TABLE STATISTICS1 TEMPDB1 TEMPORAL TABLES1 TRANSACTION LOG2 TRANSACTION LOG BACKUP1 UPDATE STATISTICS2 VIEWS1 VIRTUAL LOG FILES1 WAIT STATISTICS1 WAIT TYPES3 XP_READERRORLOG1
[A~Z][0~9]
SQLServer70.com

Copyright  SQLSERVER70.COM. All Rights Reserved

to-top