changing recovery mode

Question

changing recovery mode

Denise McMillan

SSCertifiable

Points: 6216
More actions
July 14, 2010 at 8:32 am

#224845

When you chagne the databse recovery mode, for example from FULL to SIMPLE, do you have to restart the SQL services for the change to take effect? Or is it just applied without restarting the services?
Thanks very much.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ray Mond SSCertifiable Points: 7159 More actions · Answer 1

No, you do not need to restart the SQL Server service. The change takes effect immediately.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

DaniSQL SSC Veteran Points: 222 More actions · Answer 2

The switch to Full/Bulk-logged recovery model takes effect only after you take a full backup. Check this article for more information. Also Paul Randall wrote on his blog an article/script New script: is that database REALLY in the FULL recovery mode?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

DaniSQL (7/14/2010)
The switch to Simple recovery model takes effect only after you take a full backup.

A switch to Simple takes effect immediately. It's a switch from simple to either Full or Bulk-logged that only really takes effect after a full or differential backup.

From the article you linked to:

If you must switch from the simple recovery model to the full recovery model, we recommend that you:
1. Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
The switch to the full or bulk-logged recovery model takes effect only after the first data backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DaniSQL SSC Veteran Points: 222 More actions · Answer 4

Thanks Gail. I edited my original post accordingly.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 5

Thanks, I didn't think I had to restart services but didn't see it actually stated anywhere and wanted to make sure.

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 6

One other note, the recovery mode is a database option, not a server option. I believe most db options are not service restarting changes. Some might take the db offline, but not the whole instance.

In this case, however, please pay attention to the need to take backups after full/bulkload switches. It's important.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com