June 6, 2011 at 8:08 am
To the PS Gurus I am very new to Powershell and I have a powershell script that I sort of have working but not sure if it is the best way to do this. I am able to run the back up using the following syntax
.\TDPSQLBackup.ps1 -itype FULL -iSQLServerInstance <Servername\InstanceName> -iDatabase <DBName>
SCRIPT
=======
# Copyright (C) 2011 Aegon
#
# tdpsqlbackup.bat - Backup all\Selected databases of a local SQL Server
# instance using Tivoli Data Protection for SQL.
#
#Usage:
#tdpsqlbackup.bat full <sqlserverinstance> <database> [<dsmoptfilename>]
#tdpsqlbackup.bat diff <sqlserverinstance> <database> [<dsmoptfilename>]
#tdpsqlbackup.bat log <sqlserverinstance> <database> [<dsmoptfilename>]
# INSTALLATION
# ============
#
# Copy this script into the V:\mssql$<INSTANCE>\Scripts\ directory. The user running the script
# (i.e. the account SQL Server Agent is running under) needs to have
# write and create permissions in that directory. This script needs
# to be installed on each node when deployed in a cluster.
#
# Then create SQL Server Agent jobs that call this script. The first
# three parameters, backup type, server name & database are mandatory but dsmoptfilename is optional. You
# usually need to specify the value of @@ServerName as server instance
# name parameter.
#
#
# RATIONALE
# =========
#
# The TDPSQL command line client can be told to make full and
# differential backups of all databases at once using the asterisk
# ("*") as database specification.
#
# This doesn't work with transaction log backups, because the program
# then attempts to make log backups even of databases that are in
# simple recovery mode, which fails, of course. This script gets a
# list of databases that need transaction log backups by querying the
# server first and then performs the backup only for them.
#
################################################################################################
param([string]$itype=$( `
Throw "Parameter missing: -type FULL|DIFF|LOG"),
[string]$iSQLServerInstance=$( `
Throw "Parameter missing: -SQLServerInstance SQLServerInstance"),
[string]$iDatabase=$( `
Throw "Parameter missing: -Database ALL|SYSTEM|DBName"),
[string]$idsmoptfilename)
echo $itype, $iSQLServerInstance, $iDatabase, $idsmoptfilename
################################################################################################
#Declare Variables
################################################################################################
new-variable scriptdir
new-variable script
new-variable logdir
new-variable logfile
new-variable tdpsqldir
new-variable sqlserverinstance
new-variable instance
new-variable action
new-variable exclude
new-variable parameter
################################################################################################
#Set Variables
################################################################################################
set-variable scriptdir 'V:\mssql`$SQLDBA_TST01\Scripts\'
set-variable script 'TDPSQLBackup.ps1'
set-variable logdir V:\mssql`$SQLDBA_TST01\log\DBBackLogs\
set-variable logfile "tdpforsqlimran.txt"
set-variable exclude tempdb
$sqlserverinstance = $iSQLServerInstance
# Set defaults for TDP parameters
set-variable tdpsqldir 'C:\Progra~1\Tivoli\TSM\TDPSql\'
# $tdpsqlexe = "C:\Program Files\Tivoli\TSM\TDPSql\tdpsqlc"
$tdpsqlexe = "C:\Progra~1\Tivoli\TSM\TDPSql\tdpsqlc.exe"
################################################################################################
#Functions
################################################################################################
Function Usage()
{
echo 'ERROR - Usage: full|diff|log sqlserverinstance SYSTEM|ALL|DBName'
RETURN -5
}
################################################################################################
#Check Input Parameters
################################################################################################
if ($sqlserverinstance -eq $null)
{
Usage
}
else
{
$instance = split-path $sqlserverinstance -Leaf
}
if ($dsmoptfilename -eq $null)
{
if ($instance -eq $sqlserverinstance)
{
$dsmoptfilename = $tdpsqldir + 'dsm.opt'
}
else
{
$dsmoptfilename = $tdpsqldir + $instance + '_dsm.opt'
}
if (!(Test-Path $dsmoptfilename))
{
echo $dsmoptfilename ' does not exist'
exit
}
}
$typelist = "FULL", "DIFF", "LOG"
if (!($typelist -contains $itype))
{
Usage
exit
}
else
{
$type = $itype
}
if ($type -eq "FULL") {$action = "FULL"}
elseif ($type -eq "DIFF") {$action = "DIFFULL"}
elseif ($type -eq "LOG") {$action = "LOG"}
else {
Usage
exit
}
if ($iDatabase -eq "SYSTEM")
{
$parameter = "/sqlserver=TCP:$sqlserverinstance /SQLAUTH=INT /TSMOPTFile=$dsmoptfilename /EXCLUDEDB=$exclude"
& "$tdpsqlexe backup master $action $parameter /LOGFILE=$logdir$logfile"
& "$tdpsqlexe backup msdb $action $parameter /LOGFILE=$logdir$logfile"
& "$tdpsqlexe backup model $action $parameter /LOGFILE=$logdir$logfile"
}
elseif ($iDatabase -eq "ALL")
{
$parameter = /sqlserver=TCP:$sqlserverinstance /SQLAUTH=INT /TSMOPTFile=$dsmoptfilename /EXCLUDEDB=$exclude
& "$tdpsqlexe backup * $action $parameter /LOGFILE=$logdir$logfile"
}
else {
#$parameter = "/sqlserver=TCP:" + $sqlserverinstance + " /SQLAUTH=INT /TSMOPTFile=" + $dsmoptfilename + " /EXCLUDEDB=" + $exclude
# echo $parameter >> imrantest.txt
#$cmd = "C:\Progra~1\Tivoli\TSM\TDPSql\tdpsqlc.exe backup " + $idatabase + " " + $action + " " + $parameter + " /LOGFILE=" + $logdir + $logfile + ""
#echo $cmd #>> imrantest.txt
# echo $cmd
#& $cmd >> imrantest2.txt
#backup master full /sqlserver=TCP:ukdbsqltst01\sqldba_tst01 /SQLAUTH=INT /TSMOPTFile='C:\Progra~1\Tivoli\TSM\TDPSql\SQLDBA_TST01_dsm.opt' /EXCLUDEDB=tempdb /LOGFILE='V:\mssql$SQLDBA_TST01\log\DBBackLogs\imrantest.txt'
# & 'C:\Progra~1\Tivoli\TSM\TDPSql\tdpsqlc.exe' $cmd
# & $tdpsqlexe backup $idatabase $action $parameter /LOGFILE= $logdir $logfile >> imrantest.txt
# & $tdpsqlexe backup $idatabase $action $parameter /LOGFILE=$logdir$logfile >> imrantest.txt
$cmd = $tdpsqlexe + " backup " + $idatabase + " " + $action + " /sqlserver=TCP:" + $sqlserverinstance + " /SQLAUTH=INT /TSMOPTFILE=" + $dsmoptfilename + " /EXCLUDEDB=" + $exclude + " /LOGFILE=" + $LOGDIR + $LOGFILE
Invoke-Expression $cmd >> imrantest3.txt
}
#echo $scriptdir $script $logdir $logfile $type $sqlserverinstance, $instance $iDatabase $dsmoptfilename $tdpsqldir $type $action $exclude
Additionally, when I try to import this into a SQL Server job it still runs to success even though the script may have failed. eg if I don't enter the -iDatabase parameter.
June 6, 2011 at 9:31 am
When I save just the top portion of your script as a new PS script:
param(
[string]$itype=$(Throw "Parameter missing: -type FULL|DIFF|LOG"),
[string]$iSQLServerInstance=$(Throw "Parameter missing: -SQLServerInstance SQLServerInstance"),
[string]$iDatabase=$(Throw "Parameter missing: -Database ALL|SYSTEM|DBName"),
[string]$idsmoptfilename)
echo $itype, $iSQLServerInstance, $iDatabase, $idsmoptfilename
It works as expected through a SQL Agent (2008R2 Standard Edition) PowerShell job step type when I do not provide one of the required parameters.
Command line:
C:\@\Topic1120284-1351-1.ps1 -itype FULL -iSQLServerInstance .\STD2008R2
Output:
Date6/6/2011 9:29:06 AM
LogJob History (test PS)
Step ID1
Server.\STD2008R2
Job Nametest PS
Step Namestep 1
Duration00:00:05
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: .\SYSTEM. A job step received an error at line 44 in a PowerShell script. The corresponding line is ' [string]$iDatabase=$(Throw "Parameter missing: -Database ALL|SYSTEM|DBName"),'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Parameter missing: -Database ALL|SYSTEM|DBName '. Process Exit Code -1. The step failed.
Which step type are you using in your Agent Job? CmdExec? PowerShell?
Are there any other commands building your parameters before you try calling the PS script?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply