When the data warehouse and staging databases are created using the Jet Data Manager the database files will be created in the default folders for database files and log files as specified in the SQL Server configuration. In some cases the default is left as the C:\ drive which may be undesirable. This article covers how to move the physical database files to a separate disk drive.
Once the databases to be moved have been identified the first step is to determine the current location of the database and log files for the database(s) to be moved. To do this, open SQL Server Management Studio, log on to the Database Engine, right click the database to be moved to a different disk drive and click Properties:
Click on the Files page on the left and then note the location of the database and log files in the Path and File Name columns.
After the file path has been noted, click Cancel to close the Database Properties window. Next, the database will be detached from the SQL Server so that the files can be moved. During this process no users will have access to the database. To detach the database, right click on the database name and go to Tasks... -> Detach...:
On the next screen click OK to detach the database. Once the database has been detached, navigate to the file folder where the database and log files reside and copy the LDF and MDF files to the desired location on the new drive. Once the files have been copied the database needs to be reattached to the SQL Server. To do this, right-click the Databases node at the top of the tree hierarchy and select Attach... :
Click the Add... button, navigate to the files that were copied to the new disk drive, and click OK:
Click the OK button at the bottom of the Attach Databases window to add the database back to SQL Server. The database will now be available and SQL will have it properly mapped to the new drive location.