SQL Server Database & Recovery Model

SQL Server: Type of Databases & Recovery Model

Type of Databases in SQL Server
There are two type of databases in SQL Server:

1. System Databases: SQL Server includes following system databases:
- master Database:
Records all the system-level information for an instance of SQL Server.
- msdb Database:
Is used by SQL Server Agent for scheduling alerts and jobs-history and all.
- model Database:
Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
- Resource Database:
Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
- tempdb Database:
Is a workspace for holding temporary objects or intermediate result sets.
2. User Databases: This includes databases created by users for any application.
Following are the queries to check all databases and status details:

On SQL Server 2000:

SELECT * FROM master.dbo.sysdatabases

On SQL Server 2005 and above:

SELECT * FROM sys.databases 
OR
 Sp_Helpdb

Using SSMS, Here are the Database Details

Recovery Model of Databases
SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

1. Simple:
- No log backups.
- Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.
Work loss exposure
- Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.
Point in time Recovery.
- Can recover only to the end of a backup.

2. Full:
- Requires log backups.
- No work is lost due to a lost or damaged data file.
Work loss exposure
- Normally none.
- If the tail of the log is damaged, changes since the most recent log backup must be redone.
Point in time Recovery.
- Can recover to a specific point in time, assuming that your backups are complete up to that point in time

3. Bulk Logged:
- Requires log backups.
- An adjunct of the full recovery model that permits high-performance bulk copy operations.
- Reduces log space usage by using minimal logging for most bulk operations.
Work loss exposure
- If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Otherwise, no work is lost.
Point in time Recovery.
- Can recover to the end of any backup. Point-in-time recovery is not supported.

T-SQL Command to check Recovery model for the databases

SELECT name, recovery_model_desc FROM sys.databases

To change the recovery model

USE master ;
ALTER DATABASE [Database_Name] SET RECOVERY FULL; GO

Using the SSMS to change the recovery model for the AdventureWorks database:

Follow Up Recommendations: After You Change the Recovery Model

-- After switching between the full and bulk-logged recovery models
- After completing the bulk operations, immediately switch back to full recovery mode.
- After switching from the bulk-logged recovery model back to the full recovery model, back up the log.
Note: Your backup strategy remains the same: continue performing periodic database, log, and differential backups.
-- After switching from the simple recovery model
- Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
Note: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
- Schedule regular log backups, and update your restore plan accordingly.
Important Note: If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
-- After switching to the simple recovery model
- Discontinue any scheduled jobs for backing up the transaction log.
- Ensure periodic database backups are scheduled.
- Backing up your database is essential both to protect your data and to truncate the inactive portion of the transaction log.
Recommended, for Critical Databases SET FULL Recovery model and schedule frequent t-log backup. So that we can recover data with minimal data loss.