Sql-server – Recovery mode keeps reverting to full

awssql serversql-server-2016

I'm using SQL Server 2016 to manipulate some large research data-sets.

I keep setting the recovery mode to simple, however it keeps reverting somehow back to full. Any guidance or advice on how to make the change permanent?

The data are low value, easily reproducable, etc. so there's no need for full recovery.

Best Answer

SQL Server does not automatically change the recovery model. If you set it to simple, then see later it is set to full, either:

  1. Someone else changed it back to full
  2. The database has been restored from a backup where the database is set to full recovery
  3. There may be a scheduled agent job that is changing the recovery model.

From a comment on this answer:

This was in AWS and #3 was the case. In this case backups were enabled so they have a process that automatically changes the recovery model back to Full. Changing the backup retention to zero days allows it to stay in simple recovery.

Related Question