Post reply

What happens when data files are moved to diff path when the database is OFFLINE

  • May 7, 2010 at 12:40 pm

    #140849

    Hi,

    I have done the following steps for testing purpose.

    I have created a database "db1" and it is created in the default directory.

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\db1.mdf

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\db1.mdf

    For testing purpose, i intentionally took the database to OFFLINE.

    Then i moved the .mdf and .ldf files to C:\Data and then brought the database to ONLINE state. then i used sp_helpfile . It is still pointing to default location. i.e. above path. I thought, it will go to SUSPECT mode but it didnt. Also, i have created few objects and it was successfull and tried to query such that i can know which table is residing in which file group and physical file. Still it is showing the same old defualt path and not the one where i moved the files i.e. C:\ drive.

    I Also, stopped the sql server and restarted the SQL Server and used the sp-helpfile against the db1 database. Still same old story it is pointing to old default location.

    Can i know the reason, why didn't the database have gone to SUSPECT mode which i thought is going to happen. But still am able to perfrom all operations.

    How sql server knows the data is to be stored in C:\drive but still the system catalog views shows the default path when the database was created. What will happen internally???

    How to fix so that the system tables also points to correcct files in correct path so that a new comer if he is coming and handling the database should not get confused??

    Thanks in Advance

  • May 7, 2010 at 3:05 pm

    #1164462

    did you move or copy the files?

    to tell SQL the files have been moved use the alter database command

    alter database dbname modify file (name =, filename =' ')

    I would expect your database to have come up without the '+' next to it in SSMS and file not found errors in the error log.

    It would not be usable so there is something missing in your description of events.

    ---------------------------------------------------------------------

  • May 7, 2010 at 8:35 pm

    #1164513

    I believe i move the files. If i copy the files then the database must be in ONLINE state after restarting the sql server!!! But it went to suspect mode.

    I have tried with the

    ALTER DATABASE <dbname>

    MODIFY(name='',filename='') to point to correct location.

    It worked fine!

    Thank You.

  • May 8, 2010 at 7:13 am

    #1164556

    Why didn't you just use detach, move, attach ?

  • May 8, 2010 at 9:47 am

    #1164580

    offline

    alter database

    online

    has the advantage of not changing the database owner or possibly ending up with a different dbid which can throw off default database settings.

    ---------------------------------------------------------------------

  • May 8, 2010 at 9:05 pm

    #1164658

    Thanks both the solutions has worked.

  • May 8, 2010 at 11:15 pm

    #1164666

    mahesh.vsp (5/7/2010)

    How sql server knows the data is to be stored in C:\drive but still the system catalog views shows the default path when the database was created. What will happen internally??? /quote] Did you get, why it happened ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 7 posts - 1 through 6 (of 6 total)

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