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:
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