661
As a database administrator, ensuring the recoverability of your SQL Server database is a top priority. This involves setting the proper recovery model, which determines how backups are maintained and how data can be restored. SQL Server databases have three recovery models: Full, Simple, and Bulk-Logged, each with its own features and use cases. In this blog post, let’s focus on how to view and modify the recovery model of your database using T-Sql.
To check the database recovery model
T-Sql
--Get the lists of the user database and it's recovery model
SELECT
[name],
recovery_model_desc
FROM
master.sys.databases
--WHERE
--[name] = 'dbatools'; --specify the db to filter the results
Output
To update the database recovery model
T-Sql
--Update the recovery model to FULL
ALTER DATABASE dbatools SET RECOVERY FULL
GO
--Update the recovery model to SIMPLE
ALTER DATABASE dbatools SET RECOVERY SIMPLE
GO
--Set to bulk logged recovery
ALTER DATABASE dbatools SET RECOVERY BULK_LOGGED
GO
Output
Note
For further information on this post, kindly refer to the original source provided in the links below.
Sources: microsoft, ms sql tips, sql server tutorial
