Components of a SQL Server Database
Data files in a SQL Server database refer to the individual data containers that are used to store the system and user-defined data and objects. Each database must have one, and only one, primary data file, typically denoted by the .MDF extension, which will be stored in the PRIMARY filegroup. It may also have some secondary data files, typically denoted by the .NDF extension.
Note that use of the .MDF and .NDF extensions are convention rather than necessity. Click here to see, What is MDF, NDF and LDF in SQL Server?
A filegroup is simply a logical collection of one or more data files. Every filegroup can contain one or more data files. The PRIMARY filegroup will always be created when you create a new database, and it must always hold your primary data file
There may also be occasions when we want to store just certain, specific user objects separately, outside the PRIMARY filegroup. To store an object in a secondary, rather than the PRIMARY, filegroup, we simply specify this during object creation, via the ON clause, as in the example below.
CREATE TABLE TableName
A transaction log file contains a historical account of all the actions that have been performed on your database. Transaction log files are typically denoted by the .LDF extension.
Each log record contains details of a specific action within the database (for example, starting a transaction, or inserting a row, or modifying a row, and so on). Click here to see, How to Shrink / Reduce the Size of the SQL Server Transaction Log?
Transactional durability and consistency
Via a Write Ahead Logging (WAL) mechanism that ensures that change descriptions are written to the transaction log before the actual data changes are written to the data files, SQL Server guarantees that all valid, committed data will be written to the data files and that the effects of any partial, uncommitted transactions, in the data file, can be “undone,” via transaction rollback.
SQL Server Backup Categories and Types
There are three broad categories of backup
1. Full database backup – backs up all the data and objects in the data file(s) for a given database.
2. Differential database backup – backs up any data and objects in data file(s) for a given database that have changed since the last full backup.
B. File backups – Backup of the data and objects in a data file or filegroup.
1. Full file backup – backs up all the data and objects in the specified data files or filegroup.
2. Differential file backup – backs up the data and objects in the specified data files or filegroup that have changed since the last full file backup.
3. Partial backup – backs up the complete writable portion of the database, excluding any read-only files/filegroups (unless specifically included).
4. Differential partial backup – backs up the data and objects that have changed since the last partial backup.
C. Transaction log backups – Backup of the log records inserted into the transaction log LDF file since the last transaction log backup.
D. Copy-only full backups
There is a special type of full backup, known as a copy-only full backup, which exists independently of the sequence of backup files required to restore a database, and cannot act as the base for differential database backups.
A recovery model is a database configuration option, chosen when creating a new database, which determines whether or not you need to (or even can) back up the transaction log, how transaction activity is logged.
By default, any new database will inherit the recovery model of the model system database.
There are three types of recovery model
SIMPLE recovery model – certain operations can be minimally logged. Log backups are not supported. Point-in-time restore and page restore are not supported. File restore support is limited to secondary data files that are designated as READONLY.
FULL recovery model – all operations are fully logged. Log backups are supported.
All restore operations are supported, including point-in-time restore, page restore and file restore.
BULK_LOGGED recovery model – similar to FULL except that certain bulk operations can be minimally logged. Support for restore operations is as for FULL, except that it’s not possible to restore to a specific point in time within a log backup that contains log records relating to minimally logged operations.