Attaching and Detaching a Database

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
RaviQ1:
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@.hotmail.com> wrote in message
news:92e983f6.0403040826.6c4373fb@.posting.google.com...
> 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|||In answer to Q1, I had a similar post a few weeks ago . . .firstly in order
to copy data and log files the database MUST be detached otherwise the files
are open and in use by SQL server. If you wish to copy a database using this
method then you do not have to stop SQL server just ensure that you run
sp_detach_db against the database in question, and copy the files, then
ensure that the sp_attach_db is executed once the data and log files have
been successfully copied, note that the database will not be accessible
until sp_attach_db is executed. . . .you can insome cases copy database
files(as you have proved) across to another server by just stopping sql
server and copying the files however according to Tibor, it is best to
detach database first, apparently with multiple log files if a detach is not
performed it is highly unlikely that you will be able to attach the database
at the other end.
in answer to Q2 Yes you can do the same with large production databases
Olu Adedeji
"Ravi" <araskas27@.hotmail.com> wrote in message
news:92e983f6.0403040826.6c4373fb@.posting.google.com...
> 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|||I don't believe you can copy files while attached. Maybe if all
database connections are closed it might work, but I doubt it. What I
do if I want to copy database files without detaching it, is right click
and choose Take Offline. When I'm done, I bring the database online.
Much easier.
This is the quick explanation of what happens, if you want to see actual
detail, run sp_helptext sp_attach_db and sp_helptext sp_detach_db to see
what goes on. Or grab books online from http://www.microsoft.com/sql.
Detaching a database is something that can only be done done if you are
a sysadmin on the server. After all connections are closed, it removes
the entries from the master database and msdb database for that
particular database -- sysdatabases, sysdevices, syslogins, the
maintenance plans, etc. Then you just have database files sitting
there.
Attaching a database adds entries to sysdatabases and sysdevices for
that database. It does not sync users to logins, regardless of whether
there are logins--sometimes I've had to go back and run
sp_change_users_login auto_fix, User_id inside the attached db to fix
orphaned users.
****************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
This posting is provided "as is" with
no warranties and confers no rights.
****************************************
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||We use the following in Query Analyzer to take live copies of production database and either put them onto different servers (such as laptop development machines) or simply restore them back to the server with a different database name, thus making a quick "readable" copy of production data. We aren't concerned about ever restoring these backups for production use, so whether users are in the middle of transactions or table changes doesn't matter. This works very well for us.
Sometimes we run just the first query (the BACKUP) on one server, and them run the other two queries (the RESTORE's) from a QA window on a different server. This example specifically copies the production database into a new "name" on the same server...
BACKUP DATABASE Funds
TO DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY
FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.mdf',
MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO
-- Andy S. wrote: --
I don't believe you can copy files while attached. Maybe if all
database connections are closed it might work, but I doubt it. What I
do if I want to copy database files without detaching it, is right click
and choose Take Offline. When I'm done, I bring the database online.
Much easier.
This is the quick explanation of what happens, if you want to see actual
detail, run sp_helptext sp_attach_db and sp_helptext sp_detach_db to see
what goes on. Or grab books online from http://www.microsoft.com/sql.
Detaching a database is something that can only be done done if you are
a sysadmin on the server. After all connections are closed, it removes
the entries from the master database and msdb database for that
particular database -- sysdatabases, sysdevices, syslogins, the
maintenance plans, etc. Then you just have database files sitting
there.
Attaching a database adds entries to sysdatabases and sysdevices for
that database. It does not sync users to logins, regardless of whether
there are logins--sometimes I've had to go back and run
sp_change_users_login auto_fix, User_id inside the attached db to fix
orphaned users.
****************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
This posting is provided "as is" with
no warranties and confers no rights.
****************************************
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!