February 6, 2009 at 8:06 am
Hello,
I need to move an index file to another drive and thought I'd get some advice on the best way to do this. The index is on an archive server so down time is not a problem. My plan was to shutdown the SQL services copy the file to the new location. Start up SQL then remove the original file from the file group then add the file to the same file group in the new location. Any suggestions or alternate methods are welcomed.
Thanks
February 7, 2009 at 4:30 am
The best source for an answer to a question like this is Books On Lines. You can use the alter database statement to move files from one place to another. Check out this URL to get more explanations - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad9a4e92-13fb-457d-996a-66ffc2d55b79.htm.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2009 at 8:20 am
dbv147 (2/6/2009)
Hello,I need to move an index file to another drive and thought I'd get some advice on the best way to do this. The index is on an archive server so down time is not a problem. My plan was to shutdown the SQL services copy the file to the new location. Start up SQL then remove the original file from the file group then add the file to the same file group in the new location. Any suggestions or alternate methods are welcomed.
Thanks
It doesn't work that way !
All files in a filegroup contain the data in a balanced way (simply said:every file a chunk, one after the other).
What you can do is ad another filegroup and add a new file to this new filegroup.
Then move over the intended index.
Then, if needed, shrink the original filegroup.
Check books online (alter database) for details !!
This art. can help out with the actual move:
http://www.sqlservercentral.com/articles/Files+and+Filegroups/65538/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2009 at 1:59 pm
dbv147 (2/6/2009)
Hello,I need to move an index file to another drive and thought I'd get some advice on the best way to do this. The index is on an archive server so down time is not a problem. My plan was to shutdown the SQL services copy the file to the new location. Start up SQL then remove the original file from the file group then add the file to the same file group in the new location. Any suggestions or alternate methods are welcomed.
Thanks
U can use this .......
alter database TESTdatabase set offline
TestDatabase
alter database TestDatabase
Modify File ( Name = TestDatabase, FileNAme='E:\testdatabase_data.mdf')
alter database TESTdatabase set online
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 8, 2009 at 12:54 am
Indeed,Ahmad Osama, that is the best way (especially because downtime is not an issue) !
(I was just reflecting on that this morning, you know how that goes 😉 )
Or use the :
-sp_detach_db
- move the you intended to the new drive.
- sp_attach_db specifying all file locations
Be sure the sqlserver service account has been granted windows auth to the folder that hosts the db-file at the new location !
Check "Moving Database Files" in books online
[edited/added]
Keep in mind you'll have to move the actual file to the new location or you'll get this errormessage at startup time of the database !
alter database MyTestDb set online
go
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file "X:\MSSQL.1\MSSQL\log\MyTestDb_data_2.ndf". Operating system error 2: "2(error not found)".
Msg 945, Level 14, State 2, Line 1
Database 'MyTestDb' 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.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 8, 2009 at 11:57 am
ALZDBA (2/8/2009)
Indeed,Ahmad Osama, that is the best way (especially because downtime is not an issue) !(I was just reflecting on that this morning, you know how that goes 😉 )
Or use the :
-sp_detach_db
- move the you intended to the new drive.
- sp_attach_db specifying all file locations
I would have suggested this approach...however he wants to move a single file to another location not the complete database....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 9, 2009 at 12:06 am
Actually it deffers very silghtly because the db will need to be put offline to move the file !
The alter db is better because it keeps all dbinfo in master db !
So it is less prone to human errors.
(because you could only provide wrong input for the file you intend to manipulate)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply