Difference between Detach/Attach and Restore/BackUp a DB

Posted by SAMIR BHOGAYTA on Samir ASP.NET with C# Technology See other posts from Samir ASP.NET with C# Technology or by SAMIR BHOGAYTA
Published on 2011-05-23T19:48:00.000-07:00 Indexed on 2011/06/20 16:37 UTC
Read the original article Hit count: 437

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.

Commonly it is recommended that you use BACKUP/RESTORE for disaster recovery (DR) scenario and copying data from one location to another. But this is not absolute, sometimes for a very large database, if you want to move it from one location to another, backup/restore process may spend a lot of time which you do not like, in this case, detaching/attaching a database is a better way since you can attach a workable database very fast. But you need to aware that detaching a database will bring it offline for a short time and detaching/attaching does not provide DR function.

For more information about detaching and attaching databases, you can refer to:

Detaching and Attaching Databases
http://technet.microsoft.com/en-us/library/ms190794.aspx


© Samir ASP.NET with C# Technology or respective owner

Related posts about Difference between Detach/Attach and Restore/BackUp a DB