How to change the location of SQL Server Data and Log files

Introduction

This topic explains the process to change the default file location of the SQL Server Data and Log Files.

• In order to makes these changes, the user must have admin privileges on the SQL Server and permissions to edit the REGISTRY values on the server.

Once the database default locations have been updated, restart the SQL Server Services for the new values to come into effect.

Backup the IRIS SQL database

• Before making any changes make sure the database is backed up.

1. Using SQL Server Management Studio, connect to SQL Server Database Engine.

2. In the Object Explorer expand Databases and right-click on the IRIS database.

3. Select Properties and Files, this will indicate the current location of the IRIS SQL files.

4. Close this Window and then go back to the Object Explorer right-click on the IRIS database and select Tasks then Detach.

5. Create a new folder on the PC, for example, C:\IRISDB

6. Go to the current location of the database files in Windows Explorer (as found in step 2) and move them to the new location using cut and paste.

7. Go back to the Object Explorer right-click on the IRIS database and select Tasks and Attach then click Add in the Attach Databases window.

8. Browse to the new location where the IRIS database files were moved to. Select the IRIS.mdf file and click OK.

9. The new location of the IRIS database files will be shown. Click OK.

10. In the Object Explorer right-click the SQL Server Database Engine Instance and select Properties from the menu.

11. In Server Properties dialog box navigate to Database Settings page as shown below. Under Database default locations you can specify the new path for the Data and Log.

12. Click  the …  to browse to the new database location

13. Select the folder and click OK to save the changes. Repeat the steps for the Log file.

14. Click OK to save changes and close out of SQL Server Management Studio.