SQL Server Backup – Incremental Backup of SQL Server 2008 R2

backupsql server

Is it possible (out of the box or free software) to make an INCREMENTAL backup of SQL Server 2008.

My problem is, the full backup and differential backup, backs up my 30gig database to 1 file. So when I run my incremental backup to the cloud, if the 30gig database file has a change, it will upload the entire 30gig.

Ideally, I'd like to be able to make an initial full backup to 1 file, then every night make differential backups to A NEW FILE (or a new file once a month) so that the backup doesnt re-upload the entire file every night.

Best Answer

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;
Related Question