Setting up a Maintenance Plan for SQL Express 2014

Launch the SQL Server Management Studio from the start menu. 

Login to the SQL Server as the SA User.

The Database we will be working with VSDatabase, we can scroll down and click Server Objects then click Backup Devices.

Right click Backup Devices and choose New Backup Device.

Enter in the Backup Device name we typically names these after the database. Since we will be creating two devices lets use VSDatabase_a.

The file location defaults to the instance installation under the BACKUP folder.

Repeat the process for VSDatabase_b.

Open Notepad and enter in the following line of code.
Set LogName=%date:~4,2%%date:~7,2%%date:~10,4%


This sets the Variable LogName equal to today's date. In some cases if the date of the system (Europe) uses the "/" then add this line of code on the next separate line.

Set LogName=%LogName:/=-%

Next we will set the path to the log directory in SQL. This will depend on the version you are running.
//SQL 2008 R2
cd "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\"

//SQL 2012 
cd "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log"

//SQL 2014 

cd "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log"

You can see in the example its ok to have them all as long as you comment the non valid options out. Two backslashes will make the line a comment and not run. 

Now we can create the Int-Opt file options. 

sqlcmd -E -S localhost -Q "DBCC CHECKDB(VSDatabase)"  >> %LogName%_CHECKDB.txt

sqlcmd -E -S localhost -Q "DBCC CHECKALLOC(VSDatabase)" >> %LogName%_CHECKALLOC.txt


sqlcmd -E -S localhost -Q "DBCC UPDATEUSAGE(VSDatabase)" >> %LogName%_UPDATE.txt

 Now save the file somewhere on the C drive or on the desktop for use later. We call this first file INT-OPT.bat 

(be sure show externtions for files is on so this does not get named .bat.txt)

You can reuse the same file and remove the INT-OPT code and add the DB backup code. 

sqlcmd -E -S localhost -Q "BACKUP DATABASE VSDatabase TO DISK = 'VSDatabase_a.BAK' WITH INIT" >> %LogName%_BACKUP.TXT

Save this file in the same location as INT-OPT.bat but name it VSDaatbase_A.bat

You can reuse the same file and remove the _A backup device and change this to the _B backup device. 

sqlcmd -E -S localhost -Q "BACKUP DATABASE VSDatabase TO DISK = 'VSDatabase_b.BAK' WITH INIT" >> %LogName%_BACKUP.TXT

Save this file in the same location as INT-OPT.bat but name it VSDaatbase_B.bat

Now that we have the .BAT files created and saved we can schedule them in the Task Scheduler to run as needed. 

Just right click the window and Choose "Create Basic Task"

We will call the first job INT-OPT since that is what it will run. 

This is a weekly Job.

It will run Sundays at 10:00 PM. 

Choose Start a Program and click Next to proceed.

Enter the path to the INT-OPT Batch file or click Browse. When its found click Next to Proceed. 

When you get to the next step Check the box labeled Open the Properties dialog when I click finish, and then Click Finish.

Select the Radio Button "Run whether user is logged in or not, and also Select Run with Highest privilege. Once those are selected Click OK to proceed. 

Enter in the admin username and password and click ok.

We start the process again this time naming the job VSDatabase_A.

This time this is a Daily Job. 

The job will run at 11:00 PM and it will occur every 2 days starting today. 

Choose Start a Program and click Next to proceed.

Enter the path to the VSDatabase_a Batch file or click Browse. When its found click Next to Proceed. 

When you get to the next step Check the box labeled Open the Properties dialog when I click finish, and then Click Finish.

Select the Radio Button "Run whether user is logged in or not, and also Select Run with Highest privilege. Once those are selected Click OK to proceed. 

Once you click OK - Enter in the admin username and password and click ok.

We start the process again this time naming the job VSDatabase_B.

This time this is a Daily Job. 

The job will run at 11:00 PM and it will occur every 2 days starting tomorrow.

This is important, this must start with tomorrows date to be sure the two jobs run on different days!  

Choose Start a Program and click Next to proceed.

Enter the path to the VSDatabase_b Batch file or click Browse. When its found click Next to Proceed. 

When you get to the next step Check the box labeled Open the Properties dialog when I click finish, and then Click Finish.

Select the Radio Button "Run whether user is logged in or not, and also Select Run with Highest privilege. Once those are selected Click OK to proceed. 

Once you click OK - Enter in the admin username and password and click ok.

You should run the jobs and also check the "LOG" location in the SQL directory to check for any errors. 

You can download the demo text file here.