SQLServerPedia

The full original question is:  How do you change the default location of a mdb file? Currently when I detach it is set for C: Drive path and I need to change it to D: Drive path. How can I change it?

First, thanks for the question submission!  You can detach the database, move the file(s), and reattach using the CREATE DATABASE… FOR ATTACH command.

This is a SQL Server 2005/2008 example. For SQL Server 2000, you would use sp_attach_db/sp_attach_single_file_db to perform the attach.

For example:

Detach the database during a maintenance window – make sure you have a backup just in case something goes wrong:

USE master;
GO
EXEC sp_detach_db <db_name>;
GO

Now move the database file to the D: drive and reattach:

– Execute CREATE DATABASE FOR ATTACH statement
CREATE DATABASE
ON (FILENAME = ‘D:\\’) FOR ATTACH;
GO

Keep the questions coming!  We get a large volume of submissions and love seeing what the community is thinking about. 

David