September 22, 2021 at 3:29 pm
Ran into an issue that caused me some issues. It seems that when I backup a database that is in FULL recovery mode and restore it to a different server (just did a large migration to new servers), the database restores in to SIMPLE recovery mode. Before SQL 2017, I never saw this behavior, what gives, this is kind of a big deal and I can't find anything in my searches to say that there is a reason for this change or that anybody else has even noticed. Did I miss something?
September 22, 2021 at 7:11 pm
I have never encountered that. Is it possible some step is running after the restore that changes the recovery model ?
September 22, 2021 at 7:32 pm
Or is it possible that the backup process is switching the recovery model before performing backup - then switching it back to full? Those are the only reasons I can think of for a database being restored in the simple recovery model.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2021 at 7:52 pm
They were standard backups and standard restores. Have done this many, many times in the past and never have I seen this behavior. Going to take some time tomorrow in our test area to see if I can purposely cause it to happen and try to get some more info on exactly may have caused it. We ran a regular FULL backup on all db's, then restored with out recovery to the new servers, then the next day, during cutover, we ran DIFF backups, then restored those with RECOVERY. It was that simple.
September 22, 2021 at 9:50 pm
Just out of curiosity, what is the recovery model on the model db?
I'm wondering if the db got created then restored over ?!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 23, 2021 at 12:09 pm
I'm leaning toward what Scott says. Check the model database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2021 at 4:04 pm
Ran into an issue that caused me some issues. It seems that when I backup a database that is in FULL recovery mode and restore it to a different server (just did a large migration to new servers), the database restores in to SIMPLE recovery mode. Before SQL 2017, I never saw this behavior, what gives, this is kind of a big deal and I can't find anything in my searches to say that there is a reason for this change or that anybody else has even noticed. Did I miss something?
What are you using to do the restore?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply