Simple vs. Full Backup Recovery Model
-
I just received access to the "primary" SQL server that we will be using for log shipping. We are using the "secondary" for reporting. I discovered that the database we need to use is set to simple recovery by default.
Since I am potentially modifying a system I didn't install, are there any high impact implications if I change this to "full." I have SA level access.
I need a practical read on the level of risk of me just changing this setting?
The machine is a VM, so I've asked my client to snapshot the machine for me (just in case), and have also made my own backup of the database.
-
The biggest impact will be a requirement to change your backup strategy for the database. Once you put it into Full Recovery, you'll HAVE to start taking Transaction Log backups, or the TLog will either grow to fill the disk (then things will stop working,) or grow until it hits its size limit (with the same result.)
That's really the biggest impact of changing it. Of course, the advantages are:
1. Now you can set up your log shipping
2. Now you can recover the database to a point-in-time if something goes corrupt
-
jasona.work (2/3/2016)
The biggest impact will be a requirement to change your backup strategy for the database. Once you put it into Full Recovery, you'll HAVE to start taking Transaction Log backups, or the TLog will either grow to fill the diskWhile that's true, if that's going to be the primary server in a log shipping config, then the log shipping will run the necessary transaction log backups. All that needs to be checked is that they run as scheduled.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Forgive my "greeness" on all of this...
I'm sure that the originating vendor of the solution we are log shipping from (and the customer) have no coordinated backup strategy. The managed service provider appears to have set up weekly backup jobs (they refer to full backups). Will my switching the recovery model break that?
What about bulk logged recovery model? Does that offer any benefits in terms of not forcing changes to backups? I'm trying to have as little impact on the primary server as possible. Backup or otherwise. I just want to set up log shipping and get out of there :Whistling:
-
kenny 40254 (2/3/2016)
Forgive my "greeness" on all of this...I'm sure that the originating vendor of the solution we are log shipping from (and the customer) have no coordinated backup strategy. The managed service provider appears to have set up weekly backup jobs (they refer to full backups). Will my switching the recovery model break that?
No, t-log backups do not affect full backups.
kenny 40254 (2/3/2016)
What about bulk logged recovery model? Does that offer any benefits in terms of not forcing changes to backups?Bulk logged is a short term model used for log backups during load procedures.
You need to be selecting full recovery model to support the log shipping plan, if not remove the LS plan.
As gail has pointed out the t log backups will be handled by a specific sql server agent job on the primary server, if you check it's history you should see failures for this job at present.
You'll need to find out how "out of sync" the primary and secondary are before just switching to full recovery model.
kenny 40254 (2/3/2016)
I'm trying to have as little impact on the primary server as possible. Backup or otherwise. I just want to set up log shipping and get out of there :Whistling:I do hope your client can't read this 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
OK, customer dba is fine with me switching recovery model to "full." Per them, their backup scripts will run fine (they are just doing standard backups). They are doing backups once a week.. I could use some feedback regarding how my log shipping might complement their backups
If I extend my "delete files" window (beyond the 3 day default) for the transactions logs to say 7 or 8 days, am I right to interpret that they could use the logs on the secondary box (my data center) to do a restore to any time in between their full backups?
Here's what the log shipping setup is on another customer's site...
-
kenny 40254 (2/7/2016)
If I extend my "delete files" window (beyond the 3 day default) for the transactions logs to say 7 or 8 days, am I right to interpret that they could use the logs on the secondary box (my data center) to do a restore to any time in between their full backups?Providing they have both the full backups and all log backups since the full, yes.
I'd put 2-week retention, to be safe. Just in case the latest full isn't restorable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
FYI, once you have switched it to Full recovery model, you will need to take one full backup of your database first before you can implement log-shipping on it.
-
kenny 40254 (2/7/2016)
OK, customer dba is fine with me switching recovery model to "full." Per them, their backup scripts will run fine (they are just doing standard backups). They are doing backups once a week.. I could use some feedback regarding how my log shipping might complement their backupsIf I extend my "delete files" window (beyond the 3 day default) for the transactions logs to say 7 or 8 days, am I right to interpret that they could use the logs on the secondary box (my data center) to do a restore to any time in between their full backups?
Here's what the log shipping setup is on another customer's site...
You'll also have a file retention period on the secondary server too, this controls the purging of files from the copy location on the secondary server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
@perry, how do you get to the secondary database retention settings on the secondary server?
Also, we ran into a situation over the weekend where something is conflicting the restore jobs on the secondary... we appear to have had a conflicting job that was processing the data from the database that was being log shipped to. Assuming that a situation like that is cleared, will the job that executes the restore just be smart enough to pick up where it left off?
-
@perry, I figured out where the secondary settings were.
-
kenny 40254 (2/8/2016)
Also, we ran into a situation over the weekend where something is conflicting the restore jobs on the secondary... we appear to have had a conflicting job that was processing the data from the database that was being log shipped to. Assuming that a situation like that is cleared, will the job that executes the restore just be smart enough to pick up where it left off?Is the secondary configuration set to disconnect users before the restore, otherwise the restore job will fail if the standby database is in use.
If the restores do not occur the process will pick up from the last backup to be applied once users have disconnected from the standby database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply