SQL Server Backup Proposal

Question

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!

Answer 1

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.

Answer 2

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.