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?


  • 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, 2014 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. Mar 19, 2014 at 21:18

1 Answer 1


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.

  • SQL Server will make backups to network shares. That's what I do. I run SQL as a domain user. Mar 20, 2014 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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