How to Move the MIIS Database and Log Files to Different Drives (818565)

MORE INFORMATION

• SQL Enterprise Manager
• Query Analyzer

How to Use SQL Enterprise Manager to Move Files to a New Location

1. Start SQL Server, and then click Enterprise Manager.
2. Double-click Microsoft SQL Servers, double-click SQL Server Group, double-click Server_Name (where Server_Name is the computer where SQL Server is running), and then double-click Databases.
3. Right-click the MicrosoftIdentityIntegrationServer database, and then click Properties.
4. On the General tab, note the database owner.
5. On the Data files tab and on the Log files tab, note the current file locations, and then click Cancel.
6. Right-click the MicrosoftIdentityIntegrationServer database, click All Tasks, and then click Detach database.
7. Move the MicrosoftIdentityIntegrationServer.mdf file from the existing location (C:\Program Files\Microsoft Identity Integration Server\Data\MicrosoftIdentityIntegrationServer.mdf) to a new location (D:\MiisData).
8. After the file has been successfully moved to the new location, reattach the database. To do this, right-click the Databases folder, click All Tasks, and then click Attach database.
9. In the MDF file of database to attach box, click the ellipses (...) button to locate the .mdf file.
   
   After the database file is located, SQL Enterprise Manager locates the log file. If you moved the log file location, you see a red X instead of a green check mark in the Current File(s) Location box.
   
   To edit the location of the existing log file, type the new drive location in the Current File(s) Location box.
10. Type the appropriate database owner from the information that you noted in step 4.
11. Click OK to attach the database.

How to Use Query Analyzer to Move Files to a New Location

1. Click Start, point to Programs, click Microsoft SQL Server, and then click Query Analyzer.
2. In the Query pane, run the following code:

        use master
   		   go
   		   sp_detach_db 'MicrosoftIdentityIntegrationServer'
   		   go

3. Move the log file from the existing location (C:\Program Files\Microsoft Identity Integration Server\Data\MicrosoftIdentityIntegrationServer_log.ldf) to a new location (E:\MiisLog).
4. To reattach the database, point to the files in the new location. To do so, run the following code:

       use master
   		  go
   		  sp_attach_db 'MicrosoftIdentityIntegrationServer','c:\Program Files\Microsoft Identity Integration Server\data\MicrosoftIdentityIntegrationServer.mdf','E:\MIISlog\MicrosoftIdentityIntegrationServer_log.LDF'
   		  go

5. Verify the change in file locations by using the sp_helpfile code:

        use MicrosoftIdentityIntegrationServer
   		   go
   		   sp_helpfile
   		   go

   The new locations appear in the Filename column.