How do you run a transaction log backup using parameters?

Apr 30, 2013 at 3:32 PM
How do you run a transaction log backup using a parameter file?
The following expressmaint command runs in less than one second and seems to do nothing, no error and not much in the report.

<Command line>
sqlcmd -S .\SQLSERVER2012EXP -i"C:\ExpressMaint\SqlBackupTool.SQLSERVER\bin\SQLBackupByParam.sql" -v DB = "ALL_USERS" -v DBRETAINUNIT = "copies" -v DBRETAINVAL = "3"


<contents of file: SQLBackupByParam.sql>
exec expressmaint
@database = '$(DB)',
@optype = 'LOG',
@backupfldr = 'C:\ExpressMaint\SqlBackupTool.SQLSERVER\Backups',
@reportfldr = 'C:\ExpressMaint\SqlBackupTool.SQLSERVER\Reports',
@verify = 1,
@dbretainunit = '$(DBRETAINUNIT)',
@dbretainval = '$(DBRETAINVAL)',
@rptretainunit = 'copies',
@rptretainval = 7,
@report = 1

<contents of report file>
Expressmaint utility, Logged on to SQL Server [xxx\SQLSERVER2012EXP] as [XXX\xxx]
Starting backup on Apr 30 2013 10:29AM


[1] Delete Old Backup Files...
[2] Delete Old Report Files...
0 file(s) deleted.

Expressmaint processing finished at Apr 30 2013 10:29AM (Return Code : 0)
Apr 30, 2013 at 6:52 PM
Note: Db's must not be using simple recovery model.

Modify the parameter SQL helper file and change the operation type from “DB” to a variable called $(OPTYPE) and just add a parameter to the command line argument.

<command line>
sqlcmd -S .\SQLSERVER2012EXP -i"C:\ExpressMaint\SqlBackupTool.SQLSERVER\bin\SQLBackupByParam.sql" -v DB = "ALL_USERS" -v OPTYPE = "LOG" -v DBRETAINUNIT = "copies" -v DBRETAINVAL = "3"

<file contents of “SQLBackupByParam.sql”>
exec expressmaint
@database = '$(DB)',
@optype = '$(OPTYPE)',
@backupfldr = 'C:\ExpressMaint\SqlBackupTool.SQLSERVER\Backups',
@reportfldr = 'C:\ExpressMaint\SqlBackupTool.SQLSERVER\Reports',
@verify = 1,
@dbretainunit = '$(DBRETAINUNIT)',
@dbretainval = '$(DBRETAINVAL)',
@rptretainunit = 'copies',
@rptretainval = 7,
@report = 1