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 $queryMove $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.)
