SQLTeam.com | Weblogs | Forums

Full Recovery mode to Simple recovery


#1

Hi all,

I have to change the recovery mode from Full to Simple.
What are the steps involved in this process?

Do i have to delete existing transaction log before changing from Full recovery model to Simple recovery model.

Thanks in Advance


#2

If your database is in log shipping, changing to Simple recovery mode will brake log shipping.

You do not need to delete transactions but I would suggest backing them up before changing recovery mode.


#3

Right-click on the database name in SSMS object explorer, select properties, Options tab, and you can change the recovery model there.

Or, you could run a script to do the same

USE [master]
GO
ALTER DATABASE [YouDatabaseName] SET RECOVERY SIMPLE WITH NO_WAIT
GO

When you say "delete existing transaction log", I assume you mean the transaction log backups. If that is the case, you can simply delete them. In SIMPLE recovery model, there is no relevance or use for log backups.

I ask specifically about transaction log BACKUPs because there is also a transaction log file (usually with a file extension .LDF). DO NOT DELETE THOSE!! They are essential to the proper operation of the database.

Also, when you change the recovery model to simple, you lose the ability to do point in time recovery.


#4

Thanks djj55 and jamesk.
james: Yes, i was referring to transaction log backup.


#5

I guess the big question here is WHY??? You're going to break anything and everything that relies on the contents of the log file including point-in-time backups.


#6

Using SQL Server Management Studio
To view or change the recovery model
After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the database, and then click Properties, which opens the Database Properties dialog box.
In the Select a page pane, click Options.
The current recovery model is displayed in the Recovery model list box.
Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.
Click OK.
Using Transact-SQL
To view the recovery model
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. This example shows how to query the sys.databases catalog view to learn the recovery model of the model database.

tsql SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'model' ; GO

To change the recovery model
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. This example shows how to change the recovery model in the model database to FULL by using the SET RECOVERY option of the ALTER DATABASE statement.
tsql
USE master ;
ALTER DATABASE model SET RECOVERY FULL ;