Backup Processes...Choosing the Right Option

Our clients often ask us the best process to use for backing up their data.  This important step is crucial to all our success and one that OGsys takes very seriously.

We recommend two options for backups, depending on your system and your budget.

Option 1 (Preferred)

Use a “SQL Aware” backup solution to automate the backup.  For example, Backup Exec (Symantec, nee Veritas) has a SQL Server option that will facilitate the backup of a ‘hot’ SQL Database as part of a backup job.  Most enterprise backup solutions will offer a similar option or module.  This is the simplest and most easy to configure.  It is also the most expensive if you don’t already have such a product.

The price was prohibitive enough for us that we use option #2 for our databases.

Option 2

Use SQL Server Agent to schedule nightly and weekly jobs to automate SQL Server’s creation of the database backup.  Here’s how we do it at OGsys:

  1. Create a job (#1) that fires nightly and creates a backup of the given database to a specific named file (i.e. generates OGsql_Company_05AUG2010.bak) to a specific location.
  2. Use existing backup procedures to pick up the *.bak file, some time after job #1 is known to have completed.  Either leave plenty of time, or periodically monitor the gap between SQL Backup completion and file pickup.
  3. Create a job (#2) that fires weekly and deletes the old bak files so they don’t pile up.

This is more complex, but assuming you have an edition of SQL Server that comes with the agent, it is ‘free’.  There are several variations on this theme.  For example, you might be able to have the backup software delete the bak file after it is picked up, obviating the need for the second agent job.

In addition to the DB backup (which is the most important), you should also be backing up your OGsql folder to pick up print jobs, configuration files, logs, etc.

Whatever backup method you choose, it should have a component that is “SQL Aware” to maintain the LDF file.