Partial Backups excluding a read/write filegroup?

Question

Post reply

Partial Backups excluding a read/write filegroup?

Jonathan AC Roberts

SSCoach

Points: 18271
More actions
June 12, 2019 at 9:57 am

#3652658

Is it possible to reduce the size of backups by excluding very large tables from the backup by moving them to a new filegroup and doing a partial backup excluding the file group they reside on? (the tables are read/write). If so, can the database be restored without that filegroup?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

I've done this but not the way you want. It's super simple, though. Create another database, move your large table to that, and create a synonym in the original database named the same as the original table. You won't need to change a lick of code anywhere. The only problem is that you can't do DRI across databases so that's a consideration. You can, however, replace the likes of FKs with Instead of Triggers, which can be made to be pretty efficient if they are needed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 3

Jeff Moden wrote:

I've done this but not the way you want. It's super simple, though. Create another database, move your large table to that, and create a synonym in the original database named the same as the original table. You won't need to change a lick of code anywhere. The only problem is that you can't do DRI across databases so that's a consideration. You can, however, replace the likes of FKs with Instead of Triggers, which can be made to be pretty efficient if they are needed.

Thanks Jeff, That was my initial idea for doing it too. Then I found out about partial backups, but it seems these are only suitable for excluding read-only file groups from backups. So I think I'll go with the method you've suggested.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

Totally agreed and thanks for the the feedback, Jonathan. I also do the "partial backups" to exclude Read_Only file groups, sometimes in concert with having the large table in a separate database (partitioned, in this case). I was really excited to find out about "piece-meal" restores thinking along the same lines as your original post but that's not at all what they turn out to be. They're still very useful if one FG goes haywire though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)