SQL Server Common Commands - CodeSwordsman

Frequent sqlcmd connections, T-SQL basics, database and log backup and restore, index and statistics maintenance, plus blocking troubleshooting.

SQL Server 2019 / 2022Updated 2026-10-3024 commands

Connections & Tools

Connect with sqlcmd; a non-default port follows the host after a comma

sqlcmd -S 10.0.0.12,1433 -U sa -P '<PASSWORD>' -d appdb

sqlcmd -S localhost -E -d appdb

Show the engine version, patch level and host operating system

select @@version;

Switch the current database context

use appdb;

Show a table structure including indexes, constraints and column types

exec sp_help 'orders';

Batch separator used by sqlcmd and SSMS to submit the previous batch

go

List all user tables in the current database

select name from sys.tables order by name;

Fetch the latest 20 rows; SQL Server uses TOP instead of LIMIT

select top 20 * from orders order by id desc;

Count orders after a date; make sure large tables are indexed

select count(*) from orders where created_at >= '2026-01-01';

Find which tables contain a given column name

select object_name(object_id) as tbl, name from sys.columns where name = 'user_id';

Dirty read to avoid blocking by long transactions

select id, name
from orders with (nolock)
where status = 1;

NOLOCK can return uncommitted rows; prefer snapshot isolation for transactional workloads

Full compressed backup; INIT overwrites an existing file with the same name

backup database appdb to disk = '/var/opt/mssql/backup/appdb.bak' with compression, init;

INIT overwrites the existing backup file; use NOINIT to keep history

Back up the transaction log; requires the full recovery model

backup log appdb to disk = '/var/opt/mssql/backup/appdb.trn';

Risky

Restore over the existing database and bring it online

restore database appdb from disk = '/var/opt/mssql/backup/appdb.bak' with replace, recovery;

Verify backup readability and integrity without touching the live database

restore verifyonly from disk = '/var/opt/mssql/backup/appdb.bak';

Check recent backup history to confirm jobs are running

select database_name, backup_finish_date from msdb.dbo.backupset order by backup_finish_date desc;

List indexes on a table with their clustered or nonclustered type

select name, type_desc from sys.indexes where object_id = object_id('orders');

Refresh statistics to fix poor plans caused by skewed data

update statistics orders;

Check index fragmentation to decide between rebuild and reorganize

select index_id, avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'limited');

Rebuild every index on a table; I/O heavy, best for heavily fragmented indexes

alter index all on orders rebuild;

Show sessions, statuses and blocked-by information, the first step in blocking analysis

exec sp_who2;

List blocked requests with the sessions blocking them

select session_id, blocking_session_id, wait_type, wait_time, command
from sys.dm_exec_requests
where blocking_session_id <> 0;

Risky

Kill a session; its uncommitted transaction rolls back

kill 62;

Check logical and physical consistency; run it in a maintenance window

dbcc checkdb (appdb) with no_infomsgs;