Change the recovery model of a database in SQL

Page updated :

You can change the database recovery model from the screen using SQL Server Management Studio. To change the settings, right-click the target database from SQL Server Management Studio and select Properties from the menu. When the dialog opens, change the Recovery Model by selecting Options from the Select Page menu on the left.

SQL Server Management Studio から復旧モデル変更

This is usually fine, but sql server management studio may not be available, for example, if you are using a shared database in an external service. In that case, you can run SQL to change the recovery model.

If you want to make changes in SQL, run the following SQL: For external service databases, run them with the tools or programs that match the site.

ALTER DATABASE [データベース名] SET RECOVERY 復旧オプション;

For Recovery Options, enter one of the following:

FULL Full
BULK_LOGGED Mass log
SIMPLE Simple

You typically need master privileges to run SQL. External services are similar, but you may be granted change permissions for each account, so check for each external service.