Hi All,
I want that by default SQL Server automatically starts a transaction for me
and I must commit every action manualy to really change something in the
database.
I am using SQL Server 2000.
Regards
Chakravarti MukeshHi!
Do please check the SET IMPLICIT_TRANSACTIONS statement in Books OnLine - I
think this is what you need.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
news:O6QlPOl%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I want that by default SQL Server automatically starts a transaction for
> me and I must commit every action manualy to really change something in
> the database.
> I am using SQL Server 2000.
> Regards
> Chakravarti Mukesh
>|||Hi,
Thanks. It worked fine. But It needed to be SET every time I open Query
Analyzer.
Chakravarti Mukesh
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:et$9XTl%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi!
> Do please check the SET IMPLICIT_TRANSACTIONS statement in Books OnLine -
> I think this is what you need.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
> news:O6QlPOl%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||... and if using Query Analyzer, there's a config setting for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage
news:et$9XTl%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi!
> Do please check the SET IMPLICIT_TRANSACTIONS statement in Books OnLine -
I think this is what you
> need.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
> news:O6QlPOl%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||Chakravarti Mukesh (cmukesh@.sparshindia.com) writes:
> Thanks. It worked fine. But It needed to be SET every time I open Query
> Analyzer.
Tools->Options->Connection Properties.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Go to Tools-->Options-->Connection Properties in Query Analyzer.
I advise caution in using this option. It's unlikely that your applications
will also set this option, so it will be difficult to test procedures and
other batches that are sent by applications that expect
IMPLICIT_TRANSACTIONS to be OFF. You should also be aware that every
statement will start a transaction--even SELECT, so depending on the
TRANSACTION ISOLATION LEVEL, it's possible to block other users' access even
with the most innocent-looking query.
I understand the desire not to screw up the production database with an
ill-formed UPDATE or DELETE statement, but a better solution would be to
first execute the changes against a test database, and once you're satisfied
that the results are correct, to then issue the batch against the production
database.
"Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
news:%23UVBRhl%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks. It worked fine. But It needed to be SET every time I open Query
> Analyzer.
> Chakravarti Mukesh
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:et$9XTl%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||I think Dejan was trying to suggest you use this setting in your procedures.
:)
I personally prefer explicit transactions - they can be started when needed
and rolled back or committed as appropriate based on the requirements of eac
h
individual case. But maybe I'm just a control freak.
ML
http://milambda.blogspot.com/
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
Auditing SQL Server 2005 through transaction log
Hello,
We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.
Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.
Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...
It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)
3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.
We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?
Thanks!
Marc Lacoursiere
RooSoft Computing
The format of the transaction log is undocumented, as it will change from release to release.Thanks,|||
Hi,
I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.
Thanks
|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||Thanks for your Post Peter,
I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.
What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.
I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.
thanks for your feedback|||Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.
You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.
|||Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.
We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.
|||>> SELECT "log record" FROM ::fn_dblog(null,null)
>> All we need to know is who made the modifications, when, in which tables and what are the new values.
"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().
When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().
Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.
"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.
The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.
Thanks Peter for this interesting complement of information.
We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.
For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.
|||You could look at AuditDB of Lumigent, can be customized on details what you demand.
With best regards.
Jan H. Kanon
Auditing SQL Server 2005 through transaction log
Hello,
We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.
Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.
Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...
It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)
3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.
We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?
Thanks!
Marc Lacoursiere
RooSoft Computing
The format of the transaction log is undocumented, as it will change from release to release.Thanks,|||
Hi,
I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.
Thanks
|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||Thanks for your Post Peter,
I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.
What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.
I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.
thanks for your feedback|||Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.
You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.
|||Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.
We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.
|||>> SELECT "log record" FROM ::fn_dblog(null,null)
>> All we need to know is who made the modifications, when, in which tables and what are the new values.
"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().
When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().
Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.
"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.
The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.
Thanks Peter for this interesting complement of information.
We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.
For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.
|||You could look at AuditDB of Lumigent, can be customized on details what you demand.
With best regards.
Jan H. Kanon
Auditing SQL Server 2005 through transaction log
Hello,
We are maintaining an internal ASP.NET v2.0 website which is quite big and already in production. The underlying SQL Server 2005 database contains 350+ tables.
Recently, we have been asked to implement a new feature which seems functionally quite simple. We have to track every single data modification, which includes insertions, deletions and modifications. This information should be presented to power users in the form of readable strings right in an admin section of our website.
Our team of architects is working on a way to make it possible without putting the SQL Server to a crawl. One thing is for sure, SQL Server 2005 already does the job through its transaction log. It should be a good idea to use it directly instead of managing our own log based on triggers. Why put more pressure on the server to write data that is already logged by the database engine? We have heard that Microsoft's SQL Server team do not support this concept and are wondering why...
It's quite easy to find queries on the web that output very useful information such as date of transactions and what they have done. Although, the data involved in those transactions seems to be stored in a binary field which can be retrived using this query: SELECT "log record" FROM ::fn_dblog(null,null)
3rd parties such as Apex SQL are already doing a great job at decrypting it for us. This is very useful but not efficient since those tools do a very generic job. We would like to optimize it for our needs. All we need to know is who made the modifications, when, in which tables and what are the new values.
We believe that we would have to decrypt the "log record" field from the ::fn_dblog(null, null) table. Is there any way to get basic documentation about how to do it?
Thanks!
Marc Lacoursiere
RooSoft Computing
The format of the transaction log is undocumented, as it will change from release to release.Thanks,|||
Hi,
I'm working with Marc on that point and I would like to know if there is or if Microsoft expect to expose an interface that can allow us to read the transaction log. What are the plans for futur releases.
Thanks
|||The transaction log contains physical information that are often just blocks of bytes which are meaningless in terms of the DDL or DML that instigated them. The log was never intended for audit purposes and really should not be used that way.|||Thanks for your Post Peter,
I understand that the transaction log purpose is for recovery only but you must admit that it could be a great way to audit changes without adding any overhead to the server.
What we would like to do is taking a transaction log backup on a regulary basis and read those files as they cumulated in the directory. This will allow us to log any DML changes into a Log table located on another server.
I also understand that the online transaction log format can change from one version to another but correct me if I'm wrong, the transaction log backup is compatible between SQL 2000 and SQL 2005 that means that the format of a .trn file should remain compatible as SQL Server evoluate.
thanks for your feedback|||Adding the information needed to use the log for auditing would add significant overhead which is one of the reasons it has not been done.
You might look at the Change Data Capture functionality in the upcoming SQL Server 2008 release. http://connect.microsoft.com/sqlserver/ has a CTP preview release available.
|||Our guess as of now is that some timely transaction log backup analysis on a remote machine could help keep our database server usage to an acceptable level as it already handles loads of data.
We doubt that using triggers on each table would be much more effective. This would certainly slow down every transaction. As our database grows, we must optimize DML operations to keep the website running fast.
|||>> SELECT "log record" FROM ::fn_dblog(null,null)
>> All we need to know is who made the modifications, when, in which tables and what are the new values.
"Who" is not available on a log record by log record basis. It is not part of that binary data in most cases. There are some few records that contain a "who" such as BEGIN TRAN log records. This is included in other output columns of fn_dblog().
When is not available on a log record by log record basis. There are some few record types that contain a "when" such as BEGIN TRAN and END TRAN log records. These are included as other columns in fn_dblog().
Which table is not actually stored in the log record in SQL 2005 and later. This is due to partitioning. The partition has to be linked through the catalog metadata back to its base table and index. This is attempted by fn_dblog() itself and shows up as other columns in the output. DDL can make this lookup fail.
"New values" are often just a byte by byte binary diff of the old row from the new row, not the full values themselves. The log record code does not actually know how to crack the row binary data, it just passes it along to other components in the system. i.e., if you update a INT column from a value of 1 to a value of 257, we may only log a single byte 0x01 as the difference.
The SQL 2005 output for fn_dblog() has been supplemented to try to make some of this easier, but the reality is that some of what you want is just not in the log in many cases.
Thanks Peter for this interesting complement of information.
We actually take care of the "When" and the "Who" because each table of our database contain an updatedBy and updatedDt column. These columns are systematicly updated on each changes by the application layer. We only need to find the new values of the modification.
For the byte by byte binary diff, I guess that you are talking about the online transaction log because taking a closer look at the transaction log backup file (.trn) indicates that new values are stored in the file. I've opened it with an hex editor and I was able to see the new inserted values.
Wednesday, March 7, 2012
audit log
Excuse the naive question, but I have heard about SQL Server's "transaction
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.You can "view" the transaction log using third-party programs like LogPI and
Log Explorer (Google for them) but it's not a very pleasant experience --
there's a surprisingly large amount of data to sort through. Triggers are
definitely a good bet for a manageable audit trail in the database. You can
also look at SQL Server's built-in auditing facilities. Check out the
topic, "Auditing SQL Server Activity" in BOL.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
"transaction
> log" and I am wondering if this can be viewed to see all of the
transactions
> that occurred in past X amount of time. Is this feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>|||Here are some links: http://vyaskn.tripod.com/administration_faq.htm#q1
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
Excuse the naive question, but I have heard about SQL Server's "transaction
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.|||If you talking detailed auditing such as might be required by the dreaded
Sarbnes/Oxely Act, then there are also some third party (read - not cheap)
tools for such things. Lumigent is one company I know of.
Bob Castleman
DBA Poseur
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
> "transaction log" and I am wondering if this can be viewed to see all of
> the transactions that occurred in past X amount of time. Is this
> feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.You can "view" the transaction log using third-party programs like LogPI and
Log Explorer (Google for them) but it's not a very pleasant experience --
there's a surprisingly large amount of data to sort through. Triggers are
definitely a good bet for a manageable audit trail in the database. You can
also look at SQL Server's built-in auditing facilities. Check out the
topic, "Auditing SQL Server Activity" in BOL.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
"transaction
> log" and I am wondering if this can be viewed to see all of the
transactions
> that occurred in past X amount of time. Is this feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>|||Here are some links: http://vyaskn.tripod.com/administration_faq.htm#q1
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
Excuse the naive question, but I have heard about SQL Server's "transaction
log" and I am wondering if this can be viewed to see all of the transactions
that occurred in past X amount of time. Is this feasible?
Are than any other solutions for audit trail besides putting triggers on
every table to log everything?
Thanks.|||If you talking detailed auditing such as might be required by the dreaded
Sarbnes/Oxely Act, then there are also some third party (read - not cheap)
tools for such things. Lumigent is one company I know of.
Bob Castleman
DBA Poseur
"Mike W" <mikeotown@.nospam.msn.com> wrote in message
news:eMcABUdFFHA.4004@.tk2msftngp13.phx.gbl...
> Excuse the naive question, but I have heard about SQL Server's
> "transaction log" and I am wondering if this can be viewed to see all of
> the transactions that occurred in past X amount of time. Is this
> feasible?
> Are than any other solutions for audit trail besides putting triggers on
> every table to log everything?
> Thanks.
>
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!
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
>
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
>
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
>
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
>
Subscribe to:
Posts (Atom)