Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLError
Yak Posting Veteran
63 Posts |
 Posted - 2004-05-11 : 06:51:14
|
| Hello,Can someone tell me how detaching a database and backing in updiffer? I know that detaching the database saves the entire contentsof the database. Does backing it up do the same, but it puts it inone file (Database and transaction log)Thanks. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-11 : 07:57:08
|
| Backing up the database puts everything in one file, and is generally considered more portable and safer. Other than that, detaching the database and attaching it to another server does actually work. Another thing a full backup does is serve as a baseline for transaction or differential logs which are much smaller then the database files and provide snapshots of data throughout the day. Just something to keep in mind.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
MuadDBA
628 Posts |
Posted - 2004-05-11 : 08:02:19
|
| Biggest difference in my mind is that detaching makes your database unavailable to the users, while a backup can be done "Online" so that people can still use the DB while it is being backed up.And backing up CAN put the database in one file, but you can also stripe backups across multiple files if you want. We do it a lot here due to limited drive space. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-11 : 08:10:57
|
| It takes a lot of striping to put them on those floppy disks doesn't it. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-05-11 : 09:58:08
|
| Just a note : Detaching/Attaching is much faster than backup ! ------------------------I think, therefore I am |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-05-11 : 11:25:56
|
quote:
Originally posted by derrickleggett
It takes a lot of striping to put them on those floppy disks doesn't it. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
Yeah, you should see the rack of 3.5" drives I have to use.LOL.Also, to ravilobo, you are correct that detaching is faster than backing up, but it all depends on what you want to do with your DB. If you are looking to back up the DB, backup is a lot better way to go simply because it has a lot more functionality (striping, filegroups, full, differential, transaction). If you want to move a DB from server to server, detach/attach is the way to go. |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-05-11 : 11:41:16
|
quote:
Originally posted by derrickleggett
It takes a lot of striping to put them on those floppy disks doesn't it. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
Just wondering about the db... ..which can be backed up on floppies ;-)------------------------I think, therefore I am |
|
|
|
SQLError
Yak Posting Veteran
63 Posts |
Posted - 2004-05-11 : 19:18:40
|
| Thanks guys, you were all very helpful |
|
|
|
|
|
|
|
|
Back up verses detaching