One of the challenges of using the free express editions of SQL Server is the lack of the SQL Agent.This means that creating a functioning maintenance plan is a bit more of a challenge then on a regular SQL Server instance. Luckily for us Ola Hallengren created his maintenance plan in such a way that it can easily be altered to make it usable for SQL express editions.Ok let’s get started. Creating the repository databaseFirst things first, start with downloading Ola’s excellent maintenance scripts from his website, this ensures that you have the latest and greatest version. In this post I won’t explain all the parameters as Ola has an excellent wiki for all hisPersonally I try to limit the creation of custom objects into the system databases, so I prefer creating a smaller database to contain all my DBA related objects. In this documentation we’ll use the name DBAExpress for this database.Minor changes to Ola’s scriptChanging the database from master to DBAExpressChanging default behaviorIt seems illogical to create the jobs as the agent is not running on a SQL Express, but I have a very good reasons to do so. It indeed doesn’t create any jobs on a SQL Express, but it doesn’t throw any errors neither so we can use this script on any SQL Edition ScriptsLuckily for us Ola’s scripts are designed to run command line, so we’ll us the example scripts provided in his documentation to create 5 bat files. For this purpose, I have created the folder D:MSSQLMaintenanceplan where we can store these files.
Nov 06, 2013 Hello all, I have about 3 very small (SQL Express 2012 databases being used by websites on a VPS server. I am currently using a batch script to backup other data on the server, compress it, then SFTP it to an off-site storage location. SQL Server Express 2008 supports database backups. It's missing SQL Agent, which allows to schedule backups, and the maintenance plan wizard for creating a backup tasks. You can backup databases in two different ways: Use Microsoft SQL Server Management Studio Express which has the Backup option on the right click menu for each database under.
![Sql Sql](/uploads/1/2/4/1/124198297/335650650.png)
![Sql express schedule backup Sql express schedule backup](http://www.pdoteam.com/wp-content/uploads/2012/01/image10.png)
For Logging we create the D:MSSQLMaintenanceplanLog folderBackupsFirst things first, as a DBA backups is our main concern, so I’ll start with that. Please note that I’m against storing the backups on the same drive as the database files, but in this case I have no other choice. This directory will be backed up by using robocopy backups from that drive to a safe location.Full BackupsDiff BackupsLog BackupsCheating with compressionAs an express edition doesn’t support backup compression and these servers tend to be rather thin provisioned it can be useful to compress the backup folder by using NTFS compression. As SQL Express database are limited to 10GB, and are seldom that big you could do this to save space. Don’t forget to turn it off once you go to a higher edition of SQL though, because you’ll hit the limit of NTFS compression quite fast on higher edition of SQL Server.Index RebuildsSecondary let’s script our index and statistics maintenance, as this is an express edition I won’t bother splitting it up between indexes and statistics.
Also we don’t bother including the online options as they are only supported in Enterprise editions.Database IntegrityAnd then last but certainly not least, our database integrity checks.