0

Our current system setup sees each server backed up by 3rd party software(Veeam). The backup for our Database server occurs once each night.

Alongside this, there is another 3rd party tool that performs database replication to a backup SQL Server.

As it stands, if a disaster were to occur then we automatically switch over to the replicated backup SQL Server. Then switch back once production has been fixed.

I am not sure if this is enough for DR. All our databases are currently in Simple mode. I feel that they should be in Full recovery mode, and Full/Differential/Log backups should be performed on a regular basis.

Or is the replication and Full backup enough for DR? I am basically trying to put an argument together in favour of Full/Differential/Log backups.

Any suggestions?

Thanks!

2 Answers 2

2

Veeam will give you full backups of your SQL Server VM which are not that useful when all you want to do is restore a database to see what a particular value was on a certain date. You don't want to de restoring an entire server to get at a database.

The replication gives a level of High Availability but it wouldn't save you from someone running a stupid update statement that wiped a load of valuable data.

You really need to be making regular full backups (nightly is normal) with log backups during the day depending on your RTO and RPO. If you don't know what your RTO and RPO is then you need to research these terms and sit down with management to make a decision on this. It's this decision that will determine your backup strategy and the recovery mode you run each database in.

There are so many reasons to list why you should be making regular SQL backups and retaining them but here are a few

You need to be retaining backups so you have the ability to look back and see what state your data was on a certain date. This can save your job.

Data corruption can hide in your data for a long time before it becomes an issue. If it does you will want to be able to go back before the point the corruption occurred.

It's your job to make sure your data is safe.

3
  • Thanks you! Would the Veeam backup cause issues with my Database backup chain? In the SQL Server logs I can see list of records detailing databases backed up.
    – K09
    Jul 22, 2014 at 12:10
  • AFAIK there is no Third party tool which can take transaction log backup and truncate the logs like inbuild TSQL code. If you are looking into point in time recovery please dont rely on third party tool. First define your RPO and RTO and then change recovery model to full and then according to RPO schedule differential and Log backups. If client agrees 24hrs data loss well than full backup is more than enough
    – Shanky
    Jul 22, 2014 at 12:22
  • @K09 As far as I know Veeam wont know anything about the SQL backups as it just backs up the .XVHD. Which tool are you using for replication? Jul 22, 2014 at 13:53
0

Thanks you! Would the Veeam backup cause issues with my Database backup chain? In the SQL Server logs I can see list of records detailing databases backed up

AFAIK there is no Third party tool which can take transaction log backup and truncate the logs like inbuild TSQL code. If you are looking into point in time recovery please dont rely on third party tools.

First define your RPO and RTO and then change recovery model to full( only when you require point in time recovery) and then according to RPO schedule differential and Log backups whichever suits your requirement or you can take combination of both.

If client agrees 24hrs data loss well than full backup is more than enough. Please also note that you can keep recovery model simple and take differential backups as well. So its not like that you need to make recovery model full to take differntial backup in simple recovery.

You need to simulate failure and show it to management that you can loose critical data even if you have couple of third party tools backing up your system.

2
  • 1
    RPO is 15mins; RTO is 1hr. So one Full Backup each day, Differential Backup every 30mins, Log Backup every 15mins(RPO). Does this sound correct? How do I assess how recovery will fit within the RTO 1hr limit?
    – K09
    Jul 22, 2014 at 14:49
  • As per RTO it seems fine. But nothing is full proof unless you test it like real world scenario. Now you asked good question 'How do I assess how recovery will fit within the RTO 1hr limit' answer would be do a DR testing or simulate a database or server failure and see if you can, withing 1 hour, recovery database with maximum of 15mins data loss.
    – Shanky
    Jul 22, 2014 at 14: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.