As always, the script from this post can also be found on our GitHub repo, HERE
You’ve got a SQL Server with a few hundred databases on it (to be honest it doesn’t even need to be quite that many) and you need to move all the data and log files to a new location. Perhaps you’re going to be migrating onto a new, shiny SAN or maybe your disks are just about full and you need to shift a bunch of the files off somewhere else.
The first thing that you’re going to need to do is change the paths of the files in SQL. That’s easy enough to do with an ALTER DATABASE statement.
ALTER DATABASE SQLUndercover MODIFY FILE (NAME SQLUndercover_Log, FILENAME = 'F:SQLLogsSQLUndercover_Log.ldf'
But that’s going to get very tedious very quickly if you’ve got to do that for a whole lotta databases. So to help out, I thought I’d share a little script that I’ve been using for a while (or a variation on it at least) to make the process far easier and generate all the ALTER statements for you.
/******************************************************************
Author: David Fowler
Revision date: 15/04/2020
Version: 1
© www.sqlundercover.com
This script is for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of this script,in whole or in part, is prohibited without the author's express
written consent.
The software is provided "as is", without warranty of any kind, express or
implied, including but not limited to the warranties of merchantability,
fitness for a particular purpose and noninfringement. in no event shall the
authors or copyright holders be liable for any claim, damages or other
liability, whether in an action of contract, tort or otherwise, arising from,
out of or in connection with the software or the use or other dealings in the
software.
******************************************************************/--config variables
DECLARE @logpath NVARCHAR(260) = 'D:SQLData'
DECLARE @datapath NVARCHAR(260) = 'E:SQLLogs'
DECLARE @movelogs BIT = 1
DECLARE @movedata BIT = 1
--runtime variables
DECLARE @STMT NVARCHAR(4000)
--uncomment the predicates to include and exclude databases as required
DECLARE Files CURSOR STATIC FORWARD_ONLY
FOR
SELECT DB_NAME(database_id)
,type
,name
,REVERSE(SUBSTRING(REVERSE(physical_name), 0, CHARINDEX('', REVERSE(physical_name))))
FROM sys.master_files
WHERE type IN (0,1)
--AND DB_NAME(database_id) IN ('sqlundercover')--uncomment to include databases
--AND DB_NAME(database_id) NOT IN ('master','tempdb','msdb','model')--uncomment to exclude databases
DECLARE @DBName SYSNAME
DECLARE @type TINYINT
DECLARE @logicalname SYSNAME
DECLARE @physicalname NVARCHAR(260)
--check filepaths finish with a and add if they don't
IF (SUBSTRING(@datapath, LEN(@datapath), 1) != '')
SET @datapath += N''
IF (SUBSTRING(@logpath, LEN(@logpath), 1) != '')
SET @logpath += N''
OPEN Files
FETCH NEXT
FROM Files
INTO @DBName
,@type
,@logicalname
,@physicalname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STMT = N'ALTER DATABASE ' + QUOTENAME(@DBName) + N' MODIFY FILE (NAME = ' + @logicalname + N', FILENAME = '''
SET @STMT += CASE
WHEN @type = 0
THEN @datapath + @physicalname
WHEN @type = 1
THEN @logpath + @physicalname
END
PRINT @STMT
FETCH NEXT
FROM Files
INTO @DBName
,@type
,@logicalname
,@physicalname
END
CLOSE Files
DEALLOCATE Files
There are a couple of variables that you’re going to want to change, @logpath and @datapath should match your new log and data paths respectively and @movelogs and @movedata when set to 0 will cause the script to ignore log or data files.
You can also uncomment the lines of the WHERE clause to explicitly include or exclude certain databases.
Run that and it’ll generate all the ALTER DATABASE commands to repoint SQL at the new paths.
But What About Moving the Actual Files?
So far you’ll only have repointed SQL to the new file paths, the actual files themselves will still be where they’ve always been.
Before you can move anything, you’ll need to stop your SQL Service.
Once that’s done you can get on with moving the files. You could just do a simple copy and past from the old place to the new but that brings with it the problem that you may find that the permissions aren’t copied across quite as you’d have expected and SQL Server might have trouble accessing the file.
My preferred option is to use Robocopy from within PowerShell, you can do the same in the command prompt if you wanted. Either way, by using the /COPYALL switch, you will cause all the file attributes including the ACL (permissions) to also go over.
RoboCopy SourceLocation DestinationLocation *.* /COPYALL
You’ve probably already guessed that *.* is the file mask with * being a wildcard and meaning that you’ll be copying all files from the SourceLocation. If you happened to want to limit the copy to log files only, you could change that mask to *.ldf.
With the files all copied, it’s now just a case of bringing your SQL Server back online.
When I do these sorts of moves, I do have a PowerShell script that brings these steps as well as a few others together which I may share one day but for now I hope that’s given you an idea of how to easily migrate your SQL Server’s files in bulk, easily.