Is it possible through only T-SQL to physically move the data and log files of a database from one drive to another drive (on the same server)?

Question

I know you can alter the database to update their data and log file paths in the database properties, but is there also a way to script out the actual physical move of the files themselves?...maybe through a database command?

Answer 1

Below is a solution that uses robocopy with xp_cmdshell to move the files. Afterwards the DEL command is used to delete the old files. It has its flaws (only data files + log files to be moved), only one data location, no checks for missing or needed \'s, error handling should be better,...

But you might find use in simply printing the commands by setting the @debug parameter to 1.

Test database used

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Test', FILENAME = N'G:\SQL\MSSQLSERVER\Test.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ),
( NAME = N'test2', FILENAME = N'D:\DATA\test2.ndf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test_log', FILENAME = N'E:\LOG\Test_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO

All files are moved to F:\Data and F:\Log in the example

Full script:

DECLARE @DBNAME varchar(255) = 'Test' -- No [ ] 's
DECLARE @NewDataFileLocation varchar(4000) = 'F:\Data\' -- end with \
DECLARE @NewLogFileLocation varchar(4000) = 'F:\Log\' -- end with \
DECLARE @debug bit = 1; -- 1 to print, 0 to execute

-- main file table, can be kept after commands are exeucted if created as a physical table and the drop command and the end is removed

CREATE TABLE #files (logicalname varchar(255), 
                     physical_name varchar(4000),
                     movelogicalfilecommand nvarchar(max),
                     movephyisicalfilecommand nvarchar(max),
                     error bit)

-- insert the commands for the data files
INSERT INTO #files(logicalname,physical_name,movelogicalfilecommand,movephyisicalfilecommand)
SELECT  [name],
        [physical_name],
        'ALTER DATABASE ' +QUOTENAME(DB_NAME(database_id)) +' 
 MODIFY FILE (name='''+[name]+'''
             ,filename='''+@NewDataFileLocation+RIGHT(physical_name,(CHARINDEX('\',REVERSE(physical_name))-1))+''');',
           'ROBOCOPY "'+ 
           REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))+1,LEN(physical_name))) 
           + '" "' 
           + LEFT(@NewDataFileLocation,(LEN(@NewDataFileLocation)-1)) +  '" "'+
           RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) +'" '

FROM sys.master_files 
WHERE QUOTENAME(DB_NAME(database_id)) = QUOTENAME(@DBNAME)
AND type_desc = 'ROWS';

-- insert the commands for the log files

INSERT INTO #files(logicalname,physical_name,movelogicalfilecommand,movephyisicalfilecommand)
SELECT  [name],
        [physical_name],
        'ALTER DATABASE ' +QUOTENAME(DB_NAME(database_id)) +' 
 MODIFY FILE (name='''+[name]+'''
             ,filename='''+@NewLogFileLocation+RIGHT(physical_name,(CHARINDEX('\',REVERSE(physical_name))-1))+''');',           
           'ROBOCOPY "'+ 
           REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\',REVERSE(physical_name))+1,LEN(physical_name))) 
           + '" "' 
           + LEFT(@NewLogFileLocation,(LEN(@NewLogFileLocation)-1)) +  '" "'+
           RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) +'" '


FROM sys.master_files 
WHERE QUOTENAME(DB_NAME(database_id)) = QUOTENAME(@DBNAME)
AND type_desc = 'LOG';




-- Logically move the files in the system catalogs

DECLARE @cmd nvarchar(max), @movelogicalfilecommand nvarchar(max), @movephyisicalfilecommand nvarchar(max);
DECLARE C CURSOR FOR 
SELECT movelogicalfilecommand
FROM #files;
OPEN C;
FETCH NEXT FROM C INTO @movelogicalfilecommand;
WHILE @@FETCH_STATUS = 0
BEGIN

BEGIN TRY

    SET @cmd = @movelogicalfilecommand;
    IF @debug = 1
    PRINT @CMD;
    ELSE
    EXEC SP_EXECUTESQL @cmd;

END TRY

BEGIN CATCH

    UPDATE #files
    set error = 1
    WHERE movelogicalfilecommand = @movelogicalfilecommand;

END CATCH

FETCH NEXT FROM C INTO @movelogicalfilecommand;
END
CLOSE C;
DEALLOCATE C;

-- Set the database offline 
-- subsequently move the physical files with robocopy

DECLARE @cmd2 NVARCHAR(4000)
IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN

    SET @cmd = 'ALTER DATABASE '+QUOTENAME(@DBNAME)+' SET OFFLINE WITH ROLLBACK IMMEDIATE'
    IF @debug = 1
    PRINT @CMD;
    ELSE
    EXEC SP_EXECUTESQL @cmd;

    DECLARE C CURSOR FOR 
    SELECT movephyisicalfilecommand
    FROM #files;
    OPEN C;
    FETCH NEXT FROM C INTO @movephyisicalfilecommand;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        SET @cmd2 = @movephyisicalfilecommand;
        IF @debug = 1
        PRINT 'EXEC XP_CMDSHELL '''+@cmd2 +'''';
        ELSE
        EXEC XP_CMDSHELL @cmd2;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE movephyisicalfilecommand = @movephyisicalfilecommand;

    END CATCH

    FETCH NEXT FROM C INTO @movephyisicalfilecommand;
    END
    CLOSE C;
    DEALLOCATE C;
END
ELSE
SELECT 'ERROR moving logical files'

-- Set the database online

IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN

    BEGIN TRY

        SET @cmd = 'ALTER DATABASE '+QUOTENAME(@DBNAME)+' SET ONLINE'
        IF @debug = 1
        PRINT @CMD;
        ELSE
        EXEC SP_EXECUTESQL @cmd;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE movephyisicalfilecommand = @movephyisicalfilecommand;

    END CATCH
END
ELSE
SELECT 'ERROR moving physical files';


DECLARE @physical_name varchar(4000)

IF NOT EXISTS (SELECT * FROM #files where error = 1)
BEGIN
    DECLARE C CURSOR FOR 
    SELECT physical_name
    FROM #files;
    OPEN C;
    FETCH NEXT FROM C INTO @physical_name;
    WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        SET @cmd2 = 'DEL "'+@physical_name +'"';
        IF @debug = 1
        PRINT 'EXEC XP_CMDSHELL '''+@cmd2+'''';
        ELSE
        EXEC XP_CMDSHELL @cmd2;

    END TRY
    BEGIN CATCH

        UPDATE #files
        set error = 1
        WHERE physical_name = @physical_name;

    END CATCH

    FETCH NEXT FROM C INTO @physical_name;
    END
    CLOSE C;
    DEALLOCATE C;
END
ELSE
SELECT 'ERROR setting database online';

DROP TABLE #files;

Output example

ALTER DATABASE [Test] 
 MODIFY FILE (name='Test'
             ,filename='F:\Data\Test.mdf');
ALTER DATABASE [Test] 
 MODIFY FILE (name='test2'
             ,filename='F:\Data\test2.ndf');
ALTER DATABASE [Test] 
 MODIFY FILE (name='Test_log'
             ,filename='F:\Log\Test_log.ldf');
ALTER DATABASE [Test] SET OFFLINE WITH ROLLBACK IMMEDIATE
EXEC XP_CMDSHELL 'ROBOCOPY "G:\SQL\MSSQLSERVER" "F:\Data" "Test.mdf" '
EXEC XP_CMDSHELL 'ROBOCOPY "D:\DATA" "F:\Data" "test2.ndf" '
EXEC XP_CMDSHELL 'ROBOCOPY "E:\LOG" "F:\Log" "Test_log.ldf" '
ALTER DATABASE [Test] SET ONLINE
EXEC XP_CMDSHELL 'DEL "G:\SQL\MSSQLSERVER\Test.mdf"'
EXEC XP_CMDSHELL 'DEL "D:\DATA\test2.ndf"'
EXEC XP_CMDSHELL 'DEL "E:\LOG\Test_log.ldf"'

Answer 2

There may be a way to do it through xp_cmdshell, but I wouldn't recommend it at all. The way I always do it is run the statements to move the files in the database record, set the database offline, go move the files to where they should now reside (matching the location used in the move statements just run in SQL Server) and setting the database back online. Someone else may have a way for you to do it in T-SQL, but again, I think that's pretty risky when there is a safe way to do it already.