data files moved to different location

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

data files moved to different location

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-05-07 : 14:38:40

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

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 14:50:21

Hi
Are you sure that you have moved the DB and not copied it to different location because i followed the steps mentioned by you but the behaviour is different.

Though the DB didn't go in suspect mode but it is not allowing me to access it. It is showing me in dropdown list of the database but when i select it, it is not getting selected but the default master db is appearing.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.