How to move database files to another location in SQL server

By default, all Identify's databases are put in the SQL Server's default database folder. If you want to move some of the database files to separate disks because of disk space or I/O issue, you can do that and Safewhere Identify will still work perfectly fine. This document outlines all the steps that you need to do to move the database files.

How to do

Prerequisites

You need to grant enough access to the new database folder for NT SERVICE\MSSQLSERVER or any account that you are using to run SQLServer. The Configure File System Permissions for Database Engine Access document has more details about permissions.

Note: remember to back up your databases before proceeding.

Move database files to another location

In this example, we choose to move files of the IdentifyAudit database to D:\data.

Step 1: Take the IdentifyAudit database offline

You need to execute an SQL script to take the database offline before moving its physical files (e.g. using Windows Explorer).

ALTER DATABASE IdentifyAudit SET OFFLINE;  
GO 

Step 2: Tell SQL Server that you want to move files to a new location

Script:

ALTER DATABASE IdentifyAudit
MODIFY FILE ( NAME = IdentifyAudit,   
              FILENAME = 'D:\data\IdentifyAudit.mdf');  
GO
ALTER DATABASE IdentifyAudit   
MODIFY FILE ( NAME = IdentifyAudit_log,   
              FILENAME = 'D:\data\IdentifyAudit_log.ldf');  

Step 3: Take the IdentifyAudit database online

Script:

ALTER DATABASE IdentifyAudit SET ONLINE;  
GO 

Note: If you get the error "Unable to open the physical file "...\data\IdentifyAudit.mdf". Operating system error 5: "5(Access is denied.)".", make sure that SQL Server has permission to access the specified location as noted in the Prerequisites section.

You can run the script below to verify that the process is done successfully:

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus
FROM sys.master_files  
WHERE database_id = DB_ID(N'IdentifyAudit')  
GO 

After moving the IdentifyAudit database's data files, you can verify that everything works well:

• Access an Identify instance and perform a login to its Admin site.
• Use the Identify Configurator to create/upgrade/remove/replicate an instance.