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:
References:
Enjoy!
Hiram
