Sql-server – Incremental Backups In SQL server

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.

I think what you are doing is you are writing your full and differential backups to the same physical file, which just appends each differential (and perhaps even additional full backups) to the same physical file container.

What you should be doing instead is using a different physical file name each time. I like embedding the date (and time, if we're talking about backups that are taken more often, like log backups) into the filename, like so:

BACKUP DATABASE foo TO DISK = N'c:\backups\foo_full_20150526.bak'
  WITH INIT;

BACKUP DATABASE foo TO DISK = N'c:\backups\foo_diff_20150527.diff'
  WITH INIT, DIFFERENTIAL;

And yes, you can do this dynamically:

DECLARE @path SYSNAME = N'c:\backups\foo_diff_'
  + CONVERT(CHAR(8), GETDATE(), 112) + N'.diff';

BACKUP DATABASE foo TO DISK = @path WITH INIT, DIFFERENTIAL;