SQL Server High Availability and Database Recovery Model Relationship

Recovery model

There are four types of high availability options available in Microsoft SQL server. All the options are different from each other. For example, Log shipping, Mirroring and Always On works on database level while Replication works on database objects level. The databases involved in these high availability feature must have a fixed type of recovery models else it is not possible to configure these options. Here I am briefing about the recovery models of the database which are involved in high availability.

HA & Recovery Models

How to check the recovery model of a database?

Method 1:  sp_helpdb

Method 2: select name as [Database Name], recovery_model_desc as [Recovery Model] from sys.databases

How to change the recovery model of a database?

Method: T-SQL

Alter database database name set recovery full/bulk logged/simple


alter database Mirza set recovery full



Earn Money


About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Always on, Database Administration, Replication, Sql Server, T-SQL. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s