SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Modify file help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

786 Posts

Posted - 09/10/2013 :  17:08:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 09/10/2013 :  17:12:57  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 09/10/2013 :  17:15:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 09/11/2013 :  09:15:27  Show Profile  Reply with Quote
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!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 09/11/2013 :  10:01:13  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000