Ability to take backup only for specific file groups about sql-server-maintenance-solution HOT 19 OPEN

Based on the discussion in this thread I have created a pull request which adds single filegroup backup support, including support for backing up memory-optimized filegroups together with the PRIMARY filegroup: #217

from sql-server-maintenance-solution.

Would you like to be able to back up multiple filegroups in the same backup command and backup file (e.g. back up PRIMARY, FG1 and FG2)?

from sql-server-maintenance-solution.

from sql-server-maintenance-solution.

Any thoughts on how to do with directory paths and backup file names?

It would be nice to have the names of the filegroups in the directory path and / or file names, but then you run into the limitation for a backup device (259 characters).

I could make a token for the file groups, e.g. {FileGroups}. It may work in some cases, but in some cases it may be too long.

from sql-server-maintenance-solution.

from sql-server-maintenance-solution.

I was thinking some more about this. Let's say that you want to back up the filegroups PRIMARY, FG1 and FG2, and you specify that in a filegroup parameter.

Then there are two ways of doing this in the stored procedure:

1. Perform one backup command, and specify the three filegroups. You would end up with one backup file.

2. Perform three backup commands, one backup command for each filegroup. You would end up with three backup files.

Option 2. would make directory names and file names easier, as it is only one filegroup for each backup file.

What other pros and cons are there?

from sql-server-maintenance-solution.

from sql-server-maintenance-solution.

I think that the parameter could work the same as in DatabaseIntegrityCheck.
https://ola.hallengren.com/sql-server-integrity-check.html#FileGroups

That supports exclusions and also wildcards.

from sql-server-maintenance-solution.

Probably the best is option 2, that way you can handle fg(s) independently.

from sql-server-maintenance-solution.

Would not option 1 basically include option 2, if you just specify one filegroup?

from sql-server-maintenance-solution.

If you just specify one filegroup, then there is only one way of doing it.

The question is how it should work if you specify multiple filegroups.

from sql-server-maintenance-solution.

On the back paths and file names, I don't have an answer for you. If it's longer than 259 characters, I'd say disallow and give an error message saying combined length too long. Actual filegroup names are not hard to change. If people really need this functionality, they will deal with it.

from sql-server-maintenance-solution.

Option 2 would be my preference, where you can individually backup each filegroup as required. Maybe a top level directory of FG and then the filegroup name as the subdirectory. Thanks for looking into this Ola.

from sql-server-maintenance-solution.

In my opinion the second solution it the best and I think that the athurgar's idea to have a top level directory FG is good. After that I do not know if it is better to have subdirectories for each FG or not. Thanks Ola!

from sql-server-maintenance-solution.

One of the original questions I posed to Ola was the concept of removing a partition from a normal backup. Take Ola's example of PRIMARY, FG1 and FG2. Let's say that PRIMARY is 50GB of always used data, FG1 may be a special FG for XML data (for example) maybe 20GB and growing slowly, FG2 is for archived tables and is 1TB and only required for audit purposes - data never gets updated or deleted, maybe new data once a year. A normal backup would be 1070GB. But in reality I only want to backup PRIMARY and FG1 every night and not FG2. The only way that this can be accomplished at present is to have FG2 as its own database. But this does not meet the security requirements for this company that requires all the data to be in this one database. Nightly backups take forever - but really shouldn't need to as only 70GB of data needs to be backed up, not the extra 1TB.

from sql-server-maintenance-solution.

I was thinking that you should be able to do things like this:
@FileGroups = 'ALL_FILEGROUPS, -Db1.FG2'

from sql-server-maintenance-solution.

One complexity that I found is that primary and in-memory filegroups have to be backed up together.

"A BACKUP or RESTORE DATABASE statement that includes the primary filegroup must include the MEMORY_OPTIMIZED_DATA filegroup, and vice versa."

from sql-server-maintenance-solution.

Support for specific filegroups would be great. We currently do backups of individual filegroups which gives us some flexibility on the restore side. We can exclude certain filegroups if we want to create a metadata only copy of our databases (e.g. staging environment) without pulling backups for some of the other filegroups which can be quite large (blob data, logging etc).
We put all the filegroup backups into a single folder. We name the files with the following format:
{DatabaseName}FG{BackupType}{FileGroupName}{Date}{Time}_{FileNumber}of{NumberOfFiles}.BAK
e.g.
MyDatabase_FGFULL_MyFileGroup_20180725_120000_1of4.BAK

I have a SP to handle the filegroup backups for this specific DB but use your DatabaseBackup SP for the other DBs.

In terms of the backup I actually want all read/write filegroups but as separate backups for each filegroup.

from sql-server-maintenance-solution.

I was thinking that you should be able to do things like this:
@FileGroups = 'ALL_FILEGROUPS, -Db1.FG2'

We startet to use the filestream feature of sql server. And now we would like to seperate the filestream filegroup backup from the data filegroups backup. So options like

@FileGroups = 'ALL_FILEGROUPS, -Db1.FG2'

or

@FileGroups = 'Db1.FileStreamGroup'

would be fine.

from sql-server-maintenance-solution.