Every SQL server database installation should consist of at least 3 files. The primary, secondary, and a log file.
Primary Data File - ❌ Log file - ❌ Secondary Data File -❌ Backup file -❌
This can be used to spread the data across multiple disks or to hold different types of data.
Primary Data File
Log File
Secondary Data File
Backup File
This is the starting point of the database and points to the rest of the files in the database.
Every database must have this because data is not written directly to the database file.
This can be handy for data management and backup tasks and can also be used to improve performance.
Index
File Groups
Backup Files
It is a named collection of data files.
Data Group
Transaction Log
This is like a cockpit voice recorder which stores each transaction processed by the SQL Server.
Event Viewer
Commands are written here before they are executed on the database.
❌ can be recorded to the log to aid in recovery to that point.
What are the different backup types? ❌ ❌ ❌ ❌
This backups parts of the database that have changed since the last full backup.
Full
Bulk Copy
Differential
File/ File Group
Simple
This is a type of database that stores information about SQL Server as a whole. SQL Server uses these databases to operate and manage the system.
System Databases
User Databases
SQL Databases
Check all the System Databases
Master
Module
tempdb
msdb
distribution
APXFirm
APXController
This data type uses any combination of letters, symbols, and numbers.
char(n), varchar(n), varchar(max), text*
datetime, smalldatetime
bit
decimal (x,y)
Fields it to ❌ as Records is to ❌
