Advantage comparison on Attach & Restore database

If I were to copy a database from one machine to another machine, I can
use:
- Backup & restore
- Detach & attach
Not to mention on those daily backup or availability of the database.
Just consider copy one database to another machine.
Does anyone know what's the advantage of each method mention above?
I used to Detach & Attach cause it's fast. Backup & restore database
would takes long time.
Other than that, anyone know is there any side effect or drawback of
the methods mentiond above?
Peter CCHHi,
Detach and attach must be faster but it require downtime; where as Backup
and restore do not need any downtime.
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||Other than that, nothing else?
Then it would be fine for me cause it's a developer machine, not
production server.
We always need to copy database from machine to machine.
Thanks.
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Detach and attach must be faster but it require downtime; where as Backup
> and restore do not need any downtime.
> Thanks
> Hari
> SQL Server MVP
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...|||Backup and Restore may also 'defrag' the tables and indexes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
> If I were to copy a database from one machine to another machine, I can
> use:
> - Backup & restore
> - Detach & attach
> Not to mention on those daily backup or availability of the database.
> Just consider copy one database to another machine.
> Does anyone know what's the advantage of each method mention above?
> I used to Detach & Attach cause it's fast. Backup & restore database
> would takes long time.
> Other than that, anyone know is there any side effect or drawback of
> the methods mentiond above?
>
> Peter CCH
>|||> Backup and Restore may also 'defrag' the tables and indexes.
No table/index defragmentation takes place during the restore; the restored
database is exactly like the original. However, a restore can effectively
defrag database files at the file system level when the files are recreated.
Such fragmentation usually occurs only when one relies on automatic growth.
With the exception of a version upgrade, few changes are made during the
restore. The only changes that come to mind are to the file paths and
database owner.
Hope this helps.
Dan Guzman
SQL Server MVP
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
> Backup and Restore may also 'defrag' the tables and indexes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1156564154.830161.207440@.i3g2000cwc.googlegroups.com...
>|||Thanks Dan,
I meant to say 'File' defrag, and my evil twin wrote in the other thought...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OhkIAJPyGHA.4912@.TK2MSFTNGP03.phx.gbl...
> No table/index defragmentation takes place during the restore; the
> restored database is exactly like the original. However, a restore can
> effectively defrag database files at the file system level when the files
> are recreated. Such fragmentation usually occurs only when one relies on
> automatic growth.
> With the exception of a version upgrade, few changes are made during the
> restore. The only changes that come to mind are to the file paths and
> database owner.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%23gDCwVMyGHA.4336@.TK2MSFTNGP06.phx.gbl...
>