Beginner SQL DBA Cheat Sheet

As a budding SQL Database Administrator (DBA), mastering the fundamentals of SQL commands is essential. This cheat sheet covers the core areas you’ll need to get started: Backup and Restore, Indexing, Query Optimization, and Security. Let’s dive in!

Backup and Restore

Key Commands:

BACKUP DATABASE: Creates a backup of a database.
RESTORE DATABASE: Restores a database from a backup.
RESTORE WITH RECOVERY: Brings the database online after the restore is complete.
RESTORE WITH NORECOVERY: Leaves the database in a restoring state, allowing additional backups to be restored.

Example:

-- Backup the database
BACKUP DATABASE mydb TO DISK='C:\backup.bak';

-- Restore the database and bring it online
RESTORE DATABASE mydb FROM DISK='C:\backup.bak' WITH RECOVERY;

SQL

Indexing

Key Commands:

CREATE INDEX: Creates an index on a table.
DROP INDEX: Removes an index from a table.
ALTER INDEX: Modifies an existing index.
CLUSTERED INDEX: Determines the physical order of data in a table.
NONCLUSTERED INDEX: A separate structure from the table that contains a copy of the indexed columns.

Example:

-- Create an index on columns column1 and column2 in table1
CREATE INDEX idx_name ON table1 (column1, column2);

-- Drop the index idx_name from table1
DROP INDEX idx_name ON table1;

-- Rebuild the index idx_name on table1
ALTER INDEX idx_name ON table1 REBUILD;

SQL

Query Optimization

Key Commands:

EXPLAIN: Displays the execution plan for a query.
SET STATISTICS IO: Displays the number of physical and logical reads performed by a query.
SET STATISTICS TIME: Displays the time required to execute a query.
QUERY OPTIMIZATION: Involves techniques such as indexing, partitioning, and proper use of joins to improve query performance.

Example:

-- Display the execution plan for a query
EXPLAIN SELECT column1, column2 FROM table1 WHERE condition;

-- Turn on IO and TIME statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SQL

Security

Key Commands:

CREATE LOGIN: Creates a login for a user to connect to the server.
CREATE USER: Creates a user in a database.
GRANT: Grants permissions to a user or role.
DENY: Denies permissions to a user or role.

Example:

-- Create a login with a password
CREATE LOGIN mylogin WITH PASSWORD = 'mypassword';

-- Create a user for the login
CREATE USER myuser FOR LOGIN mylogin;

-- Grant SELECT permission on table1 to the user
GRANT SELECT ON table1 TO myuser;

-- Deny INSERT permission on table1 to the user
DENY INSERT ON table1 TO myuser;

SQL

Conclusion

This cheat sheet provides a quick reference to some of the most essential SQL commands you’ll need as a DBA. From backing up and restoring databases to optimizing queries and managing security, these commands will help you perform your role efficiently. Happy SQL-ing!