Here is how you can move data files in SQL Server.
1. Retrieve DB LogicalName and current location.
SELECT name, physical_name, state_desc AS OnlineStatus FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2014') GO
2. Set Database offline.
ALTER DATABASE AdventureWorks2014 SET OFFLINE;
GO3. If your database it is taking too slow to become offline, you should kill all session ID’s that are connected to the database (including the sleeping connections). I have a script below to do just that. Just specify the database name for the @DBName variable.
DECLARE @DBName VARCHAR(50) = 'AdventureWorks2014'
DECLARE @sql NVARCHAR (500)
DECLARE @spid INT
DECLARE @numberOfRowsToKill INT
DECLARE @i INT = 0
DECLARE @sessionID INT
SET NOCOUNT ON
SET @sql = 'sp_who'
IF OBJECT_ID('tempdb..#spids') IS NOT NULL
DROP TABLE #spids
CREATE TABLE #spids
(
spid INT,
ecid INT,
status VARCHAR(30),
loginname VARCHAR(30),
HostName VARCHAR(50),
Blk INT,
DBName VARCHAR(30),
Command VARCHAR(MAX),
RequestID INT
)
INSERT INTO #spids
EXEC (@sql)
SELECT @numberOfRowsToKill = COUNT(*) FROM #spids WHERE DBName = @DBName
IF @i < @numberOfRowsToKill
WHILE @i < @numberOfRowsToKill
BEGIN
SELECT TOP 1 @sessionID = spid from #spids WHERE DBName = @DBName
SET @sql = 'kill ' + CAST(@sessionID AS NVARCHAR(10))
EXEC (@sql)
PRINT 'SPID ' + CAST(@sessionID AS VARCHAR) + ' has been killed'
DELETE FROM #spids WHERE spid = @sessionID
SET @i = @i + 1
END
ELSE
PRINT 'There are no more sessions connected to ' + @DBName + ' database.'
4. Move the physical files using Powershell. Run Powershell as Administrator and run the following command.
Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CreditData.mdf" -Destination "C:\SQL\DATA" Move-Item -Path "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CreditLog.ldf" -Destination "C:\SQL\DATA"
5. Modify the file name. Specify the new file path.
ALTER DATABASE AdventureWorks2014 MODIFY FILE (NAME = 'LogicalName' ,FILENAME = 'C:\SQL\DATA\CreditData.mdf')
GO
ALTER DATABASE AdventureWorks2014 MODIFY FILE (NAME = 'LogicalName',FILENAME = 'C:\SQL\DATA\CreditLog.ldf')
GO6. Set database online.
ALTER DATABASE AdventureWorks2014 SET ONLINE; GO
7. Verify that the change is okay.
SELECT name, physical_name, state_desc AS OnlineStatus
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2014')
GOCheers!
