Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Centralizing SQL Server Express Job Scheduling


By:   |   Last Updated: 2013-05-13   |   Comments (5)   |   Related Tips: More > 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.

Diagram Schedule Server

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

Add Login

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

Create Login Sched Acct

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

Select sysAdmin

Step 3

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

Create 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.

Add Credential info

Step 4

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

Add 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.

Add Proxy Info

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

Right Click New Login

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

Add New Login SQLExpress

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 )

Add Backup Operator

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

Backup Folder

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.

Net Command

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"

Step 1

Go to SQL Agent -> Jobs -> right-click New to create our SQLJob.

The Owner field must be the Windows Login we created from the security section MYDOMAIN\SQLExpressSchedSvc

Create SQL Job

Step 2

Click on Steps -> click New (near bottom) to create a new Job Step

Change the drop down "Run as" to use SQLExpressBackupProxy created in the Security Section.

Paste into the Command Box the Custom SQLCMD script 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"

Add Job Step

Click OK to save the Job Step.

Step 3

Create schedule for the newly created Job by clicking Schedules on the left window -> click New then select the best schedule for our SQL Agent backup Job. Please review this tip for more Job Agent Management: http://www.mssqltips.com/sqlservertip/2139/sql-server-agent-job-management/


CONCLUSION

After going through these steps we are now able to schedule SQL Server Express backups using a SQL Agent running on another SQL server. This tip demonstrates scheduling a database local backup to a SQL Server Express Database. By following these steps we can backup multiple databases on a SQL Server Express machine. In addition, we can further expand on the capabilities of scheduling other tasks on a SQL Server Express by changing the T-SQL in the template in the SQLCMD section to perform other SQL tasks.

Next Steps


Last Updated: 2013-05-13


next webcast button


next tip button



About the author
MSSQLTips author Norman Chan Norman Chan is a SQL Server DBA with over 12 years of IT experience as a software developer and DBA.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, September 08, 2013 - 5:50:14 AM - Prasad Back To Top

Ultimate post!! Keep up the good work 


Friday, May 24, 2013 - 6:19:13 AM - Pradip Back To Top

Thank you very much to  explain about sql agent job I understand now


Tuesday, May 21, 2013 - 9:21:20 AM - Mario Back To Top

Another alternative would be to create a linked server to the Express instance and kick off a stored procedure located on the Express instance that runs the backups and any other tasks. Not sure whether it is more secure, but it sure does simplify things a but. Also a lot less manual configuration once you've automated the process.


Thursday, May 16, 2013 - 2:34:20 PM - Sudhakar Back To Top

Is it more complex designing SSIS package to perform backup and then schedule as job in the source environment in which agent is avaiable ?

Thanks.


Monday, May 13, 2013 - 10:21:36 PM - Tim Cullen Back To Top

Very cool tip!


Learn more about SQL Server tools