Ava PA Web

In most circumstances Ava is the primary repository of business knowledge for either temp agency staffing or staff bank activity. It is the core of our customers' profitability and continued existence. It's at the heart of the reasons why to choose Ava. Over the years Ava has consistently had the outstanding record for reliability and security. Accurately measured in a large and complex environments Ava has continuously out performed in the 98% of other supplied software irrespective of size and complexity and matched the remaining product.
Allied to Ava's  security and reliability is the process of backing up data.

In the smaller scale environments (SQL 2008 Express)

This is probably best done with the free application SQLBackupAndFTP. You should also have an email account set up so that notifications can be sent to you.

In the larger scale environments (SQL 2008 Standard, SBS etc)

Where a bespoke database administrator post is overkill and an unnecessary overhead: Ava provides advice on backup strategy. This article is part of that advice.
Setting up backups and maintenance plans is essential for data security and continued high performance. This process is different in MS SQL 2008 having evolved over previous versions of MS SQL. This article takes the IT savvy user simply through the multistep process of setting up the basic maintenance plans and notifications necessary in a well run system.

Before you start:

Ensure that you are up to date with SQL2008 service packs. There was an issue which concerned the following setup procedure which was resolved in a very early patch to the RTM version of SQL 2008.The patch is included in SQL 2008 SP1. Ava recommend that if you are applying SP1, you also apply the latest cumulative update for SP1 namely CU3 SP1 CU3
Create a folder on a physically separate drive (from the database and log files) mounted on your server. This is for your backup files. You don't really need to worry about fragmentation of this drive (to a point). It should not be compressed (this will slow the backup process down). It must have sufficient free capacity for all your (Ava / SQL) backups.
It is certain that if the extremely rare, serious error, occurs: you want to know about it as soon  as possible.  SQL 2008 standard edition (upwards) allows for database mail to send email notifications to your (IT) network support or other designated persons. In all probability a problem of this nature with MS SQL will actually be caused by a problem with your network or will be network related. It makes sense for all these error messages to go to one person or place.
(Note:  The Ava application has an entirely separate process which is designed to give us specific information to help develop our software and refine new features.)
The following information is required for the database email system:

To set this up:
Log in to SQL server with SQL Server Management Studio (SSMS)
Expand the management node in the top left pane and right mouse click on 'Database mail'

A wizard will be launched. Click next.

If this is the first time, you will be asked if you want to enable database mail. Seems redundant to say this is a major part of the object of the exercise!

You will now get the opportunity to create an email profile for any messages you need.  I would suggest that you might have two profiles, one for the "oh F$%!!$%" messages which goes straight to a VIP's mobile and one for system ok notifications which probably goes to a mail box where it probably isn't touched, but may be useful at some time in the future for finding the date/ time when a process notified of successful completion.
We are interested in the "oh F$%!!$%" messages here. The profile should be labelled appropriately. For your information "oh F$%!!$%" is not a registered trade mark of Ava and may be used as an account name / description  without copyright referral.

Now enter the smtp account details

click OK

Now we can get started on a Maintenance plan.

Once again:
If not logged in, log in to SQL server with SQL Server Management Studio (SSMS)
If not expanded, expand the management node in the top left pane and right mouse click on 'Maintenance Plans'

A wizard will be launched. Click next.

You now get the opportunity to label you plan: you could call it "I have a cunning plan 1", but better something like "DatabseName(s) full backup".  Seems redundant, but a comment like "after office close" could be helpful (especially if office hours change).

Then click on the [change] button: this process needs to be scheduled to actually occur at some (repeated) point in a day.

The default schedule is not Daily. Unless you have a system where there is not sufficient time to schedule a daily full backup (in which case I'd be worried if you were reading this for anything but the jokes!) do it daily backup.
By default it will happen at midnight. There might be a better time, especially if some tape backup kicks off before midnight. Tape backup should occur after database backup completes allowing the backup to be quickly copied to an alternate repository.

Click ok and you will be returned to the main wizard work flow.

It will now show a description of the schedule.

Click next.
You now get a choice of a bunch of tasks.

If your database is in full recovery mode, then you will need to do a separate maintenance wizard run for log backup. So repeat this wizard process but use a frequency of (say) every 5 minutes. If it is in simple recovery mode: this additional (log backup) step is redundant.
You should also do a maintenance wizard run for clean up tasks, such as deleting old backups to reclaim disk space.
You should also have tasks to check database integrity (this can and should be done prior to backup as a database which does not pass database consistency checks (dbcc) can backup (good) but the backup may not restore (very very bad and it renders the backup process almost useless).
Restoring a backup which errors in the restore process and fails to complete.
The next window allows you to re-order tasks if they are run on the same schedule. This may happen if you've done this wizard for multiple tasks run on a single schedule. It is the default option and way back earlier in the article we showed it as the option.
Here we are concerned with the first run for the basic backup task.

Choose the database to back-up. There is no point in backing up 'tempdb' (this is recreated every time the server restarts). Back up 'master' , 'msdb' and 'model'. It should be pointed out that these databases only change under specific circumstances. If this instance of SQL server is just running the Ava software, then a single backup up 'model' is sufficient as Ava does not implement the functionality of 'model' because of possible conflict with other applications which may rely on the 'model' database. For more information on the purpose of these databases this article was written for SQL7 and its still totally relevant today.

Also you must choose the location of the backup databases. Raid 0 is a good choice for this drive.

What is raid?
http://en.wikipedia.org/wiki/RAID
http://en.wikipedia.org/wiki/Standard_RAID_levels
The location should be mounted on the server.

You can backup to a unc path, however this will probably hammer the network and in addition if the backup path then fails, could have a substantial continued negative effect on the SQL service.  In addition the SQL service must be configured to have network access and this may give rise to a security issue.
It is best to have a physically separate local hard drive. Please note, drives are really cheap.  A 1 Tera Byte drive can cost less than £100. Put it another way 10p per GB or on a 4GB database backup: 40p (and this is reusable).  Putting the backup on a drive which is a logical split of the database files drive (or array of drives) is a very false economy. If the data drive goes in this situation then so does the backup. That defeats the whole point of having a backup.
Ideally the drive should be on a separate disk controller (or controller channel) this will minimise any impact on database performance if it becomes necessary to backup (for some reason) whist normal operations are in progress.
The disk backup can then go to tape or other in a leisurely fashion.

Its always good to keep a report of these things and its always wise to clean out reports that are simply too old to be useful - so a cleanup task for reports (later).

Click next

Finish (first time anyway)

Check list:

Have I set up maintenance plans for:

Database dbcc check
Database backup
Log backup
Update stats
Reorganise indexes
Rebuild indexes

cleanup task for database backup
cleanup task for log backup
cleanup task for reports
cleanup history