Restoring a database, changing recovery model

Question

I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing. The production database uses full recovery model, but the test database would be better off with a simple recovery model. So I thought to change the script, and after the database reload, running on the test database the commands:

USE master ;
ALTER DATABASE TESTDB SET RECOVERY SIMPLE ;

I read that the change in recovery model only has effect after a backup is taken. However, if after the performing the above I do

SELECT name, recovery_model_desc
  FROM sys.databases

I can see that the recovery model is reported as simple for TESTDB. Of course to be sure I could take a backup of TESTDB (and discard it immediately), but I would avoid it if possible.

Do I need to take a backup to actually change the recovery model of my test database to simple?

Answer 1

I read that the change in recovery model only has effect after a backup is taken. However, if after the performing the above I do

This is mainly for when you change from simple recovery to full. After changing from simple to full you need to take full backup to take database out of Pseudo simple recovery. Pseudo simple recovery means even if database is in full recovery model it will behave like simple and automatic checkpoint would happen and you will not get point in time recovery. The moment you take full backup you tell SQL database engine that Now I would manage log truncations by taking transaction log backup.

Do I need to take a backup to actually change the recovery model of my test database to simple?

For your case since you are changed recovery model to simple so you DONT need to take any backup to actually make it behave in simple recovery. I am not talking about normal backup you take for your dev databases. You should continue taking normal full backup of your dev databases.