Detach and Attach database in SQL 2005 RRS feed

  • Question

  • I am very cautious now, as we just had a disaster, the db cannot be repaired, had to restore from backup though corrupted database can still be read out. don't blame me :) as I only troubleshoot when there is user complaining DB, I don't have time to check log daily unless I got email alert because I have too many tasks that include vmware admin, 10+ applications admin, 30+ windows server admin, dba for 100+ sql servers and 6 Oracle servers, also web programming, sharepoint admin etc(maybe someone can advise me a good tool that I can purchase to make dba role efficient).

    My question is, On the same server, I want to swap two databases location so that the production restored db is moved to replication LUN, and the corrupted DB moved to a non replication LUN, the db size is 102GB.  What is the better and safest way to do it? I mean safest as no user will be affected after downtime is over.

    the options 1 I can think:

    1. In SSMS, detach database(check drop connections)

    2. move file to replicaiton LUN

    3. In SSMS, attach database by selecting the mdf in new location and verify the ldf location

    option 2:

    1. backup the current production DB_a to drive I:\a.bak

    2. backup the corrupted DB_b(replicationLUN D:) to drive I:\b.bak

    3. restore database DB_a from disk='I:\a.bak' with replace, move 'a' to 'D:\a.mdf', move 'a_log' to 'D:\a.ldf'

    4. restore database DB_b from disk='I:\b.bak' with replace, move 'b' to 'J:\b.mdf',move 'b_log' to 'J:\b.ldf'

    which way is more safer?

    Monday, July 25, 2011 7:28 PM