SpurData Blog

I've been playing with SQL Agent tokens more, researching how Ola Hallengren uses them in his famous Maintenance Solution, and how you can use them. Particularly I was looking for a way to specify the "ServerName" in the Output File path. You can do this using a token instead of having to customize the MaintenanceSolution.sql script for each server. This is helpful when you're doing environment takeovers and trying to get consistent backups going across several servers. Here is an example of our solution, which is working well. You would modify line 29 of Ola's script:   DECLARE @OutputFileDirectory nvarchar ( max ) = '\\MyBackupServer\DBBACKUPS\ $(ESCAPE_SQUOTE(SRVR)) \OUTPUT' Then for each job step, Ola will setup the following output file. This can be seen in the "Advanced" properties of a particular Job Step. '\\MyBackupServer\DBBACKUPS\$(ESCAPE_SQUOTE(SRVR))\OUTPUT\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DAT...

As of SQL Server 2012, SQL and other Microsoft services run on “virtual accounts” by default instead of Network Service. This is part of a defense-in-depth strategy whereby the service running SQL is isolated from other services on the box. The virtual accounts have names like “NT SERVICE\MSSQLSERVER” and “NT SERVICE\SQLSERVERAGENT”. These are local accounts and have pretty limited access to the network. When they do access the network, they present the credentials of the Server, using the server’s name in the form of “DOMAIN\ServerName$”, eg, “Contoso\FinanceServer$”.