Sql-server – Backup Files with Append option growing huge Vs Better Options

backuprestoresql serversql server 2014

We have close to 5 Databases .

Below are the .mdf and .ldf sizes .

NOTE : Along with this we do also have the FileStream enabled for these Databases .

enter image description here

enter image description here

Kindly suggest me on below :

As of now we are using the third party tool for Backing up the data for these databases .

Most importantly they are appending backup files and now they have grown close to 800 GB .

After all , now I am just thinking of proposing the idea of daily individual files instead of appending the backup files daily and letting them grow huge .

I am thinking of it for two reasons .

  1. For Restore of the Database from backup file in a little comfortable way , than restoring a huge file .
  2. Next , I am thinking it will be easy to purge the backup files easily , I mean the old backup files .
  3. Backup file maintenance , we can maintain last 5 days or so and remove the previous ones .

Please let me know :

  1. What will be the disadvantage of my above plan .

  2. How is it different from appending backup files and daily individual backup files with Date Time Stamp appended to the filename daily basis .

  3. Will the Database backup also includes the FileStream Data ?

  4. How better I would have thought than what I am thinking now . Which is more recommended and better , Overwrite / Append / Individual Backup Files with Date-Time Stamp ?

Thanks for all your time .

Best Answer

Don't use append, it just makes that single file bigger and bigger, which makes it harder to manage. Also, if you need to restore from it, now you have to figure out which file number you need from inside (and more often than not, people don't realize there are multiple files, and they just run a restore from it and get the default file, not the one they expected).

I strongly recommend always creating independent full backups, and having the date/time embedded in the filename is useful for manual inspection / cleanup. Thankfully, you can parameterize the output filename, so you can build it dynamically with the current date (and time where appropriate). Date alone is useful when you're only doing single daily full backups and exact time is unimportant; times are likely more useful for log/diff backups. Here's a full backup with both date and time:

DECLARE @file nvarchar(512) = N'C:\temp\foo_' 
    + CONVERT(char(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(char(8), GETDATE(), 108),':','')
    + N'.bak';

BACKUP DATABASE foo TO DISK = @file
WITH INIT, COMPRESSION, STATS = 20;

For me this created the following backup file:

C:\temp\foo_20180403_082056.bak

And yes, this backup will include Filestream data.

Related Question