Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving a large index file from 1 drive to another Expand / Collapse
Author
Message
Posted Friday, February 6, 2009 8:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 9:52 AM
Points: 10, Visits: 99
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
Post #651693
Posted Saturday, February 7, 2009 4:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #652174
Posted Saturday, February 7, 2009 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #652204
Posted Saturday, February 7, 2009 1:59 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, June 12, 2014 7:54 AM
Points: 1,475, Visits: 1,630
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

Post #652295
Posted Sunday, February 8, 2009 12:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #652352
Posted Sunday, February 8, 2009 11:57 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, June 12, 2014 7:54 AM
Points: 1,475, Visits: 1,630
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,
Sqlfrenzy

Post #652433
Posted Monday, February 9, 2009 12:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #652559
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse