By: Norman Chan | Last Updated: 2013-05-13 | Comments (5) | Express Edition
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.
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
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)
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
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
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.
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.
Add group SQLServerSSQLUser$EXPSQL01$MSSQLServer to the folder permissions with read and write access to C:\Backups.
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:
We will customize our script based on information we've gathered:
Our modified script will be used as part of our SQL Agent job step when creating our scheduled 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
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
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.
Click OK to save the Job Step.
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/
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.
- Learn more about the SQLCMD Utility from Microsoft
- Learn more about SQL Server Proxy Accounts
- Learn more about Backups
- MSSQLTIPS has a several articles as a starting point: http://www.mssqltips.com/sqlservertutorial/6/types-of-sql-server-backups
- Microsoft's documentation on Backup is available here: http://msdn.microsoft.com/en-us/library/ms186865.aspx
Last Updated: 2013-05-13
About the author
View all my tips