DETTACH / ATTACH pada SQL SERVER 2000

You probably have done copy database to other location by using either database backup or detach-attach database operation. Do you know what precisely happen behind detach database under the hood?

Detach database could be done only if database is not being accessed by anyone. If you would like to make sure none accesses the database; you can set database to SINGLE_USER in ALTER DATABASE sql command with one of the termination options : WITH ROLLBACK AFTER n SECONDS or WITH ROLLBACK IMMEDIATE (Where n is a any integer number), for example :

ALTER DATABASE AdventureWorks2008
SET SINGLE_USER
WITH ROLLBACK AFTER 3 SECONDS

After that you can issue sp_detach_db <databasename> (for example: EXEC sp_detach_db AdventureWorks2008).

Interesting thing to note is that when detach database is issued, SQL Server will copy dirty pages (changes pages since last checkpoint) back to corresponding pages in database files. You just only need to copy/move database files into other location for attaching database and SQL Server could rebuild transaction log file for you. But there are 2 database conditions that you need to be aware about.

If database is read only, you need to include not only data files but also transaction log file when attach database. If database is read write, you need only to include data files.


Since SQL Server 2005, syntax for attach database is CREATE DATABASE <database name> ON <filespec> [,….n] FOR {ATTACH | ATTACH_REBUILD_LOG}. The legacy command sp_attach_db should not be used because it becomes depreciated feature and could be removed in future SQL Server release. The difference between new syntax and legacy is that maximum number of files on legacy is up to 16 files whereas on new syntax the maximum number of files that could be attached is up to 32,767 files and 32,767 file groups. Certainly you will never reach the number of files that need to be attached.

To me, attach-detach is as good as backup-restore database. The only difference between them is that database is still accessible during backup-restore whereas attach-detach requires database to be offline.

**kalau temen-temen gak tau artinya bisa di translate di mbah google

intinya :

DETTACH / ATTACH

Fungsi : mengkopi file database dan menginisilisasi ke server SQL Server
Cara Attach :
Dari Interprise Manager
Database
Klik Kanan – All Task – Attach
Cari file *.mdf

Cara Dettach :
Dari Interprise Manager
Klik database yg dimaksud
Klik kanan