Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've a SQL 2012 Server setup on a VM, with 3 disks:

  1. Disk 1: 150GB --> System
  2. Disk 2: 750GB --> SQL mdf
  3. Disk 3: 750GB --> SQL ldf

DBs are in full recovery mode. Let's say we'll have 350GB of data in the DBs. This is a SharePoint 2013 databases server, but I don't think this impacts the backup strategy to adopt here. The VM itself (and all its disks) is replicated via Hyper-V replicas.

Is the following backup strategy acceptable?

  1. I plan full data backups every week to Disk 3. As far as I understand, this flushes out all transaction logs. I then remove backups from previous week.
  2. I plan logs backups every 1 hour to disk 2. I remove log backups older than 1 week.

With this strategy, I should optimize my disk space, and at worse, I lose 1 hour data? Am I correct? Will the logs backups (every 1 hour) have a noticeable impact on my server performances when they occur?

Thanks,

share|improve this question

migrated from stackoverflow.com Mar 20 at 20:35

This question came from our site for professional and enthusiast programmers.

2  
If the server fails entirely, which is to be considered as a realistic scenario, your backups are gone. You need off-server storage. Also: brentozar.com/archive/2014/02/… –  usr Mar 19 at 21:13
    
@usr Though he doesn't say it explicilty, I get the impression those weekly and nightly backups are also going to tape or cloud storage. But if not, then yes, they definitely should be. –  Joel Coehoorn Mar 19 at 21:18

1 Answer 1

up vote 2 down vote accepted

Ideally you'd add a 4th volume for backups, but I understand that's likely not an option. (FWIW, Sql Server does support making backups to network shares).

With that out of the picture, it sounds to me like you're trying to keep it so that losing volume 2 on any given day means you can recover the full backup on volume 3 and still have the ldf file valid for that full day to get you back to current, or that losing volume 3 would mean you can still replay the transaction log backups on volume 2 from that day to get back up to the last hour. However, I don't think this will work how you want. I think you'll end up with much larger ldf and transaction log files than you need, and Sql Server will want to keep a lot more of that ldf information in memory, hurting performance.

I think you will be better served by taking nightly full backups, and doing transaction log backups as often as performance and disk space allow (which could be fairly often... but you'll need to test to know). This should result in better overall performance and reliability for your system. I do think it is okay to separate which volumes you're using for each role, as you suggested.

The one other thing I would add is to look at where you're keeping your tempdb. Moving that around can have a big impact on things as well. You might find (just as one example) that keeping your database's mdf and ldf together on one volume with tempdb and backups on the other gives better results.

share|improve this answer
    
SQL Server will make backups to network shares. That's what I do. I run SQL as a domain user. –  David Crowell Mar 20 at 20:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.