Servername in SQLCMD script

A variable can be used in both SQLCMD and T-SQL statements.
This is a very short example:
:Setvar _servername "SANDY.sqladmin.lan"
:CONNECT $(_servername)
SELECT
  N'$(_servername)' AS [_servername]
 ,SERVERPROPERTY('SERVERNAME') AS [servername];

Generate sqlcmd statements

I have a change implemented by several (10+) T-SQL script files in sizes from about 200 KB to 739 MB. Executing a 739 MB T-SQL script in Microsoft SQL Server Management Studio (SSMS) will fail, and typing several sqlcmd statements manually will be tedious and faulty.
By placing alle the T-SQL script files in one folder and using a single PowerShell statement all the sqlcmd statements can be created nice and quick:
ls *.sql | %{"`n@ECHO $($_.BaseName)...`nsqlcmd.exe -E -S %DbInstanceName% -i `"$($_.Name)`" -o `"$($_.BaseName).log`" -r1 -b`n@ECHO :: ErrorLevel = %ERRORLEVEL%`n@ECHO."}
The generated sqlcmd statements I can then copy into a Windows Shell script file (.cmd) where the variable DbInstanceName is defined by
@SET DbInstanceName="sandbox.sqladmin.lan\SSDB01"
It is on purpose that the Windows Shell script continues on error - in this specific case.

The logfile defined by the parameter -o in each sqlcmd statement holds output as results (e.g. SELECT), events (e.g. PRINT) and errors (e.g. RAISERROR).
This is verified by using a T-SQL test script
SELECT @@VERSION AS [sql_version];
GO
PRINT N':: Hello from PRINT';
GO
RAISERROR( N':: Hello from RAISERROR', 18, 0 );
and calling this from a sqlcmd statement generated by the PowerShell statement above.

Dedicated Administrator Connection (DAC)

Usually the sqlcmd tool is used for automation, but I also think of it as my last access to a running database instance when the dirt hits the fan. sqlcmd is a part of a database instance installation - you don't have to install management tools.
But when the resources of a Windows Server host or a database instance is consumed by a wild running process or session, then the Dedicated Administrator Connection (DAC) might be your last option - before shutting down the box.

SQLCMD usage
Usually it is possible to use the host name, but once I've experienced a situation where the DNS server(-s?) was down. In that case you have to enter the IP address of the host. This also gives the fastest connection to the host.

Syntax
SQLCMD.EXE -S "" -A
SQLCMD.EXE -S "Admin:" -E

Example
SQLCMD.EXE -S "SANDBOX.sqladmin\SSDB42" -A
SQLCMD.EXE -S "Admin:SANDBOX.sqladmin\SSDB42" -E

Please notice that the Browser Service must be running.

Management Studio (SSMS) usage
Use the value like the SQLCMD -S parameter.

TCP port number
The TCP port number of the DAC is shown in the Registry and the SQL Server Errorlog.

Registry
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SSDB\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp\TcpDynamicPorts [REG_SZ]
ERRORLOG
2009-03-18 21:35:15.26 Server Dedicated admin connection support was established for listening locally on port 1434.

If a host has several database instances installed, each instance DAC has it own TCP port assigned. Then you have to look into several Registry branches to get the TCP port number.

The DAC TCP port number is unfortunately not available through SMO or the SQL Server WMI provider :-(

Remote DAC
By default the DAC is available only locally on the host, but it is possible to enable remote DAC.
At SQL Server 2005 it was possible to enable remote DAC with the Surface Area Configuration point-and-click tool. But this tool is not part of SQL Server 2008 where you have to enable Remote DAC by
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Reference

MSDN library: Using a Dedicated Administrator Connection

Kalen Delaney et al: "Microsoft SQL Server 2008 Internals", pp 27.

Executing a T-SQL script file

I would like to execute a developers T-SQL script file automated, so that I can deploy without having to press the button every single time.
To do this I'm working on a PowerShell function, that uses both the Windows Shell (cmd.exe) end the SQL Server sqlcmd (SQLCMD.EXE) utility.

function Invoke-SqlCmd_cmd {
 param(
 [string]$WindowsName = $( throw "Windows host name required as parameter." ),
 [string]$SqlInstanceName = $( throw "SQL Server database instance name required as parameter." ),
 [string]$DatabaseName = $( throw "Database name required as parameter." ),
 [string]$SqlScriptFileName = $( throw "T-SQL script file name required as parameter." ) )

 if( $SqlInstanceName -eq '.' )
 { $DbInstanceName = $WindowsName }
 else
 { $DbInstanceName = $WindowsName + '\' + $SqlInstanceName }

 & cmd.exe /c "SQLCMD.EXE -E -S `"$DbInstanceName`" -d `"$DatabaseName`" -i `"$SqlScriptFileName`" -u -b -m-1"
}

The function is used like this:
Invoke-SqlCmd_cmd 'Sandbox' '.' 'master' 'C:\SQLAdmin\SqlCmd.sql'

I use '.' (dot) to identify a default database instance in my repository, but any other identification can be motivated.

I haven't found a way round the Windows Shell, which I think is a indicator that PowerShell is still some way from complete. Maybe the PowerShell V2 CmdLet Invoke-Command does a better job...

The documentation is at MSDN Library on sqlcmd and at TechNet Library on Windows Shell (cmd.exe).