Rename a local SQL Server instance

db_swap.bat

@ECHO OFF
REM detach, rename and reattach one database to another
REM @author ICC 11 Sep 09
REM @param sting Server name i.e. <Computer Name>\<SQL Server Name> e.g. .\SQLEXPRESS or COCO\SQLEXPRESS
REM @param string Existing database name e.g. mydb
REM @param string New database name e.g. mydb2
REM @return none renames database, detaches database files, renames them and reattaches
REM usage db_swap.bat .\SQLEXPRESS mydb mydb2

REM *** The following error occurs if you use a forward slash not a back slash
REM Named Pipes Provider: Could not open a connection to SQL Server [67].
REM Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

ECHO ***MUST BE RUN FROM DOS BOX RUN AS ADMINISTRATOR***

SET OrigDir=%CD%
SET OrigDrive=%CD:~0,2%
SET LogExt=log
REM Must have 3 argunments
IF "%1"=="" GOTO Syntax
IF "%1"=="/?" GOTO Syntax

IF %PROCESSOR_ARCHITECTURE% == x86 ( 
	IF "%PROCESSOR_ARCHITEW6432%" == "" (
		GOTO X86
	) ELSE (
		GOTO X64
	)
) ELSE (
	GOTO X64
)

:X86
SET DBDir=C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Data
GOTO Main

:X64
SET DBDir=C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Data

:Main
IF NOT EXIST "%DBDir%\%2.mdf" GOTO File2

IF NOT EXIST "%DBDir%\%2_%LogExt%.ldf" GOTO File3

:Exec
ECHO Database log file found
C:
CD "%DBDir%"
SQLCMD -S %1 -E -Q "sp_renamedb N'%2', N'%3';"
SQLCMD -S %1 -E -Q "sp_detach_db '%3';"
REM @todo check for SQL errors before proceeding
MOVE "%2.mdf" "%3.mdf"
MOVE "%2_%LogExt%.ldf" "%3_%LogExt%.ldf"
SQLCMD -S %1 -E -Q "sp_attach_db '%3', '%DBDir%\%3.mdf','%DBDir%\%3_%LogExt%.ldf';"
ECHO Finished!
GOTO End

:File2
ECHO Could not find first database file: %2
GOTO End

:File3
REM there are some 2008 log files that are created as DB_0.LDF not DB_log.ldf as expected - appears to be restored databases
ECHO Could not find first database log file: %2_%LogExt%.ldf, trying LogExt=0
SET LogExt=0
IF EXIST "%DBDir%\%2_%LogExt%.ldf" GOTO Exec
ECHO LogExt=0 didn't work either. Exiting.
GOTO End

:Syntax
ECHO usage: db_swap.bat "Computer\SQL Server" curr_db new_db

:End
%OrigDrive%
CD "%OrigDir%"
PAUSE