Moving data files to a different LUN or drive

Question

Moving data files to a different LUN or drive

sql-lover

SSCoach

Points: 18530
More actions
August 23, 2016 at 1:51 pm

#329583

I need to move several database files to a different LUN, same server of course. I know what's the process and TSQL, but I am trying to construct same statement for several clients via dynamic SQL and I am having some issues with two of the four steps. This is what I have so far:
/*
-Take the databases with the mdfs that I will move offline
-Update the mdf path location via TSQL (metadata change)
-Move the actual mdf files to the new LUN (or copy?) . This is done at Os level and via batch file or powershell.
-Take the databases online.
*/
/*
Order should be:
1)Change metadata
2)Put client offline
3)Move data file at Os level
4)Put client online.
*/
--This is step #2
DECLARE @total int, @id int, @sql nvarchar(MAX);
DECLARE @dbname varchar(200);
DECLARE @t table(ID int not null identity(1,1), name varchar(255));
INSERT INTO @t(name)
SELECT name FROM sys.databases WHERE name IN ('MyDatabase1','MyDatabase2')
ORDER BY name;
SET @total = @@ROWCOUNT;
SET @id=1;
WHILE @id <= @total
BEGIN
SELECT @dbname = name FROM @t WHERE ID = @id
SET @sql =
'
ALTER DATABASE [' + @dbname + '] SET OFFLINE WITH NO_WAIT;
'
PRINT @sql
--EXECUTE sp_executesql @sql
--,N'@dbname varchar(200)'
--,@dbname;
SET @id = @id + 1
END
GO
--This is step #3
--EXEC xp_cmdshell 'COPY ''D:\MSSQL\DATA\TestDB.md'' ''C:\NewLocation\TestDB.mdf'''
--GO
--This is step #4
DECLARE @total int, @id int, @sql nvarchar(MAX);
DECLARE @dbname varchar(200);
DECLARE @t table(ID int not null identity(1,1), name varchar(255));
INSERT INTO @t(name)
SELECT name FROM sys.databases WHERE name IN ('MyDatabase1','MyDatabase2')
ORDER BY name;
SET @total = @@ROWCOUNT;
SET @id=1;
WHILE @id <= @total
BEGIN
SELECT @dbname = name FROM @t WHERE ID = @id
SET @sql =
'
ALTER DATABASE [' + @dbname + '] SET ONLINE;
'
PRINT @sql
--EXECUTE sp_executesql @sql
--,N'@dbname varchar(200)'
--,@dbname;
SET @id = @id + 1
END
GO
what would be code to dynamically construct the TSQL that will change the file location via ALTER DATABASE? And the one for moving the files at Os level? I guess I need to "inject" the names for the move part but don't know how.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

x SSC-Insane Points: 23660 More actions · Answer 1

Looks like you're getting there. Check out these queries! And as always, test the heck out of your work in a development environment! Don't forget databases that might have multiple files!

select * from sys.databases

select * from sys.master_files

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 2

To concatenate multiple rows into one row, with one example result (for use with sys.master_files) being

MOVE 'MyDataFileLogicalName' TO 'C:\ThisNewFolder\MyNewFile.mdf', MOVE 'MyLogFileLogicalName' TO 'C:\ThisOtherNewFolder\MyNewLogFile.ldf'

search for "for xml path stuff concatenate"