Configuration of ESTIMATION databases

The 3 scheduled tasks

At installation, we create 3 scheduled tasks to perform maintenance and backup of the databases. This allows the customer to tailor this maintenance to meet their specific needs.

Database backup

We offer the customer the possibility to back up according to 2 recovery models: the Simple recovery model and the Full recovery model. The customer can choose either one to meet his needs.

Simple recovery model

It allows you to restore the database at a point planned in advance.  For example, if I make a backup every morning at 7:00 am, I will be able to restore my database at these precise moments when I made a backup.

The program executed to meet this need is "ExecMaintenance.cmd" with the parameter "ExpertisaDailyBackup.sql"

Edit the file to fit your needs:

@Databases = 'Expertisa%', -- This variable indicates the databases to be backed up.  In this case, we save all the databases starting with Expertisa.  We could also write 'Expertisa, Expertisa_Config' to backup only these 2 databases.@BackupType = 'FULL', - This indicates that we are doing a full backup - @Verify = 'Y', - Verify the integrity of the backup after the operation.@LogToTable = 'Y', -- Write a log of the operations performed in the database.@CleanupTime = 360 -- How long (in hours) to keep the backups in the backup directory before deleting them. (Automatic rotation of the backups)--,@Directory = 'C:\Backup' - If you want to save in another directory than the default one, you can indicate another path.  This parameter is not mandatory.  

-- You can also save to multiple locations.  Here is how the syntax would be: @Directory = '\Server1\Backup, \Server2\Backup, C:\Backup'

Full recovery model

This model allows you to restore the database at any moment in time.

In order to do this, you need to run "ExecMaintenance.cmd" with the parameter "ExpertisaDailyBackup.sql" and you also need to run "ExecMaintenance.cmd" with the parameter "ExpertisaHourlyBackup.sql"

This has the effect of saving the "Transaction Logs" (by default, every hour), which allow restoring the DB at any point in time.

The ExpertisaHourlyBackup.sql file includes these parameters:@Databases = 'Expertisa%', -- Database to be taken as a backup.  Note that the database must be in full recovery mode for this backup to take place, -- See other comments in the simple recovery
@BackupType = 'LOG', - This indicates that only Transaction Logs are backed up@LogToTable = 'Y', -- Write a log of the operations performed in the DB.@CleanupTime = 360 -- How long (in hours) to keep the backups in the backup directory before erasing them. (Automatic rotation of the backups)-- ,@Directory = 'C:\Backup' -- See comments in the simple recovery

The maintenance system

A task is also created to do maintenance on the databases.  More precisely: 1-The indexes are recompiled, 2- the SQL server plans are redone to optimize the queries.

To do this, you have to run "ExecMaintenance.cmd" with the parameter "ExpertisaIndexAndStatsMaintenance.sql".