Attaching and Detaching a Database

[Ravi]

Q1.)In SQL Server 2000, is it always possible to use copies of the
data and transaction log files of a database from one server to
reattach to a new database on another server, or even to the same
server, without first detaching from the existing database?
Books Online says that detaching a database closes the data and log
files cleanly. I read some where that some times, it is possible to
attach without detaching, if the data and log file are in stable
state.
This is what I tried - Tried to copy Northwind.mdf and ldf files using
windows explorer and it gave an error message that the files are in
use. I shutdown the server and now I could easily copy them. After
that I renamed these files and successfully attached them as a new
database.
Q2.)Is it possible to do the same with large production databases?

Q3.) Please explain what goes on during detaching /attaching process.

Thanks in advance

Ravi

[Tibor Karaszi]

Re: Attaching and Detaching a Database

Q1:
No. It will actually work in many cases, but not always.

Q2:
See Q1

Q3:
SQL Server does all cached writes and probably come synchronization with the
transaction log records. AFAIK, MS has not documented the internals of
this.
--
Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

"Ravi" <araskas27@hotm ail.com> wrote in message
news:92e983f6.0 403040826.6c437 3fb@posting.goo gle.com...[color=blue]
> Q1.)In SQL Server 2000, is it always possible to use copies of the
> data and transaction log files of a database from one server to
> reattach to a new database on another server, or even to the same
> server, without first detaching from the existing database?
> Books Online says that detaching a database closes the data and log
> files cleanly. I read some where that some times, it is possible to
> attach without detaching, if the data and log file are in stable
> state.
> This is what I tried - Tried to copy Northwind.mdf and ldf files using
> windows explorer and it gave an error message that the files are in
> use. I shutdown the server and now I could easily copy them. After
> that I renamed these files and successfully attached them as a new
> database.
> Q2.)Is it possible to do the same with large production databases?
>
> Q3.) Please explain what goes on during detaching /attaching process.
>
> Thanks in advance
>
> Ravi[/color]