A fun conversation about backups

While I was talking to one of my Jrs today about backups, a .Net guy poked his head around the corner to offer his opinion on the matter.  The subject was basically whether everything will be copied over if you do a full backup and restore it to another system.

Here’s basically how the talk went:

.NET – Well, it really depends on whether you have different filegroups as to whether everything will be restored.

DBA:  No, if it’s a full backup and it restores, everything is there.

.NET:  Well, yeah, but I’m just saying that DBs can have a lot of filegroups sometimes and if it does, then you might not get all o fthem.

DBA:  No, if it’s a full backup and it restores, everything is there.

.NET:  But…

DBA:  There are no buts… if it’s a full backup and it restores, everything is there.

.NET:  I’m just saying that…

DBA:  No, if it’s a full backup and it restores, everything is there.

.NET:  You can’t deny that there are several filegroups, right?

DBA:  I would never try to deny that.

.NET:  And if you backup those different filegroups, then you can only restore some of them, therefore you can have a DB with some filegroups unrestored.

DBA:  This is correct.

.NET:  So back to my original…

DBA:  No, if it’s a full backup and it restores, everything is there.

.NET:  No, because you just said that you can backup different filegroups and restore only part of them.

DBA:  Yes I did.

.NET:  So aren’t we saying the same thing?

DBA:  No, not even close.

.NET:  Why not?

DBA:  Because we were talking about full backups, not filegroup backups.  Full backups backup everything… it’s in the name.  Filegroup backups only backup filegroups… that’s also in the name.  But a full backup can only be restored fully.  There is no partial restore of a full backup.

.NET:  So you’re telling me that you backup the full DB and you can backup filegroups, but it’s a different kind of backup?

DBA:  Yes.

.NET:  But still, if you have…

DBA:  No, if it’s a full backup and it restores, everything is there.

.NET:  You’re not letting me finish.

DBA:  Yeah, because I know what you’re trying to say and there’s no wiggle-room.

.NET:  But if you have multiple filegroups, then…

DBA:  No, if it’s a full backup and it restores, everything is there.

.NET:  So are you telling me that there’s absolutely no way to restore only certain filegroups from a full backup?

DBA:  That’s exaxtly what I’m telling you.  Again, it’s in the name ‘Full Backup’.

.NET:  So is there a way to change the location of a filegroup when you restore the full backup?

DBA:  Only if it’s tied to a specific file and you restore that file to another location, then you’re really moving the file itself and the filegroup is coming along for the ride.

.NET:  But I keep thinking there’s got to be a way to…

DBA:  No, if it’s a full backup and it restores, everything is there.

.NET:  But if there’s not enough disk space and it only restores part of the DB then that would leave you with only part of the DB.

DBA:  No, it wouldn’t complete the restore and you’d have nothing.

.NET:  So if you take a filegroup backup then you can restore different filegroups.

DBA:  Yes.

.NET:  Then I’m still right.

DBA:  No, not even close.

.NET:  Yeah, because you can still do what I said you could do.

DBA:  No, because you’ll recall that there are 2 very important aspects of what I’m saying and they both have to be there… which is part of the original topic:  “If it’s a full backup” and “if it restores”.  If both of those exist, then you’ve got everything in the DB.

.NET:  But I still think you should be able to…

DBA:  No, if it’s a full backup and it restores, everything is there.

And then it just kind of tapered off into other backup discussions from there, but that was just fun.

Thought you guys might like a funny story on a mon.

4 thoughts on “A fun conversation about backups”

  1. .NET – What a moron, what’s so hard to understand about ‘Full Backup’?

  2. Of course you can, but this is a .NET guy going from zero backup knowledge, and the specific discussion (which I didn’t get into as part of the story) was about a restore my Jr had done that was a full backup/restore to a different system. The girl who requested it lost perms and couldn’t see any of the tables, so she thought that they hadn’t been copied over with the restore. So in the process of reasoning my Jr through the fact that a full restore will always restore all the tables, the .NET guy came over and started talking about filegroups. So rather than get into a big discussion with him about all the ins and outs and what’s possible and when, I just simplified it for him. I didn’t think it would take that long to get the point across.

    This is a good example of giving someone the info they need at the time and then spoon feeding them more info as it becomes necessary. He didn’t care about the backups or the filegroups. He just wanted to get up and introduce something into the topic that wasn’t relevant. And this DB doesn’t have any extra FGs anyway, so it’s a moot point where this conversation is concerned.

    But the specific lesson I was trying to teach the Jr was that since he did a full backup/restore, he shouldn’t have looked anywhere other than perms when she said she couldn’t see any other tables. So it was in that context I was arguing.

    I just thought this made a good story.

  3. I’m not agreeing with the .Net guy at all, but there is a way to do object level restores from a full backup, if you’re using a 3rd party tool like Litespeed. Does this help if you’re only using native SQL backups? No. Can you restore a filegroup using object level restores? No. This has actually been one of our points of contention with Quest Support, and they’ve yet to give us a better answer. It’s just not possible to restore a filegroup from a full backup without restoring to an alternative db, and copying the data back to the original…

Comments are closed.