July 7, 2014 at 8:52 am
This question may seem stupid, but I want to get it straight right now.
I have @ 30 databases to tend to, all in the full recovery model. I want to use Full, Differential and Log backups so as to facilitate point in time recovery. None of these databases are very large, no more than 5-7GB so it shouldn't take overlong to restore them. I normally use the Enterprise manager because this is not my only gig and I just take care of things as they are needed.
So for example, if I take a Full backup at 0400, and I take a differential backup every 4 hours and tran log backups every 15 (or 30?) minutes, how does one go about scheduling this so that say a tran log AND differential backup don't kick off simultaneously? Or worse, a Full and Differential backup?
Does my question make sense?
July 7, 2014 at 9:01 am
It doesn't matter if they run simultaneously.
If you really don't want the full & diff to run at the same time (only one that makes sense to worry about), then schedule the diff to run every 6 hours, or every 4 hours starting at 2AM.
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
July 7, 2014 at 9:09 am
GilaMonster (7/7/2014)
It doesn't matter if they run simultaneously.If you really don't want the full & diff to run at the same time (only one that makes sense to worry about), then schedule the diff to run every 6 hours, or every 4 hours starting at 2AM.
Well, I had thought about that but since I've never had to "emergency" recover a database to a point in time yet, I wasn't sure how that worked. Looking at one on the Dev databases, when I select the PIT to restore to, it seems to line up all the full, diff and log files but I have yet to actually pull the trigger yet until I know what to expect. That same dialog box speaks about backing up the tail of the log as well, even though I am restoring it -- which was going to be another question actually.
I have been here 8 years without incident, but I guess I feel a bit underknowledged and I am trying to fix that BEFORE an ugly emergency happens lol..
July 7, 2014 at 9:17 am
Worst case, you go to restore a diff and SQL tells you that it's the wrong one, so you get the correct one and restore that.
Practice with scripts, the GUI requires that the MSDB database is intact with all your backup history. If it's not, you won't get that handy automatic restore list.
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
July 7, 2014 at 1:40 pm
Do some practice restores to another location. You can use the GUI to get all the settings in place, then "script to new query window".
This will show you the T-SQL, which you can save for future reference, and change as needed.
July 7, 2014 at 2:31 pm
homebrew01 (7/7/2014)
Do some practice restores to another location. You can use the GUI to get all the settings in place, then "script to new query window".This will show you the T-SQL, which you can save for future reference, and change as needed.
Hey, that's a great idea. Man, I still have a lot to learn!
July 8, 2014 at 6:41 am
For really tiny databases like this (and 7gb is tiny) I wouldn't bother with differentials. A full backup will run very fast and a full restore will run very fast. I'd absolutely still suggest log backups so you can do a point in time restore, but I'd only use differentials for bigger databases.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply