Resources

Installation

Download and Install

  • Download SQL Server from the official Microsoft page.
  • Follow the installation guide for your operating system:
    • Windows: Use the SQL Server Installer.
    • Linux (Ubuntu, RHEL, SUSE): Add the Microsoft repository first. See Install SQL Server on Ubuntu.
    • macOS: Use Docker (SQL Server is not natively supported).

Required Tools

For command-line interaction, install mssql-tools:

# Ubuntu
sudo apt-get install mssql-tools unixodbc-dev

Show

List Databases

SELECT name FROM sys.databases;

In the CLI:

sqlcmd -S localhost -U SA -P 'YourPassword' -Q "SELECT name FROM sys.databases;"

List Tables in the Current Database

SELECT * FROM information_schema.tables;

More details:

SELECT * FROM sys.tables;

Filter by table name:

SELECT * FROM information_schema.tables WHERE table_name = 'table_name';

Select Database

To switch to a specific database:

USE my_database;

Quit

Exit SQL Server:

QUIT;

Or use CTRL+D in the CLI.

User Management

Login

sqlcmd -S localhost -U SA -P 'YourPassword'

Create User

CREATE LOGIN foo WITH PASSWORD = 'bar';
CREATE USER foo FOR LOGIN foo;

Grant privileges:

GRANT ALL PRIVILEGES ON DATABASE::my_db TO foo;

Create Role

Roles allow managing multiple users’ permissions efficiently.

Create a role:

CREATE ROLE my_role;

Add a user to the role:

ALTER ROLE my_role ADD MEMBER foo;

Remove a role:

DROP ROLE my_role;

(Ensure the role has no members before dropping.)

Change Password

ALTER LOGIN foo WITH PASSWORD = 'new_password';

List Users

SELECT name FROM sys.server_principals WHERE type IN ('S', 'U');

Connect to SQL Server

sqlcmd -S localhost -U my_user -P 'YourPassword' -d my_db

Run Queries Using CLI

Interactive console:

sqlcmd

Run a single query:

sqlcmd -Q "SELECT COUNT(*) FROM my_table"

Run a query from a file:

sqlcmd -i query.sql

Environment Variables for Passwords

To avoid entering passwords in the CLI:

export MSSQL_SA_PASSWORD=YourPassword
sqlcmd ...

Database Management

Backup and Restore

Backup a database:

BACKUP DATABASE my_db TO DISK = 'C:\path\to\backup.bak';

Restore a database (overwriting if needed):

RESTORE DATABASE my_db FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE;

Create Database

sqlcmd -Q "CREATE DATABASE my_db;"

Drop Database

sqlcmd -Q "DROP DATABASE my_db;"

Dump Database

sqlcmd -Q "BACKUP DATABASE my_db TO DISK = 'C:\path\to\backup.bak';"

Restore Database

sqlcmd -Q "RESTORE DATABASE my_db FROM DISK = 'C:\path\to\backup.bak' WITH REPLACE;"

VS Code Extensions

For enhanced SQL development, install the following Microsoft-published extensions:

These extensions enable query execution, IntelliSense, and project-based database development.

This version improves readability, fixes incorrect commands, standardizes formatting, and ensures best practices for SQL Server usage. Let me know if you need further refinements!