Move Data Files

Taking a database offline is probably less invasive than a detach-move files-reattach sequence.

-- Get file name and current location of files.
SELECT 
 name AS LogicalFileName, 
 physical_name AS FileLocation,
 state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('RedgateMonitor');

USE MASTER
-- Alter database file locations.
-- This has no effect until database status is cycled.
ALTER database RedGateMonitor
modify file 
(name = RedGateMonitor,
filename = 'D:\MSSQL\Data\RedGateMonitor.mdf')

ALTER database RedGateMonitor
modify file
(name = RedGateMonitor_log,
filename = 'D:\MSSQL\Logs\RedGateMonitor_log.ldf')

ALTER DATABASE RedGateMonitor SET OFFLINE WITH ROLLBACK IMMEDIATE

-- NOW move the files

ALTER DATABASE RedGateMonitor SET ONLINE

 File Permissions

Ensure that the files are copied preserving their security settings.

SQL 2012 virtual accounts:  the “MSSQLSERVER” user should have full control:

MSSQLSERVER user

 

This user cannot be typed in nor selected using Advanced Find.  Paste this exact spelling:

NT SERVICE\MSSQLSERVER

Validation errors may be reported; persevere.  See this article https://msdn.microsoft.com/en-us/library/jj219062.aspx