You are here: Home > SQL Agent > Tokens
Archive | Tokens RSS feed for this section

SQL 2008 Agent Jobs – Tokens work in PowerShell!

by on December 2, 2009

The Joy of Tokens and PoSH

I have been working away building out servers in our new prod test environment, and automating as much as possible along the way with PowerShell. I  have to say that it’s been really fun and PoSH has brought back that loving feeling that I always had for Perl.  If a programming language can be friendly, PowerShell manages it. 

One thing I had the chance to test out yesterday was using SQL Agent tokens in PowerShell type SQL Agent steps. I am very happy to find that the tokens work just like I was  hoping they would. In other words, this works:

$instanceName = "$(ESCAPE_SQUOTE(SRVR))"
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

The SRVR token will return the connection name of the sql server with the instance name. This works on standalone and named instances, and for clustered instances you get netname\instancename.  So this is perfect for creating a sql server object and connecting to it.

Why would you want to do this? Why not just use TSQL?

The primary reason I currently have to use a PoSH step is to execute commands against the operating system. In this case, I needed to connect to my sql  instance and get some information about it to then use in the OS level commands I needed to run. I  have a specific example of this I’ll post in the coming days.

How Do I Test This Out?

If you’re new to PowerShell, find yourself a test box to play around on. It needs to have PowerShell and SQL 2005 or 2008 (the client tools/SSMS are enough, they provide the SMO assemblies). Open a PowerShell command prompt as administrator. To get yourself connected to a SQL Server Instance, it’s as simple as this:

#This loads up the SMO module which will be used to connect to SQL
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instanceName = 'NETNAME\INSTANCE'; # or just "COMPUTERNAME" for the default instance

#this passes in the name you used above to create a new object
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

#this will show you a table of all the properties of your SQL Server.
$srv.Properties | format-table -auto

#this will show you a table of all the properties of tempdb on your SQL Server.
$srv.Databases['tempdb'].Properties | format-table -auto

More Information

Comments { 0 }

Backups – Using SQL Agent Tokens to Set the Date, and Why to go MultiFile

by on June 1, 2009

My life is a bit easier since I learned how to use SQL agent tokens. They are particularly nice for setting date and timestamps on backup files. Unfortunately they only work in the context of executing agent jobs.

We had some backups at work which were running quite slowly, and my colleague Gina reminded me of something I once learned, but had somehow forgotten: backups streamed to multiple files are much faster than those written to single files. This is true even when the files are sitting on the same drive.

We’ve switched over the large backups from the existing process, where they wrote a single file to local disk and then robocopied out to a remote location, to the new process where the backup is streamed directly across the network to the backup location using multiple files. The total job runtime is always lower, and in most cases the time running the backup step itself is slower. I am finding that even when the backup process time itself is slightly longer, I perfer this setup because we don’t need to execute batch files stored on the operating system (with its related security worries), nor do we have to worry about allowing memory space for the copy process to run in outside of sql server. (Along with everything else that uses up that memory space.)

Here is a sample of the modified backup commands we’re using— this one is from an 11GB SQL 2008 database where I’m using five files for the compressed backup. The total backup size across the file files is 5.5GB and it completes across the network (within a datacenter) in ~10 minutes.

BACKUP DATABASE MyDB
	TO DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_1of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_2of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_3of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_4of5.DMP'
	, DISK='\\BackupServer\MyBackupDirectoryShare\MyDB_db_$(ESCAPE_SQUOTE(DATE))$(ESCAPE_SQUOTE(TIME))_5of5.DMP'
WITH COMPRESSION
Comments { 0 }