category:

MS SQL Server backup & maintenance using Expressmaint
Expressmaint utility is a very handy tool to backup and do different maintenance activities for SQL server 2005/2008 databases. You can perform various kind of administrative jobs in your SQL server database without having a in depth knowledge of SQL server maintenance facilities available with it. The jobs that can be done using this small but powerful utility are:
  • Full Database Backup
  • Differential Database Backup
  • Log Backup
  • Housekeeping of backup files
  • Database Integrity Checks
  • Database Index Rebuilds
  • Database Index Reorganization
  • Database Statistics Update
  • Report Creation
The Expressmaint Utility can be downloaded from the below mentioned links: ExpressMaint utility for SQL 2005: http://www.sqldbatips.com/samples/code/ExpressMaint.zip ExpressMaint utility for SQL 2008: http://www.sqldbatips.com/samples/code/ExpressMaint2008.zip

Example Syntax

Note that the entire command should be on one line even though some examples span multiple lines for formatting purposes 1) Full Database Backup of all user databases to c:backups, verify the backups and report to c:reports keeping backups for 1 day and reports for 1 week and continue processing other databases if an error is encountered baking up a database
expressmaint -S (local) -D ALL_USER -T DB -R c:reports -RU WEEKS -RV 1 -B c:backups -BU DAYS -BV 1 -V -C
2) Full Database Backup of all system databases to c:backups, verify the backups and report to c:reports keeping backups for 1 week and reports for 1 week
expressmaint -S (local) -D ALL_SYSTEM -T DB -R c:reports -RU WEEKS -RV 1 -B c:backups -BU DAYS -BV 1 -V
3) Log Backup of all user databases to c:backups, don’t verify the backups and report to c:reports keeping backups for 1 day and reports for 1  day
expressmaint -S (local) -D ALL_USER -T LOG -R c:reports -RU DAYS -RV 1 -B c:backups -BU DAYS -BV 1
4) Check the integrity of the AdventureWorks database and report to c:reports keeping reports for 1 week
expressmaint -S (local) -D AdventureWorks -T CHECKDB -R c:reports -RU WEEKS -RV 1
5) Rebuild all indexes in the AdventureWorks database and report to c:reports keeping reports for 1 day
expressmaint -S (local) -D AdventureWorks -T REINDEX -R c:reports -RU DAYS -RV 1
6) Update all statistics with fullscan in the AdventureWorks database and report to c:reports keeping reports for 1 day
expressmaint -S (local) -D AdventureWorks -T STATSFULL -R c:reports -RU DAYS -RV 1
Automating backups using ExpressMaint Since SQL Server 2005 Express Edition does not include SQL Agent, we need to rely on the Windows Task Scheduler to run our maintenance tasks. If you are not familiar with how to set up a scheduled task, it’s worth reviewing the Microsoft Knowledge Base article below How to Schedule Tasks in Windows XP
  • Double-click Add Scheduled Task to start the Scheduled Task Wizard, and then click Next in the first dialog box
  • Click Browse, browse to Expressmaint.exe, and then click Open.
  • Type a name for the task e.g DAILY FULL BACKUP and then choose Daily from the scheduling options
  • Click Next, specify the information about the time to run the task e.g. 00:00, and then click Next
  • Type the name and password of the account that will execute this task. Make sure that you choose an account that is a syadmin for your instance
  • Click Next, select the checkbox to Open the Advanced Properties for this task and then click Finish
  • In the Run text box append the arguments you want to pass to the ExpressMaint utility. (You must leave a space after the existing contents)
  • Click OK. If prompted, supply the password for the account again
[ratings]
Share
Comments (4)

4 responses to “MS SQL Server backup & maintenance using Expressmaint”

  1. tv amr says:

    This post gets to be a thumbs in place from me.

  2. Whats up, I assumed that you are able to help me. I have a MSSQL .ldf and .mdf file that I have to restore. Any ideas on how to achieve this? Cheers

  3. Pierre says:

    Hello,

    I’ve got a well known issue 🙂
    The SQL backup is run by the networkservice (as is SQL Server Express), which gets access denied writing the backup to a network share. And the C-drive runs full because the file must be present for ExpressMaint to delete old versions. I do: robocopy C-drive-location Networkshare /purge

    Would it be possible to get an option? f.ex. -LB (Local Backup), that writes the backup locally, where networkservice is allowed to write, and then copied to the -B location with the rights of the user, who executes ExpressMaint, that has rights to write on the network share? That would be nice.

    Best regards,
    Pierre

  4. jayabrata says:

    Hi Pierre,

    You may run the scheduled task under System or even Administrator account. In those cases it will run with greater privileges. Any way if you run the task under Administrator account, then you will have to save the password with the task too. You may use the “NET use” command to ensure that the network drive is always mounted before files are copied onto them.

Leave a Reply

Your email address will not be published. Required fields are marked *

86 + = 93

Related:

Stay Updated

Please enter your details below to get
A Free Trial
x + x* =