July 7, 2016 at 7:26 am
I want to change a database (which is a test database) to a 'Simple recovery model' and I was going to follow the following steps and I just wanted to confirm that I'm not missing anything else e.g. when would I worry about shrinking the transactional log files etc.?
1.Log all users off the database or change the database to ‘Read-Only’ to stop users accessing the database
2.Take a backup of the required database full database backup/log (for a rollback process)
3.Change the recovery model of the database to ‘Simple’ using the following script:
USE master;
ALTER DATABASE database_name SET RECOVERY SIMPLE;
GO
4. Remove the log backup from the scheduled job
July 7, 2016 at 8:03 am
You should be able to shrink the log files right after setting it to Simple.
Instead of Read_Only, I suggest setting it to Single_User With Rollback_Immediate. ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK_IMMEDIATE, if I remember the syntax correctly.
Then, when you're done, set back to Multi_User.
You can look up the exact commands and syntax under Alter Database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2016 at 8:05 am
Step 1 is completely unnecessary. Recovery model changes are online operations. You don't need to disconnect users and if you set the DB to read_Only, you won't be able to make the recovery model change.
Step 2, Ok if you want, but if this is a production DB, you're not going to roll back and lose all changes since the recovery model change. If you or others change their mind about the recovery model, you'd change it back to full and take a full/diff backup to initialise the log chain, not revert to before the change to simple and lose everything done since that time.
As for when to consider shrinking the log, probably never.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply