Attach remote DB file: detaching

Showing posts with label detaching. Show all posts

Thursday, February 16, 2012

Attaching SQL Server 2000 database on SQL Serve 2005

Hello!
We are planning to migrate our multi terabyte SQL Server 2000 database
by detaching data/log files on 2000 box and reattaching them on 2005. Has
anyone experienced any issues with this process in general and databases of
such size in particular? As far as I know, SQL Server will perform database
conversion when attaching database on SQL 2005.
Any thoughts are greatly appreciated,
IgorThis is a simple test,with a database names 'test' which on sqlserver 2000.
first:
Stop the database's servers(2000) on machine.
second:
Execute the command on 2005 server:
exec sp_attach_db @.dbname = N'test',
@.filename1 = N'd:\sqlserver database\MSSQL\data\test_Data.MDF',
@.filename2 = N'd:\sqlserver database\MSSQL\data\test_Log.LDF'
ok.So easy.And the server will return a message:'Converting database 'test'
from version 539 to the current version 607.'
"imarchenko" wrote:
> Hello!
> We are planning to migrate our multi terabyte SQL Server 2000 database
> by detaching data/log files on 2000 box and reattaching them on 2005. Has
> anyone experienced any issues with this process in general and databases of
> such size in particular? As far as I know, SQL Server will perform database
> conversion when attaching database on SQL 2005.
>
> Any thoughts are greatly appreciated,
> Igor
>
>|||you should copy the datafile to a specifically catalog ã'And use new files to
attach.Otherwise 2000 server's 'test' database will disappear
"luyan" wrote:
> This is a simple test,with a database names 'test' which on sqlserver 2000.
> first:
> Stop the database's servers(2000) on machine.
> second:
> Execute the command on 2005 server:
> exec sp_attach_db @.dbname = N'test',
> @.filename1 = N'd:\sqlserver database\MSSQL\data\test_Data.MDF',
> @.filename2 = N'd:\sqlserver database\MSSQL\data\test_Log.LDF'
> ok.So easy.And the server will return a message:'Converting database 'test'
> from version 539 to the current version 607.'
> "imarchenko" wrote:
> > Hello!
> >
> > We are planning to migrate our multi terabyte SQL Server 2000 database
> > by detaching data/log files on 2000 box and reattaching them on 2005. Has
> > anyone experienced any issues with this process in general and databases of
> > such size in particular? As far as I know, SQL Server will perform database
> > conversion when attaching database on SQL 2005.
> >
> >
> > Any thoughts are greatly appreciated,
> > Igor
> >
> >
> >|||luyan,
Thanks for you reply. I do know how to detach/attach database. My
question is whether anyone has experienced any problems while attaching
terabyte databases.
Thanks,
Igor
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:051EBA51-A12E-4E42-A07B-94B5D588DA72@.microsoft.com...
> you should copy the datafile to a specifically catalog ?And use new files
> to
> attach.Otherwise 2000 server's 'test' database will disappear
> "luyan" wrote:
>> This is a simple test,with a database names 'test' which on sqlserver
>> 2000.
>> first:
>> Stop the database's servers(2000) on machine.
>> second:
>> Execute the command on 2005 server:
>> exec sp_attach_db @.dbname = N'test',
>> @.filename1 = N'd:\sqlserver database\MSSQL\data\test_Data.MDF',
>> @.filename2 = N'd:\sqlserver database\MSSQL\data\test_Log.LDF'
>> ok.So easy.And the server will return a message:'Converting database
>> 'test'
>> from version 539 to the current version 607.'
>> "imarchenko" wrote:
>> > Hello!
>> >
>> > We are planning to migrate our multi terabyte SQL Server 2000
>> > database
>> > by detaching data/log files on 2000 box and reattaching them on 2005.
>> > Has
>> > anyone experienced any issues with this process in general and
>> > databases of
>> > such size in particular? As far as I know, SQL Server will perform
>> > database
>> > conversion when attaching database on SQL 2005.
>> >
>> >
>> > Any thoughts are greatly appreciated,
>> > Igor
>> >
>> >
>> >|||It may take a long time. I did restore a 500 MB SQL 2000 DB to SQL 2005 and
it took about 10 minutes on SAN system. In your situation you have to
consider this issue.
Bill
"imarchenko" wrote:
> Hello!
> We are planning to migrate our multi terabyte SQL Server 2000 database
> by detaching data/log files on 2000 box and reattaching them on 2005. Has
> anyone experienced any issues with this process in general and databases of
> such size in particular? As far as I know, SQL Server will perform database
> conversion when attaching database on SQL 2005.
>
> Any thoughts are greatly appreciated,
> Igor
>
>|||Bill,
Thanks for you feedback.
Igor
"Bill Wang" <BillWang@.discussions.microsoft.com> wrote in message
news:D23E2D1B-DAA2-4FA2-A5CA-E253DC148BED@.microsoft.com...
> It may take a long time. I did restore a 500 MB SQL 2000 DB to SQL 2005
> and
> it took about 10 minutes on SAN system. In your situation you have to
> consider this issue.
> Bill
> "imarchenko" wrote:
>> Hello!
>> We are planning to migrate our multi terabyte SQL Server 2000
>> database
>> by detaching data/log files on 2000 box and reattaching them on 2005. Has
>> anyone experienced any issues with this process in general and databases
>> of
>> such size in particular? As far as I know, SQL Server will perform
>> database
>> conversion when attaching database on SQL 2005.
>>
>> Any thoughts are greatly appreciated,
>> Igor
>>

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching Databases

Hi
I've obtained a copy of the PRODUCTION database for restoring on the TEST
Environment. By detaching them and copying the underlying files. Not my
preferred approach, but the one I'm stuck with.
When I attach them into the TEST environment. They register to the instance
as READ-ONLY. When I try to take them out of READ-ONLY mode, the error with
5105 Error. Unable to activate device.
The devices are there and the permissions / attributes for the file are OK.
Any ideas?Beverley, one thing to look at... maybe the database you
got the mdf/ndf/ldf files from, was marked read only?
and ALSO thr mdf/ndf/ldf files were marked read only at
the file level (ATTRIB command). Both are done to
improve performance, but you'd need to get BOTH the files
and the database out of read-only status... You can do it
in the Windows Explorer attributes of the file or the DOS
ATTRIB command... Bruce
>--Original Message--
>Hi
>I've obtained a copy of the PRODUCTION database for
restoring on the TEST
>Environment. By detaching them and copying the
underlying files. Not my
>preferred approach, but the one I'm stuck with.
>When I attach them into the TEST environment. They
register to the instance
>as READ-ONLY. When I try to take them out of READ-ONLY
mode, the error with
>5105 Error. Unable to activate device.
>The devices are there and the permissions / attributes
for the file are OK.
>Any ideas?
>.
>

Monday, February 13, 2012

Attaching and Detaching Databases

in the BOL I have read the "Attaching and Detaching Databases"
in the query analyzer (pointing to the master db) I run (as sa)
EXEC sp_detach_db 'Mydb', 'true'
I got this message which is not covered.
Server: Msg 3702, Level 16, State 1, Line 1
Cannot drop the database 'mydb' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The database has no connections to it other than the Enterprise manager.
what am I missing.Do you have the database open (i.e. browsing the objects) in Enterprise
Manager? Is the database open in Query Analyzer and "collapsed". If so,
there's your connection.
You can also run sp_who to find out what connections are currently attached.
-Morgan
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:u5WMY2lcDHA.2112@.TK2MSFTNGP10.phx.gbl...
> in the BOL I have read the "Attaching and Detaching Databases"
> in the query analyzer (pointing to the master db) I run (as sa)
> EXEC sp_detach_db 'Mydb', 'true'
> I got this message which is not covered.
> Server: Msg 3702, Level 16, State 1, Line 1
> Cannot drop the database 'mydb' because it is currently in use.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> The database has no connections to it other than the Enterprise manager.
> what am I missing.
>|||Hi,
>>other than the Enterprise manager.
Even that is a connection.Try sp_who and make sure to kill the spids
accessing the database.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:u5WMY2lcDHA.2112@.TK2MSFTNGP10.phx.gbl...
> in the BOL I have read the "Attaching and Detaching Databases"
> in the query analyzer (pointing to the master db) I run (as sa)
> EXEC sp_detach_db 'Mydb', 'true'
> I got this message which is not covered.
> Server: Msg 3702, Level 16, State 1, Line 1
> Cannot drop the database 'mydb' because it is currently in use.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> The database has no connections to it other than the Enterprise manager.
> what am I missing.
>|||thanks for you answers:
Does not sp_who show the connections to the server?
do I have to remove all connection to the server just to move one DB?
This seems illogical sense the default is always logged in.
I looked at the sp_detach_db and it seems the error is being generated in
the
DBCC DETACHDB
which I don't know enough about, to go poking around.
I looked up in the BOL and it does not show in SQL 7.0 version.
in looking at
DROP DATABASE (T-SQL)
it mentions something about offline.
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:u5WMY2lcDHA.2112@.TK2MSFTNGP10.phx.gbl...
> in the BOL I have read the "Attaching and Detaching Databases"
> in the query analyzer (pointing to the master db) I run (as sa)
> EXEC sp_detach_db 'Mydb', 'true'
> I got this message which is not covered.
> Server: Msg 3702, Level 16, State 1, Line 1
> Cannot drop the database 'mydb' because it is currently in use.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> The database has no connections to it other than the Enterprise manager.
> what am I missing.
>

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.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||We use the following in Query Analyzer to take live copies of production dat
abase and either put them onto different servers (such as laptop development
machines) or simply restore them back to the server with a different databa
se name, thus making a quic
k "readable" copy of production data. We aren't concerned about ever restor
ing 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 r
un the other two queries (the RESTORE's) from a QA window on a different ser
ver. This example specifically copies the production database into a new "n
ame" 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.b
ak'
RESTORE DATABASE Funds_Copy
FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.b
ak'
WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKU
P\Funds_Copy.mdf',
MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Fund
s_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.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Attaching and Detaching a Database

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.c om...
> 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

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!

Sunday, February 12, 2012

Attaching & Detaching SQL 2000 Databases

Why is it that sometimes you can detach a database, delete the transaction
log, reattched the database and a new transaction log will be created; and
other times, the attach fails with an error message indicating that the
transaction log file name (including the path) "may be incorrect"?
Thanks,
Ross
Why detach and delete the transaction log? I've seen the
error before when others have done the same - it's not
really the best thing to do.
Some reasons for it not attaching are it not being cleanly
detached, issues or corruption in the database before
detaching, not using the with recovery clause, not using
sp_attach_single_file_db, using sp_attach_single_file_db
when the database had multiple log files.
-Sue
On Mon, 23 Jan 2006 10:37:29 -0600, "Ross Culver"
<rculver@.ranger-systems.com> wrote:

>Why is it that sometimes you can detach a database, delete the transaction
>log, reattched the database and a new transaction log will be created; and
>other times, the attach fails with an error message indicating that the
>transaction log file name (including the path) "may be incorrect"?
>Thanks,
>Ross
>

Attaching & Detaching SQL 2000 Databases

Why is it that sometimes you can detach a database, delete the transaction
log, reattched the database and a new transaction log will be created; and
other times, the attach fails with an error message indicating that the
transaction log file name (including the path) "may be incorrect"?
Thanks,
RossWhy detach and delete the transaction log? I've seen the
error before when others have done the same - it's not
really the best thing to do.
Some reasons for it not attaching are it not being cleanly
detached, issues or corruption in the database before
detaching, not using the with recovery clause, not using
sp_attach_single_file_db, using sp_attach_single_file_db
when the database had multiple log files.
-Sue
On Mon, 23 Jan 2006 10:37:29 -0600, "Ross Culver"
<rculver@.ranger-systems.com> wrote:
>Why is it that sometimes you can detach a database, delete the transaction
>log, reattched the database and a new transaction log will be created; and
>other times, the attach fails with an error message indicating that the
>transaction log file name (including the path) "may be incorrect"?
>Thanks,
>Ross
>

Attaching & Detaching SQL 2000 Databases

Why is it that sometimes you can detach a database, delete the transaction
log, reattched the database and a new transaction log will be created; and
other times, the attach fails with an error message indicating that the
transaction log file name (including the path) "may be incorrect"?
Thanks,
RossWhy detach and delete the transaction log? I've seen the
error before when others have done the same - it's not
really the best thing to do.
Some reasons for it not attaching are it not being cleanly
detached, issues or corruption in the database before
detaching, not using the with recovery clause, not using
sp_attach_single_file_db, using sp_attach_single_file_db
when the database had multiple log files.
-Sue
On Mon, 23 Jan 2006 10:37:29 -0600, "Ross Culver"
<rculver@.ranger-systems.com> wrote:

>Why is it that sometimes you can detach a database, delete the transaction
>log, reattched the database and a new transaction log will be created; and
>other times, the attach fails with an error message indicating that the
>transaction log file name (including the path) "may be incorrect"?
>Thanks,
>Ross
>

Thursday, February 9, 2012

Attach without Detach?

Is it possible to copy the .mdf and .ldf files from a production server to
another server and then attach the database without detaching the database
from production? I was thinking this might replace the copying of the .BAK
and the restore.
Thanks.
Ron
Mostly it will work, but might not as reliable as first detach. Also, the
backup and restore leaves you another running db.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron
|||That is a good way if you don't care about the integrity of your
database<g>. Seriously you should never simply copy the files. It only
takes a second to issue the detach and it will be done right. If you can't
take it offline then do a full backup instead.
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron
|||If one database is live on server, you can not copy its physical files.. this
will lead into file sharing voilation. Before copying files to another
location you must have to detach a database first.
"sql question" wrote:

> Mostly it will work, but might not as reliable as first detach. Also, the
> backup and restore leaves you another running db.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
>
>
|||Thank You for the replies
"Deepakontheweb" wrote:
[vbcol=seagreen]
> If one database is live on server, you can not copy its physical files.. this
> will lead into file sharing voilation. Before copying files to another
> location you must have to detach a database first.
>
>
> "sql question" wrote:

Attach without Detach?

Is it possible to copy the .mdf and .ldf files from a production server to
another server and then attach the database without detaching the database
from production? I was thinking this might replace the copying of the .BAK
and the restore.
Thanks.
RonMostly it will work, but might not as reliable as first detach. Also, the
backup and restore leaves you another running db.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||That is a good way if you don't care about the integrity of your
database<g>. Seriously you should never simply copy the files. It only
takes a second to issue the detach and it will be done right. If you can't
take it offline then do a full backup instead.
--
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||If one database is live on server, you can not copy its physical files.. this
will lead into file sharing voilation. Before copying files to another
location you must have to detach a database first.
"sql question" wrote:
> Mostly it will work, but might not as reliable as first detach. Also, the
> backup and restore leaves you another running db.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> > Is it possible to copy the .mdf and .ldf files from a production server to
> > another server and then attach the database without detaching the database
> > from production? I was thinking this might replace the copying of the
> > .BAK
> > and the restore.
> >
> > Thanks.
> >
> > Ron
>
>|||Thank You for the replies
"Deepakontheweb" wrote:
> If one database is live on server, you can not copy its physical files.. this
> will lead into file sharing voilation. Before copying files to another
> location you must have to detach a database first.
>
>
> "sql question" wrote:
> > Mostly it will work, but might not as reliable as first detach. Also, the
> > backup and restore leaves you another running db.
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> > > Is it possible to copy the .mdf and .ldf files from a production server to
> > > another server and then attach the database without detaching the database
> > > from production? I was thinking this might replace the copying of the
> > > .BAK
> > > and the restore.
> > >
> > > Thanks.
> > >
> > > Ron
> >
> >
> >

Attach without Detach?

Is it possible to copy the .mdf and .ldf files from a production server to
another server and then attach the database without detaching the database
from production? I was thinking this might replace the copying of the .BAK
and the restore.
Thanks.
RonMostly it will work, but might not as reliable as first detach. Also, the
backup and restore leaves you another running db.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||That is a good way if you don't care about the integrity of your
database<g>. Seriously you should never simply copy the files. It only
takes a second to issue the detach and it will be done right. If you can't
take it offline then do a full backup instead.
Andrew J. Kelly SQL MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
> Is it possible to copy the .mdf and .ldf files from a production server to
> another server and then attach the database without detaching the database
> from production? I was thinking this might replace the copying of the
> .BAK
> and the restore.
> Thanks.
> Ron|||If one database is live on server, you can not copy its physical files.. thi
s
will lead into file sharing voilation. Before copying files to another
location you must have to detach a database first.
"sql question" wrote:

> Mostly it will work, but might not as reliable as first detach. Also, the
> backup and restore leaves you another running db.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:967A02D0-F523-45F9-9817-A325262DAB79@.microsoft.com...
>
>|||Thank You for the replies
"Deepakontheweb" wrote:
[vbcol=seagreen]
> If one database is live on server, you can not copy its physical files.. t
his
> will lead into file sharing voilation. Before copying files to another
> location you must have to detach a database first.
>
>
> "sql question" wrote:
>

Thursday, February 16, 2012

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching SQL Server 2000 database on SQL Serve 2005

Attaching Databases

Monday, February 13, 2012

Attaching and Detaching Databases

Attaching and Detaching a Database

Attaching and Detaching a Database

Attaching and Detaching a Database

Sunday, February 12, 2012

Attaching & Detaching SQL 2000 Databases

Attaching & Detaching SQL 2000 Databases

Attaching & Detaching SQL 2000 Databases

Thursday, February 9, 2012

Attach without Detach?

Attach without Detach?

Attach without Detach?

Attach remote DB file

Blog Archive

About Me