Can someone please clarify the difference between Detach / Attach of MDF/LDF
files versus the Backup / Restore of backup to device/file feature? Which
would be ideal for restores of actual databases?
Thank you.Transact-SQL BACKUP/RESTORE is the normal method for database backup and
recovery. Databases can be backed up while online. The backup file size is
usually smaller than the database files since only used pages are backed up.
Also, in the FULL or BULK_LOGGED recovery model, you can reduce potential
data loss by performing transaction log backups.
Detaching a database removes the database from SQL Server while leaving the
physical database files intact. This allows you to rename or move the
physical files and then re-attach. Although one could perform cold backups
using this technique, detach/attach isn't really intended to be used as a
backup/recovery process.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Can someone please clarify the difference between Detach / Attach of
> MDF/LDF files versus the Backup / Restore of backup to device/file
> feature? Which would be ideal for restores of actual databases?
> Thank you.
>|||Thanks for the explanation. If I was to restore a WSS or SPS which uses SQL
that exists in one domain and restore to another domain - would the Backup /
Restore be better?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Transact-SQL BACKUP/RESTORE is the normal method for database backup and
> recovery. Databases can be backed up while online. The backup file size
> is usually smaller than the database files since only used pages are
> backed up. Also, in the FULL or BULK_LOGGED recovery model, you can reduce
> potential data loss by performing transaction log backups.
> Detaching a database removes the database from SQL Server while leaving
> the physical database files intact. This allows you to rename or move the
> physical files and then re-attach. Although one could perform cold
> backups using this technique, detach/attach isn't really intended to be
> used as a backup/recovery process.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "KP" <kp@.msn.com> wrote in message
> news:%23PtI97whFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||I usually use backup/restore to move databases between servers. It doesn't
matter if the servers on a different domains.
After you restore to another box, you'll need to make sure you don't have
orphaned users. See sp_change_users_login in the Books Online for
information on how to report/correct the users.
Hope this helps.
Dan Guzman
SQL Server MVP
"KP" <kp@.msn.com> wrote in message
news:eiTM5UxhFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Thanks for the explanation. If I was to restore a WSS or SPS which uses
> SQL that exists in one domain and restore to another domain - would the
> Backup / Restore be better?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u85i3JxhFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment