October 1, 2013 at 4:28 pm
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
October 1, 2013 at 9:39 pm
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.
October 2, 2013 at 1:41 am
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
October 2, 2013 at 1:53 am
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
October 2, 2013 at 1:57 am
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
October 2, 2013 at 2:05 am
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
October 2, 2013 at 10:23 am
AndrewSQLDBA (10/2/2013)
Thanks MichaelThose 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.
October 2, 2013 at 11:34 am
Thanks Michael
No special need, was only curious with this database, since there are multiple filegroups.
Andrew SQLDBA
October 2, 2013 at 12:21 pm
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
October 2, 2013 at 12:24 pm
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.
October 2, 2013 at 3:07 pm
GilaMonster (10/2/2013)
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
October 2, 2013 at 4:54 pm
Let's say I have a backup of the current data filegroup from 10/1 and a backup of the archive filegroup from 7/31. Does that mean to do a restore I need all the transaction log backup files since 7/31?
October 3, 2013 at 3:55 am
Yes, unless the archive filegroup was marked read only before that 7/31 backup and remained that way.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply