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!!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?|||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.|||more efficient? the dettach and reattch sp's are real fast.|||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. ;-)|||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.|||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.|||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!