March 12, 2009 at 1:06 pm
I just had an interview for a DBA position in CT, the IT Director asked me how do I backup a filegroup. I started to answer "with a full backup" but before I could get the word "Full" out of my mouth the guy went ahead and answered it for me, so then I thought I would drop some knowledge on him since I could tell he got that question from some website on the internet, anyway I told him, "Yes, with a full backup because full backup of the database filegroup is just like getting a full backup of the database", which is true
The dumbass immediately told me this interview was over!!!! I was like you have to be kidding me?
I have worked as a DBA for the past 6 years and some dumbass asked me kiddie DBA questions to try and insult my intelligence, which I played along and answer all correctly, before that he preceded by saying they already had individuals who they wanted to interview further, I was thinking why the heck did you waste my time and your time trying to interview me?
You have to watch out for some these people who hold interviews, most no nothing and if you have an interview and the interviewer jumps straight to the questions like what happened to me, ask to end the interview because they have already found some other candidates that they want to further the process with.
March 12, 2009 at 1:48 pm
frankivey (3/12/2009)
"Yes, with a full backup because full backup of the database filegroup is just like getting a full backup of the database", which is true
It is?
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
March 12, 2009 at 2:05 pm
A full database backup is different than a full file group backup. Yes, a full backup does get all of the filegroups (because the database is spread across them). A full file group backup doesn't necessiarly entail getting the whole database though. By default a database is composed of one filegroup, but it can have many.
Here's the hierarchy:
Database is made up of file groups are made up of files.
A full file group backup only allows the restore of that particular filegroup.
Cheers,
Brian
March 12, 2009 at 2:12 pm
I'm no DBA and I've done little involving filegroups or database backups, but I know that "with a full backup" is incorrect.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 12, 2009 at 2:42 pm
sorry guy but you are wrong, you back ndf file extension with a full backup.
You just proved you don't know what you are talking about.
Maybe this will help you understand
March 12, 2009 at 2:47 pm
Hopefully he was referring to using SSMS and not Transact SQL.
http://msdn.microsoft.com/en-us/library/ms179401.aspx
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 12, 2009 at 2:48 pm
GilaMonster (3/12/2009)
frankivey (3/12/2009)
"Yes, with a full backup because full backup of the database filegroup is just like getting a full backup of the database", which is trueIt is?
Yes it is, I explained in detail that both backups include the system tables and objects, sorry I did not go into detail here but yes, I did explain in detail why a full backup of a filegroup is just like getting a full backup of the database.
Now, tell me I am wrong and please prove, because based on Microsoft, doing a full backup allows you to restore the database fully in the same way. The only difference is with backing up ndf files you can restore those back across different logical drives.
Please prove to me I am wrong, I do this stuff for living so I know I am right
March 12, 2009 at 2:51 pm
beezell, yes you are right with that aspect but question was asked with the understood intent that the database is spread across the filegroup!
March 12, 2009 at 2:56 pm
DavidB (3/12/2009)
Hopefully he was referring to using SSMS and not Transact SQL.
who the heck knows, lol.
I just had to check to make sure I was right.
March 12, 2009 at 3:01 pm
frankivey (3/12/2009)
Yes it is, I explained in detail that both backups include the system tables and objects, sorry I did not go into detail here but yes, I did explain in detail why a full backup of a filegroup is just like getting a full backup of the database
Not at all.
A full database backup backs all the files and filegroups up in one operation. It allows you to restore the full database or (from 2005 onwards) one or more files of filegroups.
A restore of a full database backup allows you to move one or more files as part of the restore.
A filegroup backup backs a single filegroup of the database. It can be used to restore only that filegroup. Like with the full, restoring said filegroup backup allows you to move the files in that filegroup to different locations on restore.
There's also the file backup that allows you to backup one or more files.
Only a full database backup or a backup of the primary filegroup will contain the system objects. Backups of the other filegroups won't. Hence if restoring from filegroup backups, primary has to be restored first, then the other filegroups. This is the key to partial database availability.
Filegroup and file backups are only allowed in bulk-logged or full recovery models as, in simple, there would be no way to get the database to a consistent state if pieces were backed up at different times. The exception to that is a read-only filegroup.
The syntax for the various options (assume a DB called DB1 with 2 filegroups (Primary and fg1) both with a single file (f1 and f2)
BACKUP DATABASE DB1 TO DISK ... -- Backs up the entire database
BACKUP DATABASE DB1 FILEGROUP = 'PRIMARY' TO DISK -- Backs up just the primary filegroup
BACKUP DATABASE DB1 FILE = 'f2' TO DISK -- Backs up just file2.
To make matters really complex the database, filegroup and file backups can be full or differential
If you want proof, try Books Online.
A full file backup backs up all the data in one or more files or filegroups. Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.
Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database. For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored.
The files in a database can be backed up and restored individually. In a BACKUP or RESTORE statement, you can specify a whole filegroup instead of individually specifying each constituent file. Be aware that if any file of a filegroup is offline (for example, because it is being restored), the whole filegroup is offline and cannot be backed up.
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
March 12, 2009 at 3:03 pm
frankivey (3/12/2009)
GilaMonster (3/12/2009)
frankivey (3/12/2009)
"Yes, with a full backup because full backup of the database filegroup is just like getting a full backup of the database", which is trueIt is?
Yes it is, I explained in detail that both backups include the system tables and objects, sorry I did not go into detail here but yes, I did explain in detail why a full backup of a filegroup is just like getting a full backup of the database.
Now, tell me I am wrong and please prove, because based on Microsoft, doing a full backup allows you to restore the database fully in the same way. The only difference is with backing up ndf files you can restore those back across different logical drives.
Please prove to me I am wrong, I do this stuff for living so I know I am right
Sorry, but a casual read about Filegroup backups seems to indicate to me that they are not the same as a Full-Backup. Of course I may be wrong, but then I don't work in an environment where we use filegroup backups so i have no direct experience at this time.
March 12, 2009 at 3:07 pm
frankivey (3/12/2009)
sorry guy but you are wrong, you back ndf file extension with a full backup.
Or a filegroup backup of the filegroup that contains that ndf or a file backup of just that ndf
You just proved you don't know what you are talking about.
Maybe this will help you understand
From that exact article
In the Select Files and Filegropus dialog box, select the files and filegroups you want to back up. You can select one or more individual files or check the box for a filegroup to automatically select all the files in that filegroup.
By selecting specific files of filegroups, it's now a partial database backup, not a complete database backup. True, the GUI doesn't make that clear, but with 6 years of DBA experience you should be well acquainted with the T-SQL options for backups.
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
March 12, 2009 at 3:10 pm
you didnt get the job then?
full filegroup backup is different to full database backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2009 at 3:11 pm
frankivey (3/12/2009)
The dumbass immediately told me this interview was over!!!! I was like you have to be kidding me?I have worked as a DBA for the past 6 years and some dumbass asked me kiddie DBA questions to try and insult my intelligence
Please prove to me I am wrong, I do this stuff for living so I know I am right
If you displayed that attitude in an interview, I probably wouldn't hire you either.
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
March 12, 2009 at 3:27 pm
GilaMonster (3/12/2009)
frankivey (3/12/2009)
Yes it is, I explained in detail that both backups include the system tables and objects, sorry I did not go into detail here but yes, I did explain in detail why a full backup of a filegroup is just like getting a full backup of the databaseNot at all.
A full database backup backs all the files and filegroups up in one operation. It allows you to restore the full database or (from 2005 onwards) one or more files of filegroups.
A restore of a full database backup allows you to move one or more files as part of the restore.
A filegroup backup backs a single filegroup of the database. It can be used to restore only that filegroup. Like with the full, restoring said filegroup backup allows you to move the files in that filegroup to different locations on restore.
There's also the file backup that allows you to backup one or more files.
Only a full database backup or a backup of the primary filegroup will contain the system objects. Backups of the other filegroups won't. Hence if restoring from filegroup backups, primary has to be restored first, then the other filegroups. This is the key to partial database availability.
Filegroup and file backups are only allowed in bulk-logged or full recovery models as, in simple, there would be no way to get the database to a consistent state if pieces were backed up at different times. The exception to that is a read-only filegroup.
The syntax for the various options (assume a DB called DB1 with 2 filegroups (Primary and fg1) both with a single file (f1 and f2)
BACKUP DATABASE DB1 TO DISK ... -- Backs up the entire database
BACKUP DATABASE DB1 FILEGROUP = 'PRIMARY' TO DISK -- Backs up just the primary filegroup
BACKUP DATABASE DB1 FILE = 'f2' TO DISK -- Backs up just file2.
To make matters really complex the database, filegroup and file backups can be full or differential
If you want proof, try Books Online.
A full file backup backs up all the data in one or more files or filegroups. Under the full recovery model, a complete set of full file backups, together with enough log backups to span all the file backups, is the equivalent of a full database backup.
Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database. For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored.
The files in a database can be backed up and restored individually. In a BACKUP or RESTORE statement, you can specify a whole filegroup instead of individually specifying each constituent file. Be aware that if any file of a filegroup is offline (for example, because it is being restored), the whole filegroup is offline and cannot be backed up.
Sorry, he was pertaining to Sql Server 9, all my questions were SQL Server 9 related. You even qouted yourself that you can restore a database from a filegroup. That is why I said what I said and again, I explained in detail how it is the same
Thank you for the rest of the information but I do know I can restore a database from a ndf file backup but as you said it is in SQL Server 9 and beyond, this question was talking about SQL Server 9.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply