SQL SERVER – Move Data Files

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;
GO

3. 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.'


Preview(opens in a new tab)

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')
GO

6. 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')  
GO

Cheers!