Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create Full Backup with Multiple FileGroups Expand / Collapse
Author
Message
Posted Tuesday, October 1, 2013 4:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
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. :)

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

Andrew SQLDBA
Post #1500620
Posted Tuesday, October 1, 2013 9:39 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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.



Post #1500645
Posted Wednesday, October 2, 2013 1:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
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
Post #1500678
Posted Wednesday, October 2, 2013 1:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
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 2008, MVP
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

Post #1500683
Posted Wednesday, October 2, 2013 1:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
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
Post #1500686
Posted Wednesday, October 2, 2013 2:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
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 2008, MVP
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

Post #1500688
Posted Wednesday, October 2, 2013 10:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:23 AM
Points: 3,108, Visits: 11,503
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.





Post #1500841
Posted Wednesday, October 2, 2013 11:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 9:21 AM
Points: 977, Visits: 3,358
Thanks Michael
No special need, was only curious with this database, since there are multiple filegroups.

Andrew SQLDBA
Post #1500867
Posted Wednesday, October 2, 2013 12:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
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 2008, MVP
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

Post #1500876
Posted Wednesday, October 2, 2013 12:24 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:00 PM
Points: 554, Visits: 1,621
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.
Post #1500877
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse