Database propertiesdefault file path to custom loc

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

Transact-SQL (2005)

Database propertiesdefault file path to custom loc

Author

Topic

sql4us
Starting Member

24 Posts

Posted - 2011-11-15 : 08:27:10

Hi,

I want to change the file path in sql 2005 server from default to custom.
When you right click the database -> properties -> Files -> Path (default goes to C drive) want to change for DATA and LOG path..please let me know..its not allowing me to change..

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-15 : 08:45:38

You can't do it from properties. Hoever you can run ALTER DATABASE statement


ALTER DATABASE Db_name 
MODIFY FILE
(
    NAME = file_name,
    FILENAME = N'file_path'
);
GO

Madhivanan

Failing to plan is Planning to fail

sql4us
Starting Member

24 Posts

Posted - 2011-11-15 : 09:52:34

Thank you.

sql4us
Starting Member

24 Posts

Posted - 2011-11-15 : 11:23:10

how to drop the instance and recreate with the backup files?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-15 : 11:30:35

Note that Madhivanan's answer is only part of the process. Once you've run that, you need to then take the DB offline, go to explorer, move the database files, then bring the DB back online.

As for your new question, see your other thread.

--
Gail Shaw
SQL Server MVP

sql4us
Starting Member

24 Posts

Posted - 2011-11-15 : 11:38:30

Actually the problem is I have Database/instance1 and all the data and log files point to default location (C:\Program files\SQL Server\MSSQL\MSSQL.3|Data\) now the memory size is full we can't add more instances as it says disk space full, so I want to point all the data and log files to another Drive with full space..so according to Madhivanan I changes the Database file path to my new drive..when I copied all the data and log files to another drive(with full disk space) and started and stopped the service...it doesn't start the service and it gives me error msgs..so I thought the best way would be to drop that instance and create new instance and then copy all tables/sp's etc from the back up files..not sure if thats the better way or not..any help would be appreciated..Thanks!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-15 : 12:57:56

Wow, you want to uninstall SQL Server to resolve errors. A bit overkill don't you think?

Maybe if you mentioned what the error messages were someone might be able to help you resolve them. You didn't do anything silly like move any of the system databases did you? Cause that would certainly cause the instance not to start.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources