0

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 .

3

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.

2

It is always good to have backups done with individual files (With date stamp). This will not only help you save diskspace but also be easier to do the cleanup. The cleanup can be done as per your retention period agreement(5 days).One big disadvantage being any mishap where the bak files gets corrupted. In this scenario you will have no useable backup while in other case you will have at least one good backup (Keep checking the for validity of backups). Use Ola-Hellengren's scripts which are the best way to configure backups locally.This ha all the required parameters and also the time stamp issue will be taken care.

Coming on to answering your questions :

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

There is not disadvantage in particular.This will only help you in better capacity management.

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

When backups are appended ,during restore you will have to check for the correct file from the listed ones (in SSMS) depending on the point in time you want to recover. This can be confusing at times and also the clean up is not possible. The backup files will be separate for each day so any file if gets corrupted then you will have at least one valid backup while if the appended file gets corrupted you might face issues in recovery.

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

Yes,it will. Check out this link for backup and restore of filestream database: MSSQLTips

7.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 ?

Individual backup files with Date-Stamp as this evades any confusion during recovery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.