Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Post Unanswered: database detach/attach

    An original database (eg A.mdf) is imported/exported to a different server as named B.mdf. The detach of A.mdf was unitentionally not done. It seems that both the database were updated.

    A.mdf - 123000KB 25/12/2003
    A_log.ldf - 14000KB 25/12/2003
    B.mdf - 67000KB 25/12/2003
    B_log.ldf - 1024KB 25/12/2003

    In this case, which mdf & ldf should be the correct database?
    If I I remove A.mdf database by "detach", will B.mdf database work?

    SQL server 2000 and SP3 installed.

    Assistance is appreciated

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    you are exporting a database file and getting different sizes .. seems strange

    sp_helpdb 'database_name'

    on both servers

    Copy and paste results over here


    That might help
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jan 2004
    Posts
    6

    SQL SERVER DETACH/ATTACH

    The original database (eg A.mdf) is export/imported into a different server and the database is named as B.mdf. The 'detach' command from of the original database (A.mdf) was unintentionally
    not done. As I have unintentionally did not do a 'detach' of the old database, both the database seems to be updated (as indicated from the date stamp) AFTER I use the application (VB6 business application) a day or two.

    Once an import/export is done, is the detach of the old database compulsory?. In SQL Server Enterprise Manager->(Select say, A database)->All Task-> Detach database

    A.mdf - 123,000KB, 2/1/2004 1142am
    A_log.ldf - 10,000 KB, 2/1/2004 0133pm
    B.mdf - 67,000KB, 2/1/2004 0902am
    B_log.ldf - 1024KB, 2/1/2004 0902am

    Exporting of the database is ok before the I started using the application.

    How can I correct this? Advise is appreciated.

    Regards,
    Brian

    Originally posted by Enigma
    you are exporting a database file and getting different sizes .. seems strange

    sp_helpdb 'database_name'

    on both servers

    Copy and paste results over here


    That might help

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?

  5. #5
    Join Date
    Jan 2004
    Posts
    6
    Originally posted by rnealejr
    Are both databases supposed to be active - please describe more as to why you have 2 ? Where are you retrieving the database sizes ? Use sp_spaceused and post the results. Is the real question, what has changed between the 2 databases and how to find those records ?
    the A.mdf and its log is the active database. 2 databases are active as 1 is live/production environment and the other is test/QA environment. the database sizes is seen from the window explorer.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).

  7. #7
    Join Date
    Jan 2004
    Posts
    6
    Originally posted by rnealejr
    What needs to be corrected ? You keep mentioning detaching the A database - but why ? Is A production or test ? You need to run the stored procedure sp_spaceused to determine actual space used. Are you concerned that there might be activity on both databases - and you are not sure why ? Are you exporting the data from the production to the test periodically ? Since your log file for B does not appear to have grown, it appears that either minimal or no user activity is occuring on the B database (unless you are backing up the database/transaction log for B).
    if I were to remove (detach) the test/QA data (denotes by B.mdf and its log), will this cause any problem with production (denotes by A.mdf and its log) database?. I noted that the date/time has been updated on the same day even though my ODBC (live application) uses production database which is A.mdf.

    What happen if a detach is not done after import/export from production to test/QA environment?. Do the production database get updated (by right it should since ODBC points to production) along with the test database?. I am concern that activities may be updated in the test environment - yes, I am not sure and I would like some feedback.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.

  9. #9
    Join Date
    Jan 2004
    Posts
    6
    Originally posted by rnealejr
    There should be no connection between your A database and your B database (unless you have triggers/replication between the 2) - other than the fact that you exported the data from A to B. What method did you use to export the data from A to B ? Several activities can cause the date/time to change on B, but since the log is only 1 meg (the minimum) I would suspect that nothing has really changed on B. But if you are really concerned - dump the test database and copy the database from prod back to test.
    I agree with you. All I did was to simply import/export. no DTS used.
    The strange thing I noted was that after the import/export from production to test (and no "detach" was done on test database), I ran the application and did the update. I found was that the test database time/date was updated AND the data went into production and the mdf and log of production did not change (seen from explorer).

    Thanks for your feedback.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    It was probably just coincidence ... or a poltergeist (oooohhhhhh - supposed to be a spooky sound)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •