Posts tagged “Move Temp Database and log

Moving SQL Temp database and log files using Powershell


If you need to move the SQL Temp database and log files to a new drive/folder, you need to run a few SQL queries and restart SQL service. The following script automates this task. This can be particularly useful if you need to do this task for many SQL servers.

This script needs SQLPS module. This is installed by default with SQL 2012 and higher versions. If you’re using an older SQL version, you need to download and install the following 3 components in order:

  1. Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi)
  2. Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi)
  3. Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi)

I’ve linked to the x64 versions of these files, but you need to get the version that matches your OS.

You also need to remember to run the command:

Import-Module SQLPS

then close ISE and reopen it again. After that you can run the script.

You need to edit lines 6 and 7 to enter the new locations of where you’d like to move the Temp DB and Log files. Make sure the necessary folder exists before you run the script (h:\SQL-Temp folder in this example).

Move-SQL-Temp

# Script to move temp DB and its log from one disk location to another
# Sam Boutros
# 6/5/2014
# Move-Temp.ps1
#
$NewTempDBLoc = “h:\SQL-Temp\tempdb.mdf”
$NewTemplogLoc = ‘h:\SQL-Temp\templog.ldf’
# End Data entry section
#
$TempDB = “N'” + $NewTempDBLoc + “‘” # This format is needed for Invoke-SQLCMD
$TempLog = “N'” + $NewTempLogLoc + “‘”
$Loc = Get-Location
$Date = Get-Date -format yyyyMMdd_hhmmsstt
$logfile = $Loc.path + “\Move-Temp_” + $Date + “.txt”
#
function log($string, $color)
{
if ($Color -eq $null) {$color = “white”}
write-host $string -foregroundcolor $color
$temp = “: ” + $string
$string = Get-Date -format “yyyy.MM.dd hh:mm:ss tt”
$string += $temp
$string | out-file -Filepath $logfile -append
}
#
# Import-Module SQLPS # See notes..
log “Modifying Temp DB and Log location..” green
Invoke-SQLCMD -Query “USE [master]”
Invoke-SQLCMD -Query “ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = $TempDB ) ”
Invoke-SQLCMD -Query “ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = $TempLog) ”
Set-Location -Path ($logfile.Split(“:”)[0] + “:”)
log “Restaring SQL Service..” green
Restart-Service MSSQLSERVER -Force

 

Advertisements