SQL Relocate / Move database files

Hello,

This is not the most beautiful script I wrote, but functional it is J

The script is tested on SQL 2014. So I’m not sure if it will work on other SQL versions…

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')

$computer = 'localhost'

$sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $computer

#new data (mdf) and log (ldf) file location
$NewDataLoc = 'D:\SQLData' 
$NewLogLoc = 'D:\SQLLog'
#check if the path is typed correctly, without a \ at the end
if ($NewDataLoc.EndsWith('\') -eq $true){$NewDataLoc = $NewDataLoc.Substring(0,$NewDataLoc.Length-1)}
if ($NewLogLoc.EndsWith('\') -eq $true){$NewLogLoc = $NewLogLoc.Substring(0,$NewLogLoc.Length-1)}

#In some cases a DBA wants to locate the log file on an other disk. So that is why you have to add the path here as well
$NewTempDataLoc = "D:\SQLData"
$NewTempLogLoc = "D:\SQLLog"
#check if the path is typed correctly, without a \ at the end
if ($NewTempDataLoc.EndsWith('\') -eq $true){$NewTempDataLoc = $NewTempDataLoc.Substring(0,$NewTempDataLoc.Length-1)}
if ($NewTempLogLoc.EndsWith('\') -eq $true){$NewTempLogLoc = $NewTempLogLoc.Substring(0,$NewTempLogLoc.Length-1)}


#region Master DB

    $masterdb = $sqlserver.Databases.Item('master')
    $mastermdf = ($masterdb.FileGroups).files.FileName
    $masterldf = ($masterdb.LogFiles).filename

    $masterMDFname = $mastermdf.Split('\')[-1]
    $masterMDFDest = $NewDataLoc + '\' + $masterMDFname
    $masterLDFname = $masterldf.Split('\')[-1]
    $masterLDFDest = $NewLogLoc + '\' + $masterLDFname
    
    #Source: http://www.mikefal.net/2015/12/09/moving-your-master-database-with-powershell/
    $server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
    $startup = $server.Services | ? {$_.name -like 'MSSQL*'}
    $parameters = $startup.StartupParameters.Split(';')
    $Errorlog = $parameters | ? {$_ -match '-e'}
    $set = @("-d$masterMDFDest","$Errorlog","-l$masterLDFDest")
    $startup.startupparameters = $set -join ';'
    $startup.alter()
    $startup.Stop()
    sleep 10
    #if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10}
    Move-Item -Path $mastermdf -Destination $NewDataLoc -Force
    Move-Item -Path $masterldf -Destination $NewLogLoc -Force
    $startup.Start()

    $sqlserver.ConnectionContext.Disconnect()
#endregion

#region sysdatabases
$sysDBs = @('msdb','model')
foreach ($sysDB in $sysDBs){

    $db = $sqlserver.Databases.Item("$sysDB")
    $dbmdf = ($db.FileGroups).files.FileName
    $dbldf = ($db.LogFiles).filename

    $dbMDFname = $dbmdf.Split('\')[-1]
    $dbMDFDest = $NewDataLoc + '\' + $dbMDFname
    $dbLDFname = $dbldf.Split('\')[-1]
    $dbLDFDest = $NewLogLoc + '\' + $dbLDFname

    $db.LogFiles[0].FileName = $dbLDFDest
    ($db.FileGroups).files.FileName = $dbMDFDest
    $db.Alter()
    $server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
    $startup = $server.Services | ? {$_.name -like 'MSSQL*'}
    $startup.Stop()
    sleep 10
    #if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10}
    Move-Item -Path $dbmdf -Destination $NewDataLoc -Force
    Move-Item -Path $dbldf -Destination $NewLogLoc -Force
    $startup.Start()
    
}
#endregion

#region tempDB

    $tempdb = $sqlserver.Databases.Item('tempdb')
    $tempdbmdf = ($tempdb.FileGroups).files.FileName
    $tempdbldf = ($tempdb.LogFiles).filename

    $tempdbMDFname = $tempdbmdf.Split('\')[-1]
    $tempdbMDFDest = $NewTempDataLoc + '\' + $tempdbMDFname
    $tempdbLDFname = $tempdbldf.Split('\')[-1]
    $tempdbLDFDest = $NewTempLogLoc + '\' + $tempdbLDFname

    $tempdb.LogFiles[0].FileName = $tempdbLDFDest
    ($tempdb.FileGroups).files.FileName = $tempdbMDFDest
    $tempdb.Alter()
    $server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
    $startup = $server.Services | ? {$_.name -like 'MSSQL*'}
    $startup.Stop()
    sleep 10
    #if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10}
    Move-Item -Path $tempdbmdf -Destination $NewTempDataLoc -Force
    Move-Item -Path $tempdbldf -Destination $NewTempLogLoc -Force
    $startup.Start()
    
#endregion

#region User Databases

$Databases = $sqlserver.Databases | ? {$_.name -ne "tempdb" -and $_.name -ne "msdb" -and $_.name -ne "model" -and $_.name -ne "master"}
#$Databases | select name

foreach ($DB in $Databases){

    $DBname = $db.Name
    $DBowner = $db.Owner
    $dbmdf = ($db.FileGroups).files.FileName
    $dbldf = ($db.LogFiles).filename

    $dbMDFname = $dbmdf.Split('\')[-1]
    $dbMDFDest = $NewDataLoc + '\' + $dbMDFname
    $dbLDFname = $dbldf.Split('\')[-1]
    $dbLDFDest = $NewLogLoc + '\' + $dbLDFname

    $sqlserver.DetachDatabase("$DBname", $FALSE, $FALSE)
    $datastr = $NewDataLoc + '\' + $dbMDFname
    $logstr = $NewLogLoc + '\' + $dbLDFname
    $sc = $null
    $sc = new-object system.collections.specialized.stringcollection
    $sc.Add($datastr)
    $sc.Add($logstr)
    Move-Item -Path $dbmdf -Destination $NewDataLoc -Force
    Move-Item -Path $dbldf -Destination $NewLogLoc -Force
    $sqlserver.AttachDatabase("$DBname", $sc, $DBowner, [Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

#endregion

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement')

$computer = 'localhost'

$sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $computer

#new data (mdf) and log (ldf) file location

$NewDataLoc = 'D:\SQLData'

$NewLogLoc = 'D:\SQLLog'

#check if the path is typed correctly, without a \ at the end

if ($NewDataLoc.EndsWith('\') -eq $true){$NewDataLoc = $NewDataLoc.Substring(0,$NewDataLoc.Length-1)}

if ($NewLogLoc.EndsWith('\') -eq $true){$NewLogLoc = $NewLogLoc.Substring(0,$NewLogLoc.Length-1)}

#In some cases a DBA wants to locate the log file on an other disk. So that is why you have to add the path here as well

$NewTempDataLoc = "D:\SQLData"

$NewTempLogLoc = "D:\SQLLog"

#check if the path is typed correctly, without a \ at the end

if ($NewTempDataLoc.EndsWith('\') -eq $true){$NewTempDataLoc = $NewTempDataLoc.Substring(0,$NewTempDataLoc.Length-1)}

if ($NewTempLogLoc.EndsWith('\') -eq $true){$NewTempLogLoc = $NewTempLogLoc.Substring(0,$NewTempLogLoc.Length-1)}

#region Master DB

$masterdb = $sqlserver.Databases.Item('master')

$mastermdf = ($masterdb.FileGroups).files.FileName

$masterldf = ($masterdb.LogFiles).filename

$masterMDFname = $mastermdf.Split('\')[-1]

$masterMDFDest = $NewDataLoc + '\' + $masterMDFname

$masterLDFname = $masterldf.Split('\')[-1]

$masterLDFDest = $NewLogLoc + '\' + $masterLDFname

#Source: http://www.mikefal.net/2015/12/09/moving-your-master-database-with-powershell/

$server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer

$startup = $server.Services | ? {$_.name -like 'MSSQL*'}

$parameters = $startup.StartupParameters.Split(';')

$Errorlog = $parameters | ? {$_ -match '-e'}

$set = @("-d$masterMDFDest","$Errorlog","-l$masterLDFDest")

$startup.startupparameters = $set -join ';'

$startup.alter()

$startup.Stop()

sleep 10

#if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10}

Move-Item -Path $mastermdf -Destination $NewDataLoc -Force

Move-Item -Path $masterldf -Destination $NewLogLoc -Force

$startup.Start()

$sqlserver.ConnectionContext.Disconnect()

#endregion

#region sysdatabases

$sysDBs = @('msdb','model')

foreach ($sysDB in $sysDBs){

$db = $sqlserver.Databases.Item("$sysDB")

$dbmdf = ($db.FileGroups).files.FileName

$dbldf = ($db.LogFiles).filename

$dbMDFname = $dbmdf.Split('\')[-1]

$dbMDFDest = $NewDataLoc + '\' + $dbMDFname

$dbLDFname = $dbldf.Split('\')[-1]

$dbLDFDest = $NewLogLoc + '\' + $dbLDFname

$db.LogFiles[0].FileName = $dbLDFDest

($db.FileGroups).files.FileName = $dbMDFDest

$db.Alter()

$server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer

$startup = $server.Services | ? {$_.name -like 'MSSQL*'}

$startup.Stop()

sleep 10

#if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10}

Move-Item -Path $dbmdf -Destination $NewDataLoc -Force

Move-Item -Path $dbldf -Destination $NewLogLoc -Force

$startup.Start()

}

#endregion

#region tempDB

$tempdb = $sqlserver.Databases.Item('tempdb')

$tempdbmdf = ($tempdb.FileGroups).files.FileName

$tempdbldf = ($tempdb.LogFiles).filename

$tempdbMDFname = $tempdbmdf.Split('\')[-1]

$tempdbMDFDest = $NewTempDataLoc + '\' + $tempdbMDFname

$tempdbLDFname = $tempdbldf.Split('\')[-1]

$tempdbLDFDest = $NewTempLogLoc + '\' + $tempdbLDFname

$tempdb.LogFiles[0].FileName = $tempdbLDFDest

($tempdb.FileGroups).files.FileName = $tempdbMDFDest

$tempdb.Alter()

$server = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer

$startup = $server.Services | ? {$_.name -like 'MSSQL*'}

$startup.Stop()

sleep 10

#if ($startup.ServiceState -eq 'running'){$startup.Stop(); sleep 10}

Move-Item -Path $tempdbmdf -Destination $NewTempDataLoc -Force

Move-Item -Path $tempdbldf -Destination $NewTempLogLoc -Force

$startup.Start()

#endregion

#region User Databases

$Databases = $sqlserver.Databases | ? {$_.name -ne "tempdb" -and $_.name -ne "msdb" -and $_.name -ne "model" -and $_.name -ne "master"}

#$Databases | select name

foreach ($DB in $Databases){

$DBname = $db.Name

$DBowner = $db.Owner

$dbmdf = ($db.FileGroups).files.FileName

$dbldf = ($db.LogFiles).filename

$dbMDFname = $dbmdf.Split('\')[-1]

$dbMDFDest = $NewDataLoc + '\' + $dbMDFname

$dbLDFname = $dbldf.Split('\')[-1]

$dbLDFDest = $NewLogLoc + '\' + $dbLDFname

$sqlserver.DetachDatabase("$DBname", $FALSE, $FALSE)

$datastr = $NewDataLoc + '\' + $dbMDFname

$logstr = $NewLogLoc + '\' + $dbLDFname

$sc = $null

$sc = new-object system.collections.specialized.stringcollection

$sc.Add($datastr)

$sc.Add($logstr)

Move-Item -Path $dbmdf -Destination $NewDataLoc -Force

Move-Item -Path $dbldf -Destination $NewLogLoc -Force

$sqlserver.AttachDatabase("$DBname", $sc, $DBowner, [Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

#endregion

Categories

SQL Relocate / Move database files

Sander

Categories