Making the world a better place, one database at a time

PowerShell Script to reset the local instance of SQL Server

I use virtual machines a lot for development and testing.  I typically start with a sysprepped base image that I then initialize every time I need a new machine.  One issue is that SQL Server doesn’t know it has been sysprepped… if you execute

   1: SELECT @@SERVERNAME

You will get the name of the machine from when you installed SQL Server.

I use the following PowerShell script to reset the name of the local instance to the current name of the machine:

# Load Assemblies we need to access SMO
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.WmiEnum")
$asm = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

##############################################################################
# Description:
# Change the name SQL Server instance name (stored inside SQL Server) to the name 
# of the machine.  When a machine is unboxed after being sysprepped, it will still
# use the original SQL Server name as the instance name for SQL Server
# 
# Input:
#
# Output:
# 
# Author: DDarden
# Date  : 200904030748
# 
# Change History
# Date        Author          Description
# --------    --------------  -------------------------------------------------
# 
###############################################################################
function global:Set-SqlServerInstanceName{
    Write "Renaming SQL Server Instance"
    $smo = 'Microsoft.SqlServer.Management.Smo.'
    
    $server = new-object ($smo + 'server') .
    $database = $server.Databases["master"]
    $mc = new-object ($smo + 'WMI.ManagedComputer') .
    
    $newServerName = $mc.Name
    
    $database.ExecuteNonQuery("EXEC sp_dropserver @@SERVERNAME")
    $database.ExecuteNonQuery("EXEC sp_addserver '$newServerName', 'local'")
    
    Write-Host "Renamed server to '$newServerName'`n"
}

# Set the SQL Server instance name to the current machine name
# MSSQLSERVER service needs to be restarted after this change

Set-SqlServerInstanceName