Modify file help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Administration

Modify file help

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-09-10 : 17:08:16

Hi,

I thought this must be a easy enough task, but...

I want to move sql2012 physical files and if it is possible, rename them at the same time.

Here are their current location:
E:\SQL Data\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB1_be.mdf
E:\SQL Data\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB1_be.ldf
And here are where they should end up:
E:\SQL Data\DB1_data.mdf
E:\SQL log\DB1_log.ldf

use master;
go

alter database DB1
set offline
go

alter database DB1
modify file (Name = DB1_dat, Filename = 'E:\SQL Data\DB1') --or DB1_data.mdf
go

alter database DB1
modify file (Name = DB1_log, Filename = 'E:\SQL Logs\DB1') --or DB1_log.ldf
go
--got next for above two alter, but restart the instance did not help.
--The file "DB1_log" has been modified in the system catalog. The new path will be used the next time the database is started.

alter database DB1
set online
go
--error out as next
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\SQL Data\DB1". Operating system error 2: "2(The system cannot find the file specified.)".
File activation failure. The physical file name "E:\SQL Logs\DB1" may be incorrect.
Msg 5181, Level 16, State 5, Line 1
Could not restart database "DB1". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

What went wrong? Thanks!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-10 : 17:12:57

Take a look at this thread, and in particular the replies by a guy named James K. He has some example scripts there that you can copy and play with to make sure you understand what you are doing and that you are doing the right things.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186964

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-10 : 17:15:46

To move the files, use this procedure:
1. detach the database
2. copy the files to new location
3. attach the database

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-09-11 : 09:15:27

I see.

"alter database modify file" did not move the file.

I need a step before that to actually move the file. BOL/MSDN gave me the impression that all I need is that line.

I don't do this kind of thing often enough.

Thank you both for the help!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-11 : 10:01:13

Yes, indeed. The "alter database modify file" simply tells SQL Server where to look for the files. You (a human being) have to manually copy/move the files to the new location.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources