olahallengren
Ability to take backup only for specific file groups
A parameter to specify which filegroup to take backup for.
example - having moved nc indexes to their own file group, I now only want to take backups of primary and cdc filegroups.
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)?
Yes that would be ideal.
On Mon, Jun 4, 2018 at 4:10 PM, Ola Hallengren [email protected] wrote:
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)?
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/olahallengren/sql-server-maintenance-solution/issues/38#issuecomment-394482499, or mute the thread https://github.com/notifications/unsubscribe-auth/AGh1klwiqY3dQmMNv86Oqh2O2xrGkGEYks5t5ZQegaJpZM4TNI2P .
-- Long live the Republic.
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.
For my scenario I was imaging a separate folder, like how currently there is full, log, diff. As for the file name I hadn't thought of that. Maybe replace full with fgxzy where xzy is first their characters of file group.
On Mon, Jun 4, 2018, 16:25 Ola Hallengren [email protected] wrote:
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.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/olahallengren/sql-server-maintenance-solution/issues/38#issuecomment-394487206, or mute the thread https://github.com/notifications/unsubscribe-auth/AGh1kp3hrFwGck4eKC0vJVIWW56H_7COks5t5ZfOgaJpZM4TNI2P .
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:
-
Perform one backup command, and specify the three filegroups. You would end up with one backup file.
-
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?
I'm a fan of option 2, between these two.
I was thinking would it be possible to have a negative parameter where you exclude what fg(s) you don't want backed up? and then have that in one backup command/file?
On Tue, Jun 5, 2018 at 7:44 AM, Ola Hallengren [email protected] wrote:
I was thinking some more about this. Let's say that you want to back up up the filegroups PRIMARY, FG1 and FG2, and you specify that in a filegroup parameter.
Then there are really two ways of doing this in the stored procedure:
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 fillegroup. 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?
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/olahallengren/sql-server-maintenance-solution/issues/38#issuecomment-394678932, or mute the thread https://github.com/notifications/unsubscribe-auth/AGh1kqqzcl34dThrXiHkP4ueZIQWNDbCks5t5m8TgaJpZM4TNI2P .
-- Long live the Republic.
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.
Would not option 1 basically include option 2, if you just specify one filegroup?
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.
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.
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.
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!
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.
I was thinking that you should be able to do things like this:
@FileGroups = 'ALL_FILEGROUPS, -Db1.FG2'
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."
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.
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: https://github.com/olahallengren/sql-server-maintenance-solution/pull/217
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.
I'm interested to know if there's still a need for support for file group backup. I've started doing some work on this. https://github.com/olahallengren/sql-server-maintenance-solution/tree/file_group_backup
Here’s a test version of DatabaseBackup with support for filegroup-level backup: https://github.com/olahallengren/sql-server-maintenance-solution/tree/file_group_backup
I’m looking for early testers to help validate the functionality. If you’re interested, feel free to reach out to me via email