May 6, 2009 at 2:30 am
We have a situation where we want the recovery model to be full 8am - 10pm and then Simple 10pm - 8am.
The reason is that we import millions of rows (60gb) and do not want it sending via mirroring to our DR site as the data is discarded once processed or can be processed again if necessary.
Is this a foolhardy strategy?
If not what is the best way to implement it?
Many thanks.
Jason
May 6, 2009 at 2:48 am
If you have database mirroring, the recovery model cannot be changed from full. The only way you can achieve what you want is to completely drop the mirroring, switch the recovery to simple, then recreate the mirrored DB (from full backup) when you switch back to full.
Are you sure you want the DB in simple recovery overnight? That means no point in time recovery, should something fail around 7am, you would have to restore to last full backup. You'll also have to take a full backup every day after switching back to full recovery, or you won't be able to take log backups.
Most people switch to bulk-logged for bulk processing. The log still gets backed up with allows point-in-time recovery 1, but some operations can be minimally logged. Still not an option for mirrored DBs.
(1) Point in time recovery only if there's no bulk operation within the log backup, otherwise recovery only to the last log backup
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
May 6, 2009 at 3:18 am
Thanks very much for your answers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply