SQL Server: Installation and Management on Windows Server

# Mount ISO and run setup
# Minimal SQL Server Express installation (no GUI)
.\setup.exe /Q /ACTION=Install /FEATURES=SQLEngine `
  /INSTANCENAME=MSSQLSERVER `
  /SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" `
  /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" `
  /TCPENABLED=1 `
  /IACCEPTSQLSERVERLICENSETERMS

# Download and install SSMS silently
$ssmsUrl = "https://aka.ms/ssmsfullsetup"
Invoke-WebRequest -Uri $ssmsUrl -OutFile "C:\ssms-setup.exe"
Start-Process -FilePath "C:\ssms-setup.exe" -ArgumentList "/install /quiet" -Wait

# Enable TCP/IP protocol via registry
Import-Module SQLPS -DisableNameChecking

# Or use SQL Server Configuration Manager (GUI):
# Start → SQL Server Configuration Manager → SQL Server Network Configuration
# → Protocols for MSSQLSERVER → TCP/IP → Enable

Restart-Service MSSQLSERVER

New-NetFirewallRule -DisplayName "SQL Server" `
  -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

-- Run in SSMS as sysadmin
ALTER LOGIN sa ENABLE;
ALTER LOGIN sa WITH PASSWORD = 'YourSecurePassword123!';
GO

-- Enable mixed mode (Windows + SQL Authentication)
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer',
  N'LoginMode', REG_DWORD, 2;
GO

CREATE DATABASE MyDatabase
ON PRIMARY (
  NAME = MyDatabase,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf',
  SIZE = 100MB,
  MAXSIZE = 10GB,
  FILEGROWTH = 100MB
)
LOG ON (
  NAME = MyDatabase_log,
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyDatabase_log.ldf',
  SIZE = 20MB,
  MAXSIZE = 2GB,
  FILEGROWTH = 50MB
);
GO

-- Create SQL login
CREATE LOGIN my_user WITH PASSWORD = 'Password123!';

-- Create user in database
USE MyDatabase;
CREATE USER my_user FOR LOGIN my_user;

-- Assign permissions (db_owner = all permissions on DB)
ALTER ROLE db_owner ADD MEMBER my_user;
-- Or limited permissions:
ALTER ROLE db_datareader ADD MEMBER my_user;
ALTER ROLE db_datawriter ADD MEMBER my_user;
GO

BACKUP DATABASE MyDatabase
TO DISK = 'C:\Backup\MyDatabase_full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;
GO

-- Create nightly backup job (via SSMS: SQL Server Agent → Jobs → New Job)
USE msdb;
EXEC sp_add_job @job_name = 'Nightly Backup MyDatabase';
EXEC sp_add_jobstep @job_name = 'Nightly Backup MyDatabase',
  @step_name = 'Backup',
  @command = 'BACKUP DATABASE MyDatabase TO DISK = ''C:\Backup\MyDatabase.bak'' WITH FORMAT, COMPRESSION';
EXEC sp_add_schedule @schedule_name = 'Every night at 2',
  @freq_type = 4, @freq_interval = 1,
  @active_start_time = 020000;
EXEC sp_attach_schedule @job_name = 'Nightly Backup MyDatabase',
  @schedule_name = 'Every night at 2';
EXEC sp_add_jobserver @job_name = 'Nightly Backup MyDatabase';
GO

-- First take DB offline if exists
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backup\MyDatabase_full.bak'
WITH REPLACE, RECOVERY, STATS = 10;
GO

# SQL Server service status
Get-Service MSSQLSERVER

# Restart
Restart-Service MSSQLSERVER

# Size of all databases
Invoke-Sqlcmd -Query "SELECT name, size*8/1024 AS size_MB FROM sys.master_files WHERE type=0" -ServerInstance "localhost"

# List databases
Invoke-Sqlcmd -Query "SELECT name, state_desc FROM sys.databases" -ServerInstance "localhost"

# Active connections
Invoke-Sqlcmd -Query "SELECT * FROM sys.dm_exec_sessions WHERE is_user_process=1" -ServerInstance "localhost"

-- Rebuild all fragmented indexes (run weekly)
EXEC sp_MSforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)';

-- Update statistics
EXEC sp_updatestats;

-- Check database integrity
DBCC CHECKDB (MyDatabase) WITH NO_INFOMSGS;
GO