RECOVEY MODEL IN SQL SERVER

 

Recovery model depend upon database levels and business levels required as per business requirement

data-critical or not required on the scenario we can set the database recovery model accordingly.

there are 3 types are recovery model in SQL server

• Full Recovery Model
• Bulk Logged Recovery Model 
• Simple Recovery Model

Full Recovery Model:-  Full recovery model supports all backup types like full, differential & transaction log backups are supported it is also support point in time recovery we can easily recover total data loss if any sort of incident got occurring features of High Availability supported like log shipping, Mirroring, clustering, replication, Always on Availability...

Bulk Logged Recovery Model:-

It is Similar to full recovery model but some restriction is there in bulk-logged recovery model it is while we are doing any bulk activity on that time minimally logged operation when we perform any bulk operation bulk log recovery model only store the data in buffer cache pages and write the MDF file complete record are not store in logfile while when we do any bulk operations only information is store in a log file. if we are doing any huge data operation we want to reduce the log file when we can choose or at that time We can change the recovery model after successfully done we can rollback the same it is not possible to recover the 100 % of data in the bulk-logged recovery model.

Database Highavilability feature only Logshiping & replication are supported mirroring and always-on Availability are not supported in the bulk-logged recovery model.

Simple Recovery Model:-

Simple Recovery Model:- only for system databases except for that model database  every transaction  store in the log file when a checkpoints occurs log file is truncated 

 we can take only the full and differential backups if any incident is happened only the options to restore the last full/differential backups.

Databases in sql server

Working With SQL Database

Databases in SQL Server Three types of databases are available. As a DBA working with only 2 types of databases, 

databases, 

These are the type of database we are using in the SQL server

1- System Define Databases.-   2-  User Define Databases.  3- Sample Databases

1. System Define Data Bases: - System databases store the complete Metadata info of SQL server we cannot deploy and Disaster Recovery Features like (Log Shipping, Database Mirroring, Always on Availability, Replication, etc...) But  Disaster recovery Features details are store in system define databases & directly we cannot do offline or de-attach the system define databases without system databases we cannot imagine running SQL. Here are the Some System Databases are...

   To Check SQL Server Databases with the state using this t-SQL command.

        select * from sys. databases

• Master Databases
• Model Databases
• Msdb  Databases
• Tempdb databases
• Resource Database
• Report Database
• Report Tempdb database
• Distribution Database

Master Databases in SQL Server:-

Master Database is the main database of SQL server it stores the SQL server user informactin , link server information server-level & database level settings details are store in the master database is the entry-level of SQL server when we restart SQL server start with the help of master database & tempdb database. when we install SQL server 2 copy of system databases are created  in SQL server  one copy of system databases are store in the data folder  and another copy of  system database are store in Bin Template folder .whenever you changes in any server or database level setting in SQL server Microsoft suggest to take backup Immediately of system databases 

Sof UserFul Point are Master Databases:-

• It store the Servrer Level Sttings Informaction.
• System Define error messages.
• Link server Details are store in the master Database.
• Backup Device Details are store in the master Database.
• Logins details.
• Endpoints.
• The default recovery Model is Simple.
• We can not de-attach Master Database
• Daily Full Backup is required We we do any changes to server level or database level Immediately Take backup for Sqfty pupruse 

SQL Server startup Parameters are  

Registry startup parameters: 

-d C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf

-e C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG

-l C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

Model Database:-

The Model database is the template database that SQL Server uses to create new databases. When we create the new user database in SQL server same setting like database recovery model , setting data file store path, data file and log file by default size database growth  size these types are setting are applied to the newly created database 

MSDB DATABASES

MSDB Database stores the automation level details like  SQL server agents detail , Job details, Job history detail backup and restores history details, database mail details, service broker details are store in the msdb database.

TEMPDB DATABASE

TempDB is a system database in Microsoft SQL Server used as a store of internal objects, row versions, work tables, temporary tables, and indexes. TempDB is available for use to all participants connected to a SQL Server instance (it is a global resource). ... Temporary tables are created with the # naming convention. Every time when we restart the SQL server it's created new with the help of the maser database we can not take backup temp DB.

Resource Database

The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys. objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Report Database & Report Tempdb

When we install SSRS Services in SQL by default install report DB & report tempdb database in SQL server 

 temporary database to store session and execution data, cached reports, and work tables that are generated by the report server.

User Databases:-  User database define or create by end-user & we can set applied setting accordingly like (attach,de-attach, online, offline,DB shrink, etc....).

Sample Database:-  Sample Database use for testing or learning purpose we can use it  Sample database are Adventureworks 2012,2014,2016,2017

We hope this article helps you know more about databases in SQL server. you may also want to see .--