Post reply

Issue in moving db files to different folder - urgent

  • May 3, 2011 at 9:48 am

    #249703

    Hi, Immediate response would be much apprdciated.

    Issue description:

    We have planned to move data and log files of our sql instance (SQL 2008) to different path.

    We have used ALTER DATABASE MODIFY FILE for this.

    Once we issued this statement, changes are reflecting in sys.master_files system view.

    but while checking the file in physical path, we found that its not physically moved.

    reason is, dba team forgot to bring the db offline before moving the file

    impact:

    We could not connect to the database (database is not accessible) from the point we restart the sql service.

    can anyone tell me,

    1. is there any way to update current/actual database path in sys.master_files system view

    2. or how to bring the database online by refering the data files available in older path

    thanks in advance

  • May 3, 2011 at 9:52 am

    #1319186

    Take the database offline

    Either move the file or run alter database again (note alter database will NOT move the file for you. You have to do that)

    Bring the database online.

    A restart of SQL was unnecessary.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 4, 2011 at 12:07 am

    #1319543

    Thanks GilaMonster. Issue is resolved.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply