Move database file

I want to move my database file to a new directory, I search in MS and I use the following steps:

USE master;

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'test');

ALTER DATABASE test SET OFFLINE;

ALTER DATABASE test

MODIFY FILE ( NAME = test_Log,

FILENAME = 'D:\Databases\test_log.ldf');

ALTER DATABASE test

MODIFY FILE ( NAME = test,

FILENAME = 'D:\Databases\test.mdf');

ALTER DATABASE test

MODIFY FILE ( NAME = test_Log,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_log.ldf');

ALTER DATABASE test SET ONLINE;

But I had the following errors:

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file "D:\Databases\test.mdf". Operating system error 5: "5(error not found)".

Msg 5120, Level 16, State 101, Line 1

Unable to open the physical file "D:\Databases\test_log.ldf". Operating system error 5: "5(error not found)".

File activation failure. The physical file name "D:\Databases\test_log.ldf" may be incorrect.

Msg 945, Level 14, State 2, Line 1

Database 'test' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Can someone tell me how to move my database to another directory (in the same server instance)?

Thanks ina dvance

In between the lines:

ALTER DATABASE test SET OFFLINE;

ALTER DATABASE test

You should make sure to copy the database files to the new location. The errors point to the fact that the files aren't there. You can find step-by-step instructions here:

http://msdn2.microsoft.com/en-us/library/ms345483.aspx

Buck Woody

|||

Try using detach and attach method to copy the datbase file to new location and attach it from the new location.

Much better way to do the same

thankx