Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2005
    Posts
    16

    Unanswered: Replace DB Files while Offline ... Potential Problems?

    I have created a program using SQL-DMO to replace an SQL Server 2000 database by setting it OFFLINE, replacing the .MDF and .LDF and then setting the database back ONLINE.

    SQL Server does permit replacing the files while offline. And when the database is back online, everything seems to have worked perfectly with the new database files.

    My questions is ... What potential problems (if any) exist with this process replacing a database?

    Other methods I have considered:
    - Detach, replace DB files, re-attach
    - Delete old database, attach-using-new-DB-files

    Thanks for your input!!

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    The problem you've followed to detach, rename and re-attach is fine. As the SQL server doesn't remember the previous database names and when you detach the name &* details will be cleared from sysdatabases table.

    By any reason if there is a mismatch of filenames with .MDF and .LDF then you will have issue of re-attachign the database.

    BTW can you explain why you need to this on regular basis?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2005
    Posts
    16
    Thx for the reply,

    Because I am just updating the same SQL database with 2 new database files (.mdf and .ldf) with the same file names, I figure setting the database offline in order to do the 'file replacement' would be more efficient than detaching the database.

    You mentioned that the process of (Detach)-(Rename)-(ReAttach) is fine.

    However, I am using the process of (Set Offline)-(Replace .mdf/.ldf)-(Set Online). Would there be any problems when this method is used?

    This 'database replacement' process is needed because databases sometimes gets corrupted for various reasons, and it needs to be 'sent in' to repair. The fixed database is sent back to the user and replaces their corrupted database.

    Thanks again.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    more efficient? the dettach and reattch sp's are real fast.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    As long as you carryon such rename operations during SQL offline then you will not have any issues, otherwise SQL will flag inconsistency with mismatche of files.

    Any reason and investigation on why the database is corrupted?
    If you can fix the problem then you can reduce round-robin method. ;-)
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Apr 2005
    Posts
    16

    Smile

    The 'data corruption' problems I had previously mentioned, are only at the data-level (caused by errors in the client program) and not at the database-level. Sorry for the confusion.

    And when I say 'more efficient', I had some quicker response times setting a database OFFLINE/ONLINE then DETACH/RE-ATTACH. Also in code (SQL-DMO), setting a detabase offline/online is as simple as setting a boolean flag to true/false, whereas the detach/attach method requires full path names to the .MDF and .LDF.

    Thx again for your responses.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    User mappings can be lost in this sort of operation. Does the client have to re-add those?

    Hopefully this is not terribly important data. Especially if the database has to be 'sent in' for repairs. Sorry, but it just rubs me the wrong way.

  8. #8
    Join Date
    Apr 2005
    Posts
    16
    Thanks for the excellent reminder.... a custom database USER and ROLE is used. All 'repairs' are applied to on the same database received (which will also be the same one sent back to the client). I will definately keep this in mind when testing.

    And yes, it rubs me the wrong way as well... but that is another story
    ... and I plan to add backups and restores to avoid this whole process of 'sending and receiving' of an entire database.

    Thx again!

Posting Permissions

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