SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving a large index file from 1 drive to another


Moving a large index file from 1 drive to another

Author
Message
dbv147
dbv147
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 116
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
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3657 Visits: 6512
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/
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12131 Visits: 8924
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1831 Visits: 1657
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,
Sqlfrenzy
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12131 Visits: 8924
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 Wink )

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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1831 Visits: 1657
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 Wink )

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,
Sqlfrenzy
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12131 Visits: 8924
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search