Rename a lot of databases with PowerShell

I was given the task of renaming a bunch of databases named something like “name,” “name_1,” “name_2,” etc., to “nameold,” “nameold_1,” “nameold_2,” etc.  Our convention is for the database name to match the file names.  So…

$controlfile=”path\to\listofdatabases.txt”
$server=”servername\instance”
$mdfpath=”c:\path\to\Data”
$ldfpath=”c:\path\to\Log”
$dbnameappend=”old”
$dbnameappendarchive = $dbnameappend + “_”

foreach ($dbname in get-content $controlfile)
{
$newdbname = $dbname + $dbnameappend
if ($dbname -match ‘_[0-9]’) {$newdbname = $dbname -replace ‘_’, $dbnameappendarchive}
Write-Host $newdbname

$mdf=$dbname + “.mdf”
$ldf=$dbname + “_log.ldf”
$newmdf=$newdbname + “.mdf”
$newldf=$newdbname + “_log.ldf”

$query = “ALTER DATABASE ” + $dbname + ” SET SINGLE_USER WITH ROLLBACK IMMEDIATE;”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $dbname + ” MODIFY NAME = ” + $newdbname
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET offline”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]_log’, FILENAME = ‘” + $ldfpath + “\” + $newldf + “‘)”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]’, FILENAME = ‘” + $mdfpath + “\” + $newmdf + “‘)”
sqlcmd -E -S $server -Q $query

Move $ldfpath\$ldf $ldfpath\$newldf -Force
Move $mdfpath\$mdf $mdfpath\$newmdf -Force

$query = “ALTER DATABASE ” + $newdbname + ” SET online”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET MULTI_USER;”
sqlcmd -E -S $server -Q $query

}

This was so fast that I decided to share the joy. 🙂

(I plan to use this script if I ever need to bring my log-shipped databases online, hence the append.  I might need to do thing_oldprod, stuff_oldprod, whatsit_oldprod, etc.)

Leave a Comment

Filed under powershell, scripting

Leave a Reply

Your email address will not be published. Required fields are marked *