Centralizing SQL Server Express Job Scheduling

By: Norman Chan | Comments (5) | Related: > Express Edition

Problem

We're running multiple versions of SQL Server (SQL Server 2005, 2008, 2012 and Express Editions) in our Windows Domain. I'm interested in monitoring all my SQL Server backups, but for SQL Server Express we're currently using scheduled backups using Windows Task scheduler on each SQL Server Express since SQL Agent does not come installed on Express Editions. Is there an alternative where I can utilize my existing SQL Server infrastructure? In this tip we will utilize SQL Agent on an existing SQL Server to help centralize SQL Express Backup Scheduling to replace Windows Task scheduler.

Solution

This solution will require a SQL Server which has SQL Server agent actively running, which we will name as the Scheduling SQL Server. In addition, proper security is required for communications between the Scheduling (SQL) Server and the SQL Server Express Server. This will allow the Scheduling Server to initiate a remote backup command to the SQL Express Server to back up a database on SQL Express Server's local backup directory instead of using Windows Task scheduler to backup to SQL Express Server's local backup directory.

In order to configure scheduled backups we will look at 3 areas:

Security: Configure Security for Servers
SQLCMD: Define parameters to use sqlcmd to execute a backup
SQL Job: Define a scheduled Job to initiate a script

Security

In order for this method to work we must have proper permissions between our Schedule (SQL) Server and our SQL Server Express Database. We will create a new Windows account as a general purpose account for SQL Express Backups that has permissions on both servers. We will also create a credential and proxy account on the Schedule Server for use with the SQL Agent Job.

We will need to gather some information:

Identify a SQL Server that will be used for scheduling (for example SQLSchedSVR)
Identify a SQL Server Express Server Database to backup. (for example: Server EXPSQL01 database DBA_TEST )
Determine a name that for a Windows Login account. (for example: MYDOMAIN\SQLExpressSchedSvc)
Determine a name for a credential, which is easy for us to identify (for example: CmdExecCreds)
Determine a name for a Proxy Account, which is easy for us to identify (for Example: SQLExpressBackupProxy)
Determine a name for a folder name for your SQL Server Express database backup. (C:\Backups)

Step 1

Request creation of a Windows Domain Account from your System Administrators to be used for SQL Server Express Backups. A new account specifically used for this purpose is recommended, as it's useful from a database management perspective and trouble-shooting. By having a specific account it will simplify diagnosing SQL Server Express backup issues instead of using an existing account in your infrastructure.

This will be a common account that will be added to all SQL Server Express servers for this backup strategy and also used on our Scheduling Server (SQLSchedSVR) to initiate a SQL Server backup.

Determine a name that is meaningful for the Windows Domain account Name, this tip will use MYDOMAIN\SQLExpressSchedSvc

Step 2

Add a new Login MYDOMAIN\SQLExpressSchedSvc to Schedule Server (SQLSchedSVR) by opening SQL Server Management Studio, navigating to Security -> Logins -> Right Click New Login

Enter Windows Login name created earlier. (for example: MYDOMAIN\SQLexpressSchedSvc)

Click Server Roles -> click check box to add sysadmin as a server role for account MYDOMAIN\SQLExpressSchedSvc

Step 3

Create a Credential in the Security section of the Schedule Server (SQLSchedSVR) going to Security-> Credentials-> right click New Credential...

A credential contains authentication information stored that's needed to use a resource outside of SQL Server. This information consists of a Windows login and password. Our outside resource requires the ability to execute a command at the operating system command prompt to back up a SQL Server Database. Our credential is used as part of creating a Proxy Account in the next step.

Determine a credential name that is meaningful and easy to identify for your organization. In this tip we are naming ours CmdExecCreds.

For the Identity and password sections, use the Windows Login account created earlier (Our exampleMYDOMAIN\SQLExpressSchedSvc).

Afterwards click OK to complete.

Step 4

Create a Proxy Account on the Schedule Server. Navigate to the SQL Agent-> Proxies-> right click New Proxy...

A SQL Agent proxy account acts as an intermediary (middle man) and runs under a specific security context for our SQL Job task to use the Operating System (CmdExec).

Enter desired name for the Proxy Name. (For Example: SQLExpressBackupProxy), then enter the credential name created in step 3 (For Example CmdExecCreds ) or you can browse for it by clicking the ellipsis.

Now we connect to the SQL Server Express and add MYDOMAIN\SQLExpressSchedSvc. Begin by navigating to the Security -> Login -> New Login...

Enter the Windows Login name (MYDOMAIN\SQLexpressSchedSvc) created earlier, which we are now also using for our SQL Server Express Server (EXPSQL01).

Then on the left navigation select "User Mapping" and add db_backupoperator Role for your SQL Express Database for backing up. (for Example: DBA_TESTDB )

If you already have an existing backup directory this step can be skipped, otherwise we can follow the instructions below.

We need to create a backup folder to allow the SQL Server Express Server to back up locally. Our backup folder will be C: \Backups on our SQL Server Express Server EXPSQL01

We will use the "SQLServerMSSQLUser" group found on the SQL Express Server (EXPSQL01) to configure SQL Server access to the backup folder C:\Backups To find the name of the group use the "NET"command from the command prompt on EXPSQL01.

At the Command Prompt type: net localgroup

This command will list all groups on EXPSQL01, the SQL Server Express Server. We will use the "SQLServerMSSQLUser"group shown below for configure security for C:\Backups.

Configure permissions from Windows Explorer-> Locate C:\Backups-> right-click Properties -> click Security Tab -> Click Edit Button.

Remove all users and groups (This may not be possible depending on your organizations configurations, removal can be skipped)

Add group SQLServerSSQLUser$EXPSQL01$MSSQLServer to the folder permissions with read and write access to C:\Backups.

SQLCMD

Before providing final directions on creating the SQL Agent Job it's important to familiarize ourselves with SQLCMD. It is a utility that allows us to execute T-SQL statements without SQL Server Studio Manager (SSMS). It lets us connect to a SQL Server typically to run T-SQL scripts or ad hoc SQL from the command prompt when SSMS can't be used. SQLCMD can be used in a Windows batch file, or can be incorporated into a SQL Job as part of Job Step.

For this tip we will incorporate SQLCMD as part of our SQL Agent Job to centralize scheduling backups of our SQL Server Express Database(s). For an example see this MSSQL Tip (http://www.mssqltips.com/sqlservertip/2478/connecting-to-sql-server-using-sqlcmd-utility/) on connecting to SQL Server using the SQLCMD utility.

This section we will build our T-SQL script, first we will need to gather some information:

Identify Machine with SQL Server Express For Example: EXPSQL01
Identify SQL Server Express Database for backup For Example: DBA_TESTDB
Determine SQL Server Express Drive path for Backup and file name for our backup: C:\backup\DBA_TestDB.bak
Determine backup name description for our Backup: TestDB_Full Database Backup

Our template will be:

sqlcmd -S {SQL EXPRESS SERVER} T -b -E -Q "BACKUP DATABASE{DATABASE NAME}TO DISK = N'{DRIVE PATH}' WITH NOFORMAT, INIT, NAME = N'{BackUpName-Description}', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM"

We will customize our script based on information we've gathered:

sqlcmd -S EXPSQL01 T -b -E -Q "BACKUP DATABASE [DBA_TESTDB]TO DISK = N'C:\Backup\DBA_TestDB.bak' WITH NOFORMAT,INIT, NAME = N'TestDB_Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS, CHECKSUM= 10"

Our modified script will be used as part of our SQL Agent job step when creating our scheduled job.

SQL Job

In order to automate the process and schedule backups we will use a SQL Agent Job. We will begin by creating a new job.

We will need to gather some information:

Use our Windows account created in the Security section MYDOMAIN\SQLExpressSchedSvc
Use our SQLAgent Proxy Account created in the Security section SQLExpressBackupProxy
Use our customized SQLCMD script created in the SQLCMD section

sqlcmd -S EXPSQL01 T -b -E -Q "BACKUP DATABASE [DBA_TESTDB] TO DISK = N'C:\Backup\DBA_TestDB.bak' WITH NOFORMAT, INIT, NAME = N'TestDB_Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM"