|
Question:
How can I move my database to a different drive or folder?
Answer:
Users occasionally desire to move the ExpressMaintenance database to a
different location. Usually this is because they need to free up
disk space or they want the database on a different drive for other
reasons.
Moving a SQL
database is not difficult but it is not a matter of simply moving files.
Because the SQL server has the database files locked and in use, you
must take a few steps. The following steps must be taken.
1. Locate the
current files that make up the database. The files are
ExpressMaintenance_Data.mdf and ExpressMaintenance_Log.ldf. With
the MSDE2000 or SQL2000, the files are likely in the C:\Program Files\MSSQL\Data
folder. With the older MSDE7 they will likely be in the
C:\MSSQL7\Data folder. These files will exist in the ExpressTech
folder but these are not the database, they are the original model files
used to create the database.
2. You
download, install and run ExpressUtility to perform the remaining steps.
For information on downloading, installing and setting up
ExpressUtility, click here
for the applicable tech bulletin.
3. Detach The
Database - With ExpressUtility, click on the Server and the Master
database. Click on the Explore Data tab. Insert the
following SQL statement in the upper right area where SQL commands are
entered. After the statement is entered, click the Execute SQL
button (lightning bolt graphic).
EXEC sp_detach_db 'ExpressMaintenance',
'true'
4. Locate the the data and log files as outlined in step
1 above. Move the two files from their current folder to the new
desired drive and folder.
5. Attach The Database -
With
ExpressUtility, click on the Server and the Master database. Click
on the Explore Data tab. Insert the following SQL statement in the
upper right area where SQL commands are entered. Be sure to adjust
for the exact drive and folder path you are actually using where you
placed the files in step 4 above. After the statement is entered
correctly, click the Execute SQL button (lightning bolt graphic).
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'D:\MSSSQL\Data\ExpressMaintenance_Data.mdf',
@filename2 = N'D:\MSSQL\Data\ExpressMaintenance_Log.ldf'
This will
attach the database using the new location of the files. In
ExpressUtility, you can click on the Data Connection tab, click on the
Server and you should now see the ExpressMaintenance database listed.
6. Exit
ExpressUtility and you are done. |
