How to move tables, indexes & Logs from one disk to another disk drive
-
Hello,
I am new to SQL Server so would greatly appreciate any help.
I am running SQL Server 2008 on my machine, can anyone explain a step by step procedure to move tables, indexes and transactions logs from one disk drive to another disk drive.
This is to avoid taking down SQL Server on my machine.
Thank you.
Danesh
-
have a look at this SSC article http://www.sqlservercentral.com/articles/Administration/65896/ and see if it fits your needs.
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
-
Hi ALZDBA,
Thank you for your reponse. The link you provided requires the database to be offilne.
What I am looking for is a way to move or copy all objects tables,logs,etc,etc to new disk drive without taking SQL Server 2008 offline or shutting it down.
I have googled around but not having much joy.
Dan
-
to move the tables and indexes to another file\filegroup you may use the create index with drop existing. Ensure the table has a clustered index in the first place then execute create on a new file\filegroup with the drop exisiting will move these. The log file will need to be performed via an alter database statement. Either way you are going to encounter some disruption!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
d.qureshi (9/15/2010)
Hi ALZDBA,Thank you for your reponse. The link you provided requires the database to be offilne.
What I am looking for is a way to move or copy all objects tables,logs,etc,etc to new disk drive without taking SQL Server 2008 offline or shutting it down.
I have googled around but not having much joy.
Dan
Try Alter database command to move the physical file to different drive.
"More Green More Oxygen !! Plant a tree today"
-
Multiple options for this:
Create an additional file in the filegroup and empty the previous exisitng file and moving the data to the new file.
Create a new filegroup, recreate clustered indexes on this new filegroup (make sure you check for LOB data to be recreated on the new filegroup)
Or analyze your database and only move specific data objects to the new filegroup (via move of clustered index recreate on new filegroup). this could help to distribute your processing to different spindles and may help with performance as well as allow for growth and free up disk space on your current disks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Do all the objects have to move?
If yes, then do the following in OFF hours:
1) Backup the database
2) Restore to the other drive with a slightly different name (MyDBDup)
3) Take original database offline
4) Change name of new database to old database name
Viola! All objects moved without the pain of recreating indexes, etc.
-
Hello Brandie,
Thank you for your kind advice.
So there is no way to move a table and its data from one physical disk location to different physical disk location without some disruption to users?
-
d.qureshi (9/21/2010)
Hello Brandie,Thank you for your kind advice.
So there is no way to move a table and its data from one physical disk location to different physical disk location without some disruption to users?
No - there will be disruption. It will be in how you manage that disruption though that is essential. You should do this during off-hours and during an approved outage.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
CirquedeSQLeil (9/21/2010)
d.qureshi (9/21/2010)
Hello Brandie,Thank you for your kind advice.
So there is no way to move a table and its data from one physical disk location to different physical disk location without some disruption to users?
No - there will be disruption. It will be in how you manage that disruption though that is essential. You should do this during off-hours and during an approved outage.
And if you do it right, the users won't even notice the "distruption".
-
Brandie Tarvin (9/21/2010)
CirquedeSQLeil (9/21/2010)
d.qureshi (9/21/2010)
Hello Brandie,Thank you for your kind advice.
So there is no way to move a table and its data from one physical disk location to different physical disk location without some disruption to users?
No - there will be disruption. It will be in how you manage that disruption though that is essential. You should do this during off-hours and during an approved outage.
And if you do it right, the users won't even notice the "distruption".
Exactly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply