Move Data Files – SQL Server

[adsense slot=”5402167102″ width=”468″]

One of those clean-up tasks we come across in development severs is to move database mdf and ldf files to a new location. SQL Server isn’t as GUI-friendly yet to allow one to just change the paths on database properties. I have tried this out in SQL Server 2008, but the method must essentially be the same in 2005 and 2000 versions also.

What we have to do is this.
– Detach the Database
– Move the files
– Attach the database with the new location

Detach

Instead of “use master”, you can just set the query window drop-down to any DB other than DB_To_Move

Now, you need to move the files from its current location to wherever you need it to be (say, d:\data\)

Attach

Again, ideally it is just the sp_attach_db line that you would need to execute. Make sure the file security permissions are set aptly at the target location.

However, things get a bit tricky if you want to move system db files to a new location (we don’t usually findourselves doing this). Please refer this for further reference on that.

Leave a Reply

Your email address will not be published. Required fields are marked *