SQL 2016

Installation

• Feature: Database engine
• Default Instance ID: MSSQLSERVER
• SQL service account: alphabook\Service_SQL
• SQL collation: SQL_Latin1_General_C P1_CI_AS
• Authentication Mode: Mixed Mode
• Configure Data Directory
• Configure TempDB
• Install SQL Server Management Studio
• SQL Port: TCP 1433

Restore mode

• Simple (Checkpoint with truncate log)
  • It is not necessary for DBA to maintain and backup log
  • Restore to last backup
  • Non-critical
• Full (Checkpoint without truncate log)
  • DBA need to maintain and backup log
  • Point-in-time restore
  • Critical
• Bulk-logged (Best practice: switch to Bulk-logged mode before bulk operation, and switch back to Full mode after bulk operation)

Backup type

• Full database backup
• Differential backup
• Transaction log backup (https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/apply-transaction-log-backups-sql-server?view=sql-server-2017)
• Tail-Log backup (https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server?view=sql-server-2017)
• Copy-only backup (One time full backup and no touch with current backup plan)
• Note: To create a differential or a transaction log SQL Server database backup a full database backup has to exist.
• For example, a full backup can be scheduled every 24 hours, a differential backup can be performed every 5 hours, and a transaction log backup every 15 minutes.
• When restore, basically Restore Full backup -> Restore Differential backup -> Restore Log backup

Backup location

• Another server or network location

Backup verification

• USE TestDB
• GO
• BACKUP DATABASE TestDB
• TO DISK = 'F:\Backup\TestDB.bak'
• WITH CHECKSUM;

Test backup

• Validate if backup works in test environment

Schedule backup by using SQL Server Maintenance Plans

• Management -> Maintenance Plans -> Maintenance Plan Wizard
• Setup Schedule
• Select Maintenance Tasks (Check Database Integrity and Backup Database (Full))
• Specify Database

Restore

• USE master
• ALTER DATABASE TestDB
• SET SINGLE_USER
• WITH ROLLBACK IMMEDIATE
• GO
• RESTORE DATABASE TestDB
• FROM DISK = 'F:\Backup\TestDB.bak'
• WITH REPLACE
• GO
• USE master
• ALTER DATABASE TestDB
• SET MULTI_USER