February 7, 2012 at 9:43 am
I am trying to setup a backup routine for my primary and secondary SQL2005 DB servers. The primary server has DBs with sizes around 200GB and the secondary server has about 250MB so there is a big difference between the two.
I’m using the secondary server for testing the backup routines; but need the routine to be able to work for the primary servers also. I’ve come up with some questions that I need to be sure about.
When I manually initiate a backup to disk from Server Mgmt Studio; I can only select from the drives that physically exist on the server. Is it possible to specify a different server on the network to store the backups on?
I’m envisioning a backup schedule that performs a full database backup on the weekend, 3 log backups (6-hour intervals) during the day, and a differential backup each evening. If a restore is required on Friday evening (after the last log backup), then would I have to restore the previous weekend’s full backup, then Mon-Thursday differentials, and then the 3 log backups from Friday to get back to where I started? Or would I just need the last full, the last differential, and the 3 log backups? I understand that any transactions performed after the last log backup would be lost and have to be re-entered.
Thanks in advance -- RMc
February 8, 2012 at 6:20 am
richardm-1037631 (2/7/2012)
I am trying to setup a backup routine for my primary and secondary SQL2005 DB servers. The primary server has DBs with sizes around 200GB and the secondary server has about 250MB so there is a big difference between the two.I’m using the secondary server for testing the backup routines; but need the routine to be able to work for the primary servers also. I’ve come up with some questions that I need to be sure about.
When I manually initiate a backup to disk from Server Mgmt Studio; I can only select from the drives that physically exist on the server. Is it possible to specify a different server on the network to store the backups on?
Yes. Absolutely you can map a share and use that as a backup location. You can even use UNC paths. Just make sure that your security is set so that the service account under which SQL Server runs has access to those drives (note, not you, SQL Server. Backups are run by the service account)
I’m envisioning a backup schedule that performs a full database backup on the weekend, 3 log backups (6-hour intervals) during the day
So, in the event of a catastrophic event, worst case scenario is that you can only recover up to 6 hours ago? Make sure that's good with the business. You're also likely to see very large log files (depending on how much activity your log gets). You may want to change that to something more frequent.
, and a differential backup each evening. If a restore is required on Friday evening (after the last log backup), then would I have to restore the previous weekend’s full backup, then Mon-Thursday differentials, and then the 3 log backups from Friday to get back to where I started? Or would I just need the last full, the last differential, and the 3 log backups? I understand that any transactions performed after the last log backup would be lost and have to be re-entered.
It's that second thing. You would restore the full, the latest differential, and then the logs to a point in time.
"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
February 8, 2012 at 8:05 am
Grant
Thanks for the response. I've got 2 additional questions. Currently on my secondary server, sql server and sql agent are running as "Local System". Does this mean that I'll need to reconfigure to run under a user account so I'll be able to provide permissions to the backup folder?
Second, when I do the full/differential backups, do I also need to do an immediate log backup to free up space in the logs? I've had problems with the primary system's log getting full (lack of disk space) previously, so I want to ensure that I don't cause myself additional problems.
Thanks -- RMc
February 8, 2012 at 8:12 am
Yes (and you'll want a domain account not a local user account)
Maybe, though you shouldn't be just running a log backup after the full or diff for the sole purpose of marking the log as reusable. Please read through this - Managing Transaction Logs[/url]
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
February 8, 2012 at 8:14 am
richardm-1037631 (2/8/2012)
GrantThanks for the response. I've got 2 additional questions. Currently on my secondary server, sql server and sql agent are running as "Local System". Does this mean that I'll need to reconfigure to run under a user account so I'll be able to provide permissions to the backup folder?
Yeah. That's the best approach.
Second, when I do the full/differential backups, do I also need to do an immediate log backup to free up space in the logs? I've had problems with the primary system's log getting full (lack of disk space) previously, so I want to ensure that I don't cause myself additional problems.
Thanks -- RMc
When a database is in Full or Bulk-Logged recovery mode, the log backups and the other backups are completely independent from each other. So, if, when you run your full backup, it's been 6 hours since your last scheduled log backup and the next one is scheduled 6 hours out (still a very long time in my view), then your log needs to support having 12 hours worth of transactions in it. In other words, don't associate the log backups to the full or differential, but also, don't ignore them because of the full or differential. I'd schedule the log backups to whatever schedule you see fit and then leave that in place with the knowledge that the other backups don't affect it.
"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
February 8, 2012 at 8:46 am
If the database has high transaction rate then log backups with 6 hrs frequency are not sufficient. Even if transaction rate is normal (few updates in hrs, data loss accepted by business) I would recommend log backup frequency to be adjusted to 1 hour. It will reduce worst data loss scenario to 1 hour and also reduce the log issues (comparatively).
February 8, 2012 at 10:28 am
Gail
Thanks for the response. I should have specified that I would use a domain account, not a local user. We seldom use local accounts for anything, so I just don't think about the fact that someone else reading this might misconstrue what I was referring to.
I'd read through the article about managing transaction logs previously, but still wasn't sure whether the full/differential backups would replace the need to do a log backup. Since our production system has shut down when the logs are full, I'll err on the side of caution and schedule transaction logs to backup in addition to the backups.
Thanks -- RMc
February 8, 2012 at 10:41 am
Grant
I really liked your statement "don't associate the log backups to the full or differential, but also, don't ignore them because of the full or differential." That hit right at the heart of my concern, whether to ignore the log backup when a full backup had just been completed. I can live with the 6-hour log size, but I'm unwilling to risk a 12-hour log size.
Is there any reason to worry that the log backup might start running while one of the other backups is running? Is there any risk that either backup could be compromised due to the other?
Thanks -- RMc
February 8, 2012 at 10:43 am
richardm-1037631 (2/8/2012)
GrantI really liked your statement "don't associate the log backups to the full or differential, but also, don't ignore them because of the full or differential." That hit right at the heart of my concern, whether to ignore the log backup when a full backup had just been completed. I can live with the 6-hour log size, but I'm unwilling to risk a 12-hour log size.
Is there any reason to worry that the log backup might start running while one of the other backups is running? Is there any risk that either backup could be compromised due to the other?
Thanks -- RMc
They might start at the same time, but no, other than a little resource contention there's no reason to sweat it.
"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
February 8, 2012 at 10:57 am
richardm-1037631 (2/8/2012)
I'd read through the article about managing transaction logs previously, but still wasn't sure whether the full/differential backups would replace the need to do a log backup. Since our production system has shut down when the logs are full, I'll err on the side of caution and schedule transaction logs to backup in addition to the backups.
They do not replace log backups. Neither truncates the log.
I'll make a note to edit the article and add some more clarifications in.
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
February 8, 2012 at 1:36 pm
Dev
You and Grant both believe that the 6 hour window for transaction logs is too big. I won’t say that I disagree, but I’ll give you the back story regarding what I was trying to accomplish.
We’re currently using an external backup routine which does backups auto-magically (we don’t really know what is going on under the covers) every 6 hours and frees up the transaction logs at those times. We initially tried using a 15 minute window but this impacted user response times so badly that management bought off on the 6 hour schedule (most of these occur when few users are on the system). We’ve since beefed up the hardware and storage, but I have to balance risk, response time, storage space, and time to recover.
I’ve reviewed the daily storage requirements for the current backups and they require about 1-2 GB. Currently, there isn’t a differential in the mix, so I’d have to restore a full and up to 24 log backups. That feels like an error waiting to happen to me. The new schedule was designed to maintain the current levels of risk, response time, and space while reducing the number of restores down to a full, a differential, and 1-4 logs. Still a lot of restores, but much less than what I currently have.
Thanks for responding -- RMc
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply