Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Full Backup with Multiple FileGroups


Create Full Backup with Multiple FileGroups

Author
Message
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Hello Everyone
I am in the process of writing a Full Backup Database script, but this database has multiple filegroups. The Database is set to Simple Recovery Mode. It has been ages since I have backed up a Multi FileGroup database.

No one installed the SQL Books Online on this server, and I am going to hang someone for that.

Can someone give me the code to create a Full Backup with Multiple FileGroups, that will backup each FileGroup?

This is what I have:

BACKUP DATABASE ProdDB
TO DISK = N'F:\Database Backups\ProdDB\ProdDB_Full.bak'
WITH COPY_ONLY
, NOFORMAT
, INIT
, NAME = N'ProdDB-Full Database Backup'
, SKIP
, NOREWIND
, NOUNLOAD
, STATS = 10
, CHECKSUM;


Yes, I know. Great naming convention at this place. I knew that I should not have taken this contract. Smile

Thank you in advance for all your assistance, suggestions and comments.

Andrew SQLDBA
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
That code will create a full backup that includes all filegroups.

You don't need the NOFORMAT, SKIP, NOREWIND, and NOUNLOAD options. They only apply to tape.

You should probably leave out the COPY_ONLY option, because it would prevent applying tran log or differential backups.

I would include the COMPRESSION option unless you have a good reason to not compress the backups.
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Thanks Michael
Those other options are what SSMS put there. I used the "wizard" and then did the "script to new window".

I did a test, but the size of the backup file was really small compared to the data file, that is why I was asking.

If I wanted to backup only one or two of the filegroups, would it be better to use the "File" Option, or the "FileGroup" option? That is where I am confused also.

Thanks for your assistance with this.

Andrew SQLDBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
Warning: Since the DB is in simple recovery, you can take file and file group backups at different times, but you won't be able to restore them (unless all but one are read only).

Stick to full backups unless you like surprises when it comes to restore.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Thanks Gila
I was reading about that. I don't always like surprises.

What about if the database were in Full Recovery Mode?

Thanks
Andrew SQLDBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
Then you can take any combination of file or filegroup backups and providing you have an unbroken log chain covering all of them, you can restore from multiple file/filegroup backups taken at different times.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3254 Visits: 11771
AndrewSQLDBA (10/2/2013)
Thanks Michael
Those other options are what SSMS put there. I used the "wizard" and then did the "script to new window".

I did a test, but the size of the backup file was really small compared to the data file, that is why I was asking.

If I wanted to backup only one or two of the filegroups, would it be better to use the "File" Option, or the "FileGroup" option? That is where I am confused also.

Thanks for your assistance with this.

Andrew SQLDBA


The backup file could be much smaller than the database files if you have a lot of free spaces in the data or log files. You can use the script on the following link to analyze the database file sizes and spaced used in detail.
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

It could also be smaller if the backup was compressed. The server level default could have been configured to compress backups, even if you did not specify it.


As Gail mentioned, it is probably best to stay away from file or filegroup backups. I have managed hundreds of servers myself, and have never had a need for it, so unless you have very special backup needs, you should avoid it.
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Thanks Michael
No special need, was only curious with this database, since there are multiple filegroups.

Andrew SQLDBA
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47271 Visits: 44392
I tend to agree with Michael. File/filegroup backups make backup times faster (because you're only backing up part of the DB each time), but they massively complicate restores. If you do that route test carefully before hand, make sure you're completely comfortable with the restore process.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


dan-572483
dan-572483
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: General Forum Members
Points: 643 Visits: 1958
GilaMonster (10/2/2013)
Warning: Since the DB is in simple recovery, you can take file and file group backups at different times, but you won't be able to restore them (unless all but one are read only).

Stick to full backups unless you like surprises when it comes to restore.


What if the Recovery model is full? We've been discussing reducing backup times & space by doing nightly backups of filesgroups containing current data only, and then backup the archive filegroups only after the archiving job runs, which isn't very often. But if this makes a full restore problematic, it's not something we should do.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search