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 (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 ON [,….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

 

About arieve techno

My name arieve, i'm from Purworejo..i live in Yogyakarta more aboute me YM/twitter : arieve_techno

Posted on January 15, 2011, in Sistem Basis Data. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: