SQLCMD variables for input SQL file

In short, this is an example of how you do it. Set a couple of variables, use -v parameter sqlcmdVariable=”%batVariable%”.

The following contains more details on how to use SQLCMD variables for an input SQL file. Note, I am not advising you to you use this as your routine backup method. If you’re looking for a backup process, there are much better methods.

0.Run.bat

REM cmd /K "cd /d C:\Deployments\20170626\"

@ECHO Off

Set Source=ProdSvrSQL1
Set Target=QASvrSQL1
Set Database=Trollhunters
Set Databases=Trollhunters','WideWorldImporters','tpcxbb_1gb
Set Chg=449
Set Env=%Chg%
Set PrintedFile=3.PrintedRestore.sql
set root=%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\
set sqlbin="C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\"

@ECHO ********** STARTED %DATE% %TIME% ********** >>Output%Env%.txt

@ECHO OFF

@ECHO ON
@ECHO Started Deployment Steps >>Output%Env%.txt
@ECHO OFF

GOTO START
:START

@Echo Running 1.Backup.sql on Source.>>Output%Env%.txt
%sqlbin%sqlcmd /S "%Source%" /d "TEMPDB" /i "1.Backup.sql" /b -v Database="%Database%" /b -v chg="%chg%" >>Output%Env%.txt
IF ERRORLEVEL 1 GOTO ROLLBACK 

@Echo Running 1.Backup.sql on Target before REPLACE.>>Output%Env%.txt
%sqlbin%sqlcmd /S "%Target%" /d "TEMPDB" /i "1.Backup.sql" /b -v Database="%Database%" /b -v chg="%chg%" >>Output%Env%.txt
IF ERRORLEVEL 1 GOTO ROLLBACK 

@Echo Running 2.PrintRestore.sql>>Output%Env%.txt
%sqlbin%sqlcmd /S "%Source%" /d "%Database%" /i "2.PrintRestore.sql" /b -v Database="%Database%" /b -v chg="%chg%" >%PFile%
IF ERRORLEVEL 1 GOTO ROLLBACK 

PING 127.0.0.1 -n 10 > NUL

@Echo Running %PrintedFile%>>Output%Env%.txt
%sqlbin%sqlcmd /S "%Target%" /d "MASTER" /i "%PrintedFile%" /b -v Database="%Database%">>Output%Env%.txt
IF ERRORLEVEL 1 GOTO ROLLBACK 

@Echo Running 4.SyncLogins.sql>>Output%Env%.txt
%sqlbin%sqlcmd /S "%Target%" /d "%Database%" /i "4.SyncLogins.sql" /b -v Database="%Database%">>Output%Env%.txt
IF ERRORLEVEL 1 GOTO ROLLBACK 

@Echo Running 5.ResetSettingsQA.sql>>Output%Env%.txt
%sqlbin%sqlcmd /S "%Target%" /d "%Database%" /i "5.ResetSettingsQA.sql">>Output%Env%.txt
IF ERRORLEVEL 1 GOTO ROLLBACK 

@Echo Running 6.ResetAccounts.sql>>Output%Env%.txt
%sqlbin%sqlcmd /S "%Target%" /d "%Database%" /i "6.ResetAccounts.sql">>Output%Env%.txt
IF ERRORLEVEL 1 GOTO ROLLBACK 

@ECHO ************* COMPLETED %DATE% %TIME% ************* >>Output%Env%.txt
@ECHO OFF

GOTO END

:ROLLBACK
@ECHO ********** ROLLBACK %DATE% %TIME% ********** >>Output%Env%.txt
@ECHO Date: %Date% %Time% PublishErrorlevel:%errorlevel% >>Output%Env%.txt
@ECHO Review Output%Env%.txt to determine next steps.>>Output%Env%.txt
start %root%PowerShell.exe -nologo -executionpolicy bypass -File ".\SendOutput.ps1" 

:END
@ECHO ********** END %DATE% %TIME% ********** >>Output%Env%.txt
start %root%PowerShell.exe -nologo -executionpolicy bypass -File ".\SendOutput.ps1"
CALL Output%Env%.txt
PAUSE

1.Backup.sql

--:connect ProdSvrSQL1
--:setvar CHG  "626"
--:setvar DISK "\\fs1\share\Deployments\20170626\"
--:setvar Database "ᕙ༼,இܫஇ,༽ᕗ"

--  +--------+
--  | Backup |
--  +--------+
set nocount on
use tempdb
go
declare @DB sysname, @SQL nvarchar(max), @Count int, @chg nvarchar(10), @timestamp nvarchar(25), @LastPath nvarchar(520)
set @chg = '$(CHG)'
set @LastPath = '$(DISK)'

select @timestamp = replace(replace(replace(replace(convert(nvarchar(25), getdate(), 121),'-',''),':',''),'.',''),' ','_');

if ISNULL(@LastPath,'') = ''
begin
	select top 1 @LastPath=substring(bmf.physical_device_name,1,charindex('_backup',bmf.physical_device_name,0))
	from   		msdb.dbo.backupmediafamily as bmf
	inner join 	msdb.dbo.backupset as bs on bmf.media_set_id = bs.media_set_id
	where  		bs.type = 'D' and bs.database_name in ('$(Database)')
	order by 	backup_start_date desc;
end;

if object_id('Tempdb..#dbs') is not null drop table #dbs;
select name into #dbs from sys.databases where name in ('$(Database)') --and database_id > 4

select @Count = count(*) from #dbs;

while @Count > 0
	begin
		select top 1 @DB = name FROM #dbs
		set @SQL = 'backup database ['+@DB+'] to disk = '''+@LastPath+@DB+'_backup_'+@timestamp+'_pre'+@chg+'.bak''
			with noformat, noinit, skip, norewind, nounload, compression
			, encryption(algorithm = AES_256, server certificate = [BackupCertWithPK])
			, stats = 25;'
		exec sp_executesql @SQL; print @SQL;
		delete from #dbs where name = @DB;
		select @Count = Count(*) from #dbs;
	end
if object_id('Tempdb..#dbs') is not null drop table #dbs;
go

SendOutput.ps1

 

$file = "C:\Deployments\20170626\Output*.txt"
$lines = get-content $file | measure-object -line | select Lines

if ($lines.Lines.value -gt 1){
##Send Email Report
$smtpServer = "ProdSvrMail1"
$att = new-object Net.Mail.Attachment($file)
$msg = new-object Net.Mail.MailMessage
$msg.From = "hiram@fleitasarts.com"
$msg.To.Add("hiram@fleitasarts.com")
$msg.Subject = "Deployment Output"
$msg.IsBodyHtml = $True 
$msg.Body = "See attached file."
$msg.Attachments.Add($att)

$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($msg)
$att.Dispose()
} else {write-host $lines.Lines

$smtpServer = "ProdSvrMail1"
$msg = new-object Net.Mail.MailMessage
$msg.From = "hiram@fleitasarts.com"
$msg.To.Add("hiram@fleitasarts.com")
$msg.Subject = "Deployment Success"
$msg.IsBodyHtml = $True 
$msg.Body = "Success. There were no Errors."

$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($msg)
$att.Dispose()
}

If you’d like to understand more, keep reading.

SQLCMD is a great tool to automate database tasks, deployments or simply querying across multiple servers without a Linked Server. This command line tool provides a robust control of an entire process. One of the key items, or perhaps a favorite of mine, is the ability to automate by passing a variable value to an input .sql script file.

For example, lets say you want to take an ad-hoc backup of a few databases right before making your deployment changes. This is a good practice but depending on the database size you may be able to take a full, differential or transactional log backup. Also, if you want the ad-hoc backup to be full on a primary without breaking your usual backup chain, then consider using with Copy_Only. Lastly, for security purposes I strongly advise to use with Encryption(algo aes_256 & a valid server cert)

Consider the following folder contents:

SQLCMDDeployment

References:

  1. sqlcmd-utility
  2. sqlcmd use with scripting variables

Enjoy!

Hiram

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.