Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jamesy281
Starting Member
2 Posts |
 Posted - 2009-09-02 : 04:57:07
|
| Hi There,I have SQL Server 2005 on the D: drive on our server the Databases folder is under the same path as the program filesD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataThere are approx 3 programs that have databases on this instance of SQL server. I want to move the Databases to another drive on the same server.Can I move the Data folder that contains the databases and if so will the programs that use them still be able to access the Databases? |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2009-09-02 : 05:10:28
|
| Detach your database, move your mdf and ldf files to new location, attach database from new location |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-09-02 : 05:12:08
|
| Only if you repoint the database.There are a few ways this can be completed. You coulda) Backup and Restore using WITH MOVE on the data files.b) Create a clustered index for each table on a new filegroup on the new drive.c) Detach the database, move the files and reattach. |
|
|
|
Jamesy281
Starting Member
2 Posts |
Posted - 2009-09-02 : 11:19:03
|
| Thanks |
|
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-03 : 07:08:30
|
quote:
Originally posted by RickD
Only if you repoint the database.There are a few ways this can be completed. You coulda) Backup and Restore using WITH MOVE on the data files.b) Create a clustered index for each table on a new filegroup on the new drive.c) Detach the database, move the files and reattach.
While technically you could do option "a", option "c" (detach/attach) is the best choice since the only downtime for each database is the amount of time it takes to move the files to their new location. With backup and restore, it will take at least twice as long since you have to (a) wait for the backup to complete and then (b) wait for the restore to complete.I don't think option "b" is an option. In that specific example, the original database files would remain in their original location (only the new filegroup's files would be in the new location).========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-09-03 : 07:31:28
|
quote:
Originally posted by ScottWhigham
quote:
Originally posted by RickD
Only if you repoint the database.There are a few ways this can be completed. You coulda) Backup and Restore using WITH MOVE on the data files.b) Create a clustered index for each table on a new filegroup on the new drive.c) Detach the database, move the files and reattach.
While technically you could do option "a", option "c" (detach/attach) is the best choice since the only downtime for each database is the amount of time it takes to move the files to their new location. With backup and restore, it will take at least twice as long since you have to (a) wait for the backup to complete and then (b) wait for the restore to complete.I don't think option "b" is an option. In that specific example, the original database files would remain in their original location (only the new filegroup's files would be in the new location).========================================================I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx
So, what is your point? The OP asked for ways to move a database, so I gave them choices. Is it not better to give someone choices on ways to do something rather than preaching a certain way?I would rather let people learn by working out the pros/cons of doing things different ways.Dogma is not a good way to teach. |
|
|
|
ScottWhigham
Starting Member
49 Posts |
Posted - 2009-09-03 : 13:54:53
|
quote:
Originally posted by ScottWhigham
[quote]Originally posted by RickD
So, what is your point? The OP asked for ways to move a database, so I gave them choices. Is it not better to give someone choices on ways to do something rather than preaching a certain way?I would rather let people learn by working out the pros/cons of doing things different ways.Dogma is not a good way to teach.
My point is that option (b) is not correct, option (a) although right will take the longest, and by default that leaves option (c) as the best choice in my opinion. I didn't think I was being rude or being dogmatic; I was trying to expound on what I thought was a quality post already (yours). Look - I'm new here and I apologize if what I said upset you as that was not my intent. I think providing alternatives is the right thing to do and, if you search my posts or have every watched my videos, you would see that. I think we can both agree that giving people extra/enough information to make an informed decision is the right thing to do as well - and that's truly all I thought I was doing. Again - I apologize if I'm upset you. I'll try to word my posts differently in the future. |
|
|
|
|
|
|
|
|
Moving Databases