How do you change the default location of an mdb file?
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
Tags: database administration, detached database, mdb, sql server, t-sql, tsql
October 7th, 2008 at 9:56 am
How can I accomplish this same function using the GUI of SQL 2000. The issue is when I detach the file the HardDrive does not have enough room for the mdb file that is why I need to change the default from C: Drive to D: Drive because D: has more space.
Thanks-Jeff
October 8th, 2008 at 5:12 pm
There is no reason you need to use SQL Enterprise Manager for this. Simply detach the database using sp_detach_db:
EXEC sp_detach_db ;
GO
Then move the file from your C drive to your D drive.
Now all you need to do is reattach in SQL Server. For SQL Server 2000, use either sp_attach_db or sp_attach_single_file_db (if the database is a singl file).
It should then show up in SQL Server.
If you need to use SQLEM, you can right-click the database and go to All Tasks and then select Detach Database. To attach, right-click the databases node and select All Items – Attach Database.
March 12th, 2009 at 5:34 am
i have copied all mdb files from one server to other server but some of the mbd files pointing to previous server.How to know the path where i should change…..