SQL Server administration and maintenance. Use for database backups, security, user management, maintenance tasks, monitoring, and troubleshooting.
Expert knowledge for SQL Server database administration, maintenance, security, and operational tasks.
CREATE DATABASE MyDatabase
ON PRIMARY (
NAME = 'MyDatabase_Data',
FILENAME = '/var/opt/mssql/data/MyDatabase.mdf',
SIZE = 100MB,
MAXSIZE = 1GB,
FILEGROWTH = 10MB
)
LOG ON (
NAME = 'MyDatabase_Log',
FILENAME = '/var/opt/mssql/data/MyDatabase_log.ldf',
SIZE = 50MB,
MAXSIZE = 500MB,
FILEGROWTH = 5MB
);
-- Change database name
ALTER DATABASE OldName MODIFY NAME = NewName;
-- Set recovery model
ALTER DATABASE MyDatabase SET RECOVERY FULL;
-- Options: SIMPLE, FULL, BULK_LOGGED
-- Set to single-user mode
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Set back to multi-user
ALTER DATABASE MyDatabase SET MULTI_USER;
-- Enable snapshot isolation
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Drop database (must not be in use)
DROP DATABASE MyDatabase;
-- Force drop (disconnect users first)
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MyDatabase;
-- List all databases
SELECT
name,
database_id,
create_date,
state_desc,
recovery_model_desc
FROM sys.databases
ORDER BY name;
-- Database size
EXEC sp_spaceused;
-- Database file information
SELECT
name,
physical_name,
size * 8 / 1024 AS SizeMB,
max_size
FROM sys.database_files;
-- Full database backup
BACKUP DATABASE MyDatabase
TO DISK = '/var/opt/mssql/backup/MyDatabase_Full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;
-- Full backup with name and description
BACKUP DATABASE MyDatabase
TO DISK = '/var/opt/mssql/backup/MyDatabase_Full_20241220.bak'
WITH
FORMAT,
COMPRESSION,
NAME = 'MyDatabase Full Backup',
DESCRIPTION = 'Full backup performed on 2024-12-20',
STATS = 10;
-- Differential backup (changes since last full backup)
BACKUP DATABASE MyDatabase
TO DISK = '/var/opt/mssql/backup/MyDatabase_Diff.bak'
WITH DIFFERENTIAL, FORMAT, COMPRESSION;
-- Transaction log backup (requires FULL recovery model)
BACKUP LOG MyDatabase
TO DISK = '/var/opt/mssql/backup/MyDatabase_Log.trn'
WITH FORMAT, COMPRESSION;
-- View backup file contents
RESTORE FILELISTONLY
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Full.bak';
-- Restore with replace
RESTORE DATABASE MyDatabase
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Full.bak'
WITH REPLACE,
MOVE 'MyDatabase_Data' TO '/var/opt/mssql/data/MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO '/var/opt/mssql/data/MyDatabase_log.ldf';
-- Restore to different database name
RESTORE DATABASE MyDatabase_Copy
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Full.bak'
WITH
MOVE 'MyDatabase_Data' TO '/var/opt/mssql/data/MyDatabase_Copy.mdf',
MOVE 'MyDatabase_Log' TO '/var/opt/mssql/data/MyDatabase_Copy_log.ldf';
-- Restore with recovery/norecovery
RESTORE DATABASE MyDatabase
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Full.bak'
WITH NORECOVERY; -- To apply more backups (diff/log)
-- Apply differential backup
RESTORE DATABASE MyDatabase
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Diff.bak'
WITH NORECOVERY;
-- Apply log backup and bring online
RESTORE LOG MyDatabase
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Log.trn'
WITH RECOVERY; -- Brings database online
-- Restore to specific point in time
RESTORE DATABASE MyDatabase
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Full.bak'
WITH NORECOVERY;
RESTORE LOG MyDatabase
FROM DISK = '/var/opt/mssql/backup/MyDatabase_Log.trn'
WITH STOPAT = '2024-12-20 14:30:00', RECOVERY;
-- SQL Server authentication
CREATE LOGIN john_doe
WITH PASSWORD = 'StrongP@ssw0rd!';
-- Windows authentication
CREATE LOGIN [DOMAIN\username]
FROM WINDOWS;
-- Create database user from login
USE MyDatabase;
CREATE USER john_doe FOR LOGIN john_doe;
-- Create user without login (for contained databases)
CREATE USER app_user WITH PASSWORD = 'StrongP@ssw0rd!';
-- Grant database role membership
ALTER ROLE db_datareader ADD MEMBER john_doe;
ALTER ROLE db_datawriter ADD MEMBER john_doe;
-- Grant specific permissions
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO john_doe;
GRANT EXECUTE ON dbo.GetCustomerOrders TO john_doe;
-- Grant schema permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO john_doe;
-- Grant database-level permissions
GRANT CREATE TABLE TO john_doe;
GRANT VIEW DATABASE STATE TO monitoring_user;
REVOKE SELECT ON dbo.Orders FROM john_doe;
REVOKE EXECUTE ON dbo.GetCustomerOrders FROM john_doe;
-- Explicitly deny (overrides grants)
DENY DELETE ON dbo.Customers TO john_doe;
-- Create custom role
CREATE ROLE SalesTeam;
-- Grant permissions to role
GRANT SELECT, INSERT, UPDATE ON SCHEMA::Sales TO SalesTeam;
-- Add users to role
ALTER ROLE SalesTeam ADD MEMBER john_doe;
-- Built-in database roles:
-- db_owner - Full control
-- db_datareader - SELECT on all tables
-- db_datawriter - INSERT, UPDATE, DELETE on all tables
-- db_ddladmin - CREATE, ALTER, DROP objects
-- db_backupoperator - Backup operations
-- User's permissions
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
-- Specific user's permissions
EXECUTE AS USER = 'john_doe';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;
-- User's role memberships
SELECT
USER_NAME(rm.member_principal_id) AS UserName,
USER_NAME(rm.role_principal_id) AS RoleName
FROM sys.database_role_members rm;
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count,
CASE
WHEN ips.avg_fragmentation_in_percent < 10 THEN 'No action needed'
WHEN ips.avg_fragmentation_in_percent < 30 THEN 'Reorganize'
ELSE 'Rebuild'
END AS Recommendation
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'LIMITED'
) ips
INNER JOIN sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE ips.page_count > 1000 -- Only indexes with significant pages
ORDER BY ips.avg_fragmentation_in_percent DESC;
-- Rebuild single index
ALTER INDEX IX_Orders_CustomerId ON Orders REBUILD;
-- Rebuild all indexes on table
ALTER INDEX ALL ON Orders REBUILD;
-- Rebuild with options
ALTER INDEX IX_Orders_CustomerId ON Orders
REBUILD WITH (
ONLINE = ON, -- Enterprise Edition only
MAXDOP = 4, -- Parallel processing
SORT_IN_TEMPDB = ON -- Use tempdb for sorting
);
-- Reorganize index (online operation)
ALTER INDEX IX_Orders_CustomerId ON Orders REORGANIZE;
-- Reorganize with LOB compaction
ALTER INDEX IX_Orders_CustomerId ON Orders
REORGANIZE WITH (LOB_COMPACTION = ON);
-- Update statistics for table
UPDATE STATISTICS Orders;
-- Update with full scan
UPDATE STATISTICS Orders WITH FULLSCAN;
-- Update specific index statistics
UPDATE STATISTICS Orders IX_Orders_CustomerId WITH FULLSCAN;
-- Update all statistics in database
EXEC sp_updatestats;
-- Comprehensive maintenance script
DECLARE @SQL NVARCHAR(MAX);
-- Rebuild fragmented indexes (>30%)
DECLARE index_cursor CURSOR FOR
SELECT
'ALTER INDEX ' + QUOTENAME(i.name) +
' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) +
' REBUILD;'
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
AND ips.page_count > 1000
AND i.name IS NOT NULL;
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SQL;
EXEC sp_executesql @SQL;
FETCH NEXT FROM index_cursor INTO @SQL;
END;
CLOSE index_cursor;
DEALLOCATE index_cursor;
-- Update statistics
EXEC sp_updatestats;
SELECT
session_id,
login_name,
host_name,
program_name,
status,
cpu_time,
total_elapsed_time / 1000 AS elapsed_seconds,
reads,
writes,
last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY cpu_time DESC;
SELECT
r.session_id,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time / 1000 AS elapsed_seconds,
r.reads,
r.writes,
r.blocking_session_id,
SUBSTRING(qt.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE r.session_id <> @@SPID -- Exclude current session
ORDER BY r.total_elapsed_time DESC;
-- Find blocking chains
SELECT
blocked.session_id AS blocked_session_id,
blocked_sql.text AS blocked_query,
blocker.session_id AS blocker_session_id,
blocker_sql.text AS blocker_query,
waits.wait_type,
waits.wait_time_ms
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocker
ON blocked.blocking_session_id = blocker.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
CROSS APPLY sys.dm_exec_sql_text(blocker.sql_handle) blocker_sql
LEFT JOIN sys.dm_os_waiting_tasks waits
ON blocked.session_id = waits.session_id;
-- Kill blocking session
KILL 53; -- session_id
-- Kill with rollback status
KILL 53 WITH STATUSONLY;
SELECT TOP 20
wait_type,
wait_time_ms / 1000 AS wait_time_seconds,
(wait_time_ms * 100.0) / SUM(wait_time_ms) OVER() AS percentage,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
-- Filter out benign waits
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
)
ORDER BY wait_time_ms DESC;
SELECT
DB_NAME() AS database_name,
name AS file_name,
type_desc,
physical_name,
size * 8 / 1024 AS size_mb,
(size * 8 / 1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024) AS free_space_mb,
CAST((FILEPROPERTY(name, 'SpaceUsed') * 100.0 / size) AS DECIMAL(5,2)) AS percent_used
FROM sys.database_files;
-- Read SQL Server error log
EXEC sp_readerrorlog;
-- Read specific error log
EXEC sp_readerrorlog 0; -- Current log
EXEC sp_readerrorlog 1; -- Previous log
-- Search for specific text
EXEC sp_readerrorlog 0, 1, N'error';
-- Enable trace flag for deadlock capture
DBCC TRACEON(1222, -1); -- Global
-- Read captured deadlocks from error log
EXEC sp_readerrorlog 0, 1, N'deadlock';
-- Turn off trace flag
DBCC TRACEOFF(1222, -1);
-- Check database integrity
DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS;
-- Check specific table
DBCC CHECKTABLE('Orders') WITH NO_INFOMSGS;
-- Update usage stats
DBCC UPDATEUSAGE('MyDatabase');
-- Free procedure cache
DBCC FREEPROCCACHE;
-- Clear wait stats (useful after solving issues)
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
-- Shrink database (avoid in production!)
DBCC SHRINKDATABASE('MyDatabase', 10); -- 10% free space
-- Shrink file
DBCC SHRINKFILE('MyDatabase_Log', 50); -- MB
# Connect with SQL authentication
sqlcmd -S localhost -U sa -P 'YourPassword'
# Connect with Windows authentication (if supported)
sqlcmd -S localhost -E
# Connect to specific database
sqlcmd -S localhost -U sa -P 'YourPassword' -d MyDatabase
# Execute query from command line
sqlcmd -S localhost -U sa -P 'YourPassword' -Q "SELECT @@VERSION"
# Execute script file
sqlcmd -S localhost -U sa -P 'YourPassword' -i script.sql
# Output to file
sqlcmd -S localhost -U sa -P 'YourPassword' -Q "SELECT * FROM Users" -o output.txt
# Use variable
sqlcmd -S localhost -U sa -P 'YourPassword' -v MyVar=Value -i script.sql
Within sqlcmd session:
-- List databases
SELECT name FROM sys.databases;
GO
-- Change database
USE MyDatabase;
GO
-- Execute script
:r script.sql
-- Set variable
:setvar MyVar "MyValue"
SELECT '$(MyVar)';
GO
-- Quit
EXIT
-- or
QUIT
SELECT
OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
OBJECT_NAME(p.object_id) AS TableName,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB,
SUM(p.rows) AS RowCount
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
GROUP BY p.object_id
ORDER BY TotalSpaceMB DESC;
SELECT
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID()
AND OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;
Use this skill when:
Simply mention database administration, maintenance, backups, users, or monitoring, and this knowledge will be applied.