Lesson 10	Run `sqlmaint.exe`
Objective	Run database integrity checks, stats updates, and backups from the command line using `sqlmaint`, with modern cautions and examples, and explore SQL Server 2022 features for enhanced maintenance.

Run `sqlmaint.exe` (with modern guidance and SQL Server 2022 enhancements)

sqlmaint is a legacy utility that can perform DBCC checks, back up databases and logs, update statistics, rebuild indexes, and write text/HTML reports. It remains functional in SQL Server 2022 for backward compatibility but is deprecated and removed in future versions. For new work, prefer Maintenance Plans (SQL Agent + SSIS), direct T-SQL, or SQL Server 2022’s advanced features for performance and maintenance.

What `sqlmaint` can do

DB integrity checks: -CkDB, -CkDBNoIdx, -CkAl, -CkAlNoIdx, -CkCat
Statistics: -UpdOptiStats (optionally with -SupportComputedColumn)
Index maintenance: -RebldIdx
Backups: -BkUpDB or -BkUpLog with -BkUpMedia DISK|TAPE, plus -UseDefDir, -CrBkSubDir, -DelBkUps , -VrfyBackup, -BkUpOnlyIfClean
Reporting: -Rpt, -HtmlRpt, -DelHtmlRpt , -WriteHistory
Plan scoping: -PlanName or -PlanID (uses the plan’s database list only)

Auth and syntax essentials

REM Windows Authentication is used by default if -U is omitted 
sqlmaint -S MyServer\SQL2022 ^ 
-D AdventureWorks2022 ^ 
-CkDB -CkAl -CkCat ^ 
-Rpt "C:\DBMaint\AdvWorks_chk.rpt" ^ 
-WriteHistory

REM Update stats with a 15% sample for all DBs in a plan
sqlmaint -S MyServer -PlanName "UserDBs" -UpdOptiStats 15 -WriteHistoryREM Back up DB to default backup dir, one subdir per DB, keep last 2 weeks
sqlmaint -S MyServer -PlanName "UserDBs" -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks -VrfyBackupREM Backup only if checks pass
sqlmaint -S MyServer -D Sales -CkDB -BkUpDB "D:\SqlBackups" -BkUpMedia DISK -BkUpOnlyIfClean -VrfyBackup

Tip: Avoid embedding passwords on the command line. Use Windows Authentication (omit -U and -P) or, in SQL Server 2022, leverage Microsoft Entra ID authentication (formerly Azure AD) for secure connections, which integrates with hybrid environments for enhanced security.

Return codes and history

Exit code: 0 success, 1 failure (including DBCC errors).
-WriteHistory records to msdb.dbo.sysdbmaintplan_history. Reports also write to the specified -Rpt/-HtmlRpt destination with a timestamped filename.

SQL Server 2022: Modern Maintenance Enhancements

Intelligent Query Processing (IQP): SQL Server 2022 introduces advanced IQP features, such as Degree of Parallelism (DOP) Feedback and Memory Grant Feedback Persistence, which optimize query execution plans automatically. These reduce the need for frequent manual statistics updates (previously handled by sqlmaint -UpdOptiStats), as the Query Optimizer adapts to workload changes dynamically. For example, DOP Feedback adjusts parallelism for maintenance tasks like DBCC CHECKDB, improving performance on large databases.
Query Store Enhancements: The Query Store is enabled by default in SQL Server 2022 for new databases, capturing query performance history automatically. This provides detailed insights into maintenance task performance (e.g., index rebuilds or statistics updates) without relying on sqlmaint reports. DBAs can use Query Store hints to shape execution plans for maintenance scripts, reducing resource contention during tasks like ALTER INDEX ... REBUILD.
ZSTD Compression for Backups: SQL Server 2022 supports the ZSTD compression algorithm for backups, offering faster compression and smaller backup files compared to traditional methods. Replace sqlmaint -BkUpDB with T-SQL like: BACKUP DATABASE AdventureWorks2022 TO DISK = 'D:\Backups\AdvWorks.bak' WITH COMPRESSION, COMPRESSION_ALGORITHM = 'ZSTD', COMPRESSION_LEVEL = 'LOW'. This reduces backup times and storage needs, especially for large databases.

Modern alternatives (recommended)

Maintenance Plans in SSMS (run by SQL Agent/SSIS), with rich logging, scheduling, and integration with SQL Server 2022 features like ZSTD compression and Query Store analytics.
dtexec to run SSIS packages from the command line, offering more flexibility and better integration with modern SQL Server features than sqlmaint.
Direct T-SQL (e.g., DBCC CHECKDB, BACKUP DATABASE ... WITH COMPRESSION, ALTER INDEX ... REBUILD, UPDATE STATISTICS) scripted via SQL Agent or sqlcmd. Use Ola Hallengren’s Maintenance Solution for a robust, community-trusted T-SQL framework that leverages SQL Server 2022’s capabilities.

Exercise

Test yourself on switches, safe usage, and SQL Server 2022 maintenance features: Using sqlmaint.exe and SQL Server 2022 Features - Exercise

The next lesson will show you how to use the database Maintenance Plan Wizard, including how to incorporate SQL Server 2022’s enhanced features for streamlined maintenance.