How to move the database files from one logical driver to another Drive

Question

How to move  the database files from one logical driver to another Drive?

What is the  best practice ?. How to make sure everything is consistent successful

I have found the below things one article ? .Actually does all the thing below are  required or just detach ,move and attach the database is not enough ?

----------------------------------

select name,physical_name  from sys.master_files

alter database AdventureWorks
set offline
go

--make offline forcefully--You need to use WITH ROLLBACK IMMEDIATE to boot other conections out with no

regards to what or who is is already using it.


alter database AdventureWorks
set offline with rollback immediate
go

alter database AdventureWorks
modify file
(
name =AdventureWorks,
filename='P:\DATAFILES\AdventureWorks.mdf'
)
go

alter database AdventureWorks
modify file
(
name =AdventureWorks_log,
filename='R:\LOGFILES\AdventureWorks_log.Ldf'
)
go


--move the data file to the new directory we specified

alter database AdventureWorks
set online
go

select name,physical_name  from sys.master_files

Answer 1

Yes it looks OK.... Someone prefer using attach\detach approach, I would prefer the script you posted above

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Thanks

Can you give one reason  why you prefer the script rather than detach \attach process .

What is the difference between the detach \attach process  and the above script

Answer 3

>>>Can you give one reason  why you prefer the script rather than detach \attach process .

Its is just personal preference. Ideally, if you go for detach\attach option, have a last good backup in case something goes wrong.

>>What is the difference between the detach \attach process  and the above script

1.Backup can be done online....
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3e9c558b-3550-4d20-a12d-90cd2d2ed204/

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Detatch/attach is the "old" way. What you posted is the "new say".In general, you want to adapt the new method of doing things. For instance, detach/attach doesn't handle service broker (SB). OK, you might not be using SB today, but tomorrow there might be something else (new feature) that old method doesn't handle properly.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 5

How to move  the database files from one logical driver to another Drive?

You can restore the database from backup to different files:

http://www.sqlusa.com/bestpractices2005/restoredbwithsqlcmd/

---

Kalman Toth SQL SERVER & BI TRAINING