Automate - Detach Db's - Move Files Different Drive

Question

Automate - Detach Db's - Move Files Different Drive

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
December 1, 2015 at 2:01 pm

#305717

I have a task where i have to move 100's databases to another drive on the same server. I could spent some time to create a script, was wondering if anyone has done this before , will save me some time :). I need to do following things.:
i) I should be able to provide list of databases, script should be able to identify mdf ,ndf and ldf files location and then move to respective drives (data and log).
ii) After move it should be able to attach databases with the new location.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i use this snippet a lot; but it is centric to a single database, but i bet it gets you 90% there.

it assumes I KNOW the destination for the files, and finds anything not already in that path, and builds the commands and instructio ns.

results for one db:

ALTER DATABASE [DBA_Utilities] SET OFFLINE

--MOVE The File Z:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DBA_Utilities.mdf To D:\MSSQL\Data\DBA_Utilities.mdf

--then execute this command

ALTER DATABASE [DBA_Utilities] MODIFY FILE ( NAME = DBA_Utilities, FILENAME = 'D:\MSSQL\Data\DBA_Utilities.mdf')

--MOVE The File L:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\DBA_Utilities_log.LDF To L:\MSSQL\Logs\DBA_Utilities_log.LDF

--then execute this command

ALTER DATABASE [DBA_Utilities] MODIFY FILE ( NAME = DBA_Utilities_log, FILENAME = 'L:\MSSQL\Logs\DBA_Utilities_log.LDF')

ALTER DATABASE [DBA_Utilities] SET ONLINE

DECLARE @DBToMove varchar(100) = 'DBA_Utilities'

DECLARE @TargetDataLocation varchar(500) = 'D:\MSSQL\Data';

DECLARE @TargetLogsLocation varchar(500) = 'L:\MSSQL\Logs';

WITH MyData AS

(

SELECT 0 AS SortOrder, @DBToMove As DatabaseName,'' AS CurrentPath,'' As Currentfile,'ALTER DATABASE ' + quotename(@DBToMove) + ' SET OFFLINE ' AS cmd UNION ALL

SELECT 2 As SortOrder,db.name As DatabaseName,

REVERSE(SUBSTRING(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)),256)) As CurrentPath,

REVERSE(LEFT(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)) -1)) As Currentfile,

'--MOVE The File ' + mf.physical_name + ' To ' + + @TargetDataLocation + '\' + REVERSE(LEFT(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)) -1)) + '

--then execute this command

ALTER DATABASE ' + quotename(db.name) + ' MODIFY FILE ( NAME = ' + mf.name + ', FILENAME = ''' + @TargetDataLocation + '\' + REVERSE(LEFT(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)) -1)) + ''') '

AS cmd

FROM sys.databases db

inner join sys.master_files mf

on db.database_id = mf.database_id

where db.database_id > 4

AND mf.type_desc = 'ROWS'

AND CHARINDEX(@TargetDataLocation,mf.physical_name) = 0

),

MyLogs AS

(

SELECT 4 AS SortOrder, @DBToMove As DatabaseName,'' AS CurrentPath,'' As Currentfile,'ALTER DATABASE ' + quotename(@DBToMove) + ' SET ONLINE ' AS cmd UNION ALL

SELECT 3 AS SortOrder,db.name As DatabaseName,

REVERSE(SUBSTRING(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)),256)) As CurrentPath,

REVERSE(LEFT(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)) -1)) As Currentfile,

'--MOVE The File ' + mf.physical_name + ' To ' + + @TargetLogsLocation + '\' + REVERSE(LEFT(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)) -1)) + '

--then execute this command

ALTER DATABASE ' + quotename(db.name) + ' MODIFY FILE ( NAME = ' + mf.name + ', FILENAME = ''' + @TargetLogsLocation + '\' + REVERSE(LEFT(REVERSE(mf.physical_name),CHARINDEX('\',REVERSE(mf.physical_name)) -1)) + ''') ' AS cmd

FROM sys.databases db

inner join sys.master_files mf

on db.database_id = mf.database_id

where db.database_id > 4

AND mf.type_desc = 'LOG'

AND CHARINDEX(@TargetLogsLocation,mf.physical_name) = 0

)

SELECT Databasename,SortOrder,cmd FROM MyData WHERE @DBToMove = NULL OR @DBToMove = Databasename

UNION ALL

SELECT Databasename,SortOrder,cmd FROM MyLogs WHERE @DBToMove = NULL OR @DBToMove = Databasename

order by Databasename,SortOrder

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

curious_sqldba

SSC-Dedicated

Points: 36494

December 1, 2015 at 2:32 pm

#1843338

how do the files move from one drive to another?

Lowell SSC Guru Points: 323518 More actions · Answer 3

curious_sqldba (12/1/2015)
how do the files move from one drive to another?

i'd go with doing it physically.

if you really want to automated it,

you might do it via xp_cmdShell and the MOV command, if you REALLY want to script it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

Yeah i want a script at-least for this go round, there are about 500 databases. I will keep looking.