I'm trying to get to a point where I can implement structural changes to a
merge replication system. My plan is to ship copies of a subscription
database to enduser outside the corp firewall and write some code to load
the new subscription database. I've done the following:
1. create a subscription db on the same server as the publisher
2. use the 'copy a subscription database' menu choice to create a copy of
it.
3. move the copy to target server.
4. in the replication folder on the target server select: 'attach
subscription database', select the msf file, specify a name for the db.
5. run sp_addMergeSubscription on the publication server as follows:
exec sp_addmergesubscription
@.publication = 'rep_test_hub',
@.subscriber = 'k-server',
@.subscriber_db = 'rep_test_sub2',
@.subscription_type = 'pull',
@.sync_type = 'none'
6. on the publisher - drill down to the publisher list, refresh it and note
the addition of 'rep_test_sub2'. At this point the status is 'deactivated'
7. synch with the publisher on subscriber
The above works fine when both pub and sub are on the same box. When I try
this on another pc in a peer-peer network I get the following error:
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 203
Date: 1/20/2006
Time: 1:20:32 PM
User: N/A
Computer: k-HOME
Description:
SubSystem Message - Job
'K-SERVER-rep_test_hub-rep_test_hub-K-HOME-rep_test_sub6- 0'
(0x826C45BA92AD5F4E9B37842A22DB6F0B), step 1 - The process could not connect
to Distributor 'K-SERVER'. Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'.
Why is the synch attempt using an 'anonymous logon'?
I've checked my SQL-Agent properties and it is setup for 'SQL-Server
Authentication' in the connection tab on both systems. Both server have
identical 'sa' passwords. The startup account is 'system account' on the
sub PC and a domain admin account on the hub PC. The sub PC is not part of
the domain.
Any suggestions?
Thanks.
Run SQL Server agent under a local admin account on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"astro" <astro@.bcmn.com> wrote in message
news:U2bAf.38900$7S.35782@.tornado.rdc-kc.rr.com...
> I'm trying to get to a point where I can implement structural changes to a
> merge replication system. My plan is to ship copies of a subscription
> database to enduser outside the corp firewall and write some code to load
> the new subscription database. I've done the following:
> 1. create a subscription db on the same server as the publisher
> 2. use the 'copy a subscription database' menu choice to create a copy of
> it.
> 3. move the copy to target server.
> 4. in the replication folder on the target server select: 'attach
> subscription database', select the msf file, specify a name for the db.
> 5. run sp_addMergeSubscription on the publication server as follows:
> exec sp_addmergesubscription
> @.publication = 'rep_test_hub',
> @.subscriber = 'k-server',
> @.subscriber_db = 'rep_test_sub2',
> @.subscription_type = 'pull',
> @.sync_type = 'none'
> 6. on the publisher - drill down to the publisher list, refresh it and
> note the addition of 'rep_test_sub2'. At this point the status is
> 'deactivated'
> 7. synch with the publisher on subscriber
> The above works fine when both pub and sub are on the same box. When I
> try this on another pc in a peer-peer network I get the following error:
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 203
> Date: 1/20/2006
> Time: 1:20:32 PM
> User: N/A
> Computer: k-HOME
> Description:
> SubSystem Message - Job
> 'K-SERVER-rep_test_hub-rep_test_hub-K-HOME-rep_test_sub6- 0'
> (0x826C45BA92AD5F4E9B37842A22DB6F0B), step 1 - The process could not
> connect to Distributor 'K-SERVER'. Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'.
> Why is the synch attempt using an 'anonymous logon'?
> I've checked my SQL-Agent properties and it is setup for 'SQL-Server
> Authentication' in the connection tab on both systems. Both server have
> identical 'sa' passwords. The startup account is 'system account' on the
> sub PC and a domain admin account on the hub PC. The sub PC is not part
> of the domain.
>
> Any suggestions?
> Thanks.
>
>
Showing posts with label copies. Show all posts
Showing posts with label copies. Show all posts
Thursday, February 16, 2012
Monday, February 13, 2012
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!
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
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?
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.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!
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!
Thursday, February 9, 2012
attach/deattach database
is it possible to create a dts job or sproc that can be launched via a vbs
file or wsh script that:
1. unattaches a sql 2000 database
2. copies it to a removable drive
3. re-attaches original db
4. and does opposite at office to keep a sql in sync?
any ideas on best method and syntax would be appreciated.Scott,
If youo are trying to have a remote database that is in sync, or at least
is in syn some what, is it possible to use replication? A snap shot
replication using the SQL Server as both the publisher and the distributor
can create a push a snapshot of the entire database, or selected tables from
the database. If the databases are not connected (I noted that you want to
use a removeable drive) then a process can be developed to copy the files to
a drive and then mount the databases on the remote server. Here is just one
method:
1. Create a job that backups up the database
2. Create a DTS package that will copy the backup files to the removable
drive.
3. Step 2 of the job would be to execute the DTS package in step 2.
4. Do the reverse on the remote server but in place of the backup create a
job that restores the database.
Detaching and reattaching can be an option as well as long are not
connections to the database when it is detached. A Backup can be done
regardless if the database is in use or not.
Hope this helps.
Rick
"scott" wrote:
> is it possible to create a dts job or sproc that can be launched via a vbs
> file or wsh script that:
> 1. unattaches a sql 2000 database
> 2. copies it to a removable drive
> 3. re-attaches original db
> 4. and does opposite at office to keep a sql in sync?
> any ideas on best method and syntax would be appreciated.
>
>
file or wsh script that:
1. unattaches a sql 2000 database
2. copies it to a removable drive
3. re-attaches original db
4. and does opposite at office to keep a sql in sync?
any ideas on best method and syntax would be appreciated.Scott,
If youo are trying to have a remote database that is in sync, or at least
is in syn some what, is it possible to use replication? A snap shot
replication using the SQL Server as both the publisher and the distributor
can create a push a snapshot of the entire database, or selected tables from
the database. If the databases are not connected (I noted that you want to
use a removeable drive) then a process can be developed to copy the files to
a drive and then mount the databases on the remote server. Here is just one
method:
1. Create a job that backups up the database
2. Create a DTS package that will copy the backup files to the removable
drive.
3. Step 2 of the job would be to execute the DTS package in step 2.
4. Do the reverse on the remote server but in place of the backup create a
job that restores the database.
Detaching and reattaching can be an option as well as long are not
connections to the database when it is detached. A Backup can be done
regardless if the database is in use or not.
Hope this helps.
Rick
"scott" wrote:
> is it possible to create a dts job or sproc that can be launched via a vbs
> file or wsh script that:
> 1. unattaches a sql 2000 database
> 2. copies it to a removable drive
> 3. re-attaches original db
> 4. and does opposite at office to keep a sql in sync?
> any ideas on best method and syntax would be appreciated.
>
>
Subscribe to:
Posts (Atom)