MS SQL Server Disaster Recovery

Despite the media backup programs or internal backup procedures that you may have in place, with few exceptions, the MS SQL Server Software itself is the best tool to backup your SQL Server.  The main reason for this is that SQL related backups are tightly integrated with the way that the server operates.  If you have third party SQL based backup solutions, they need to communicate with various SQL services in order make the actual database backup.  If any MS SQL services required are unavailable, the backup may not start or complete.

1. Doing MS SQL Backups.

MS SQL Server 2005 and greater, supports doing both Incremental Database and Full Database Backups.  It is recommended that you always do Full SQL Backups instead of Incremental ones. This is because it insures that you get all of the data, not just changes since the last backup was made.  This is critical in order to be able to make a complete recovery, in a timely manner.  It's better to have the whole picture and not need it, than to not have enough of the right combination of pieces that you can't put back together properly. If your database is unusually large, this may require special considerations.

As a general rule it is best to make a Full Database Backup, at least once a day, preferably before any automated Point of Sale posting is done.  This gives you a clean recovery point.  However, since MS SQL data backups can be done while the application is in use, the can actually be done multiple times during the day, such as every hour or every couple of hours.  

Hourly backups would be more than many, but not all, Retailers would need.  If you open a lot of new accounts each day you may want to consider frequent backups to protect your receivables.  A failure may result in permanent loss of account details.

Special Considerations

Regarding SQL backup support in MS SQL Server 2005 Management Studio v.s. MS SQL 2005 Management Studio Express. 

MS SQL 2005 (Server) Management Studio supports doing both scheduled backups with associated or separate maintenance tasks.

MS SQL 2005 (SSE 2005) Management Studio Express DOES NOT support doing scheduled  backup tasks or scheduled Maintenance Tasks.  If a  scheduled backup is desired for SSE 2005,  this must be done as a custom OSQL(SQLCMD) script that is scheduled through the MS Task Scheduler.

However, database backups can always be done manually at any time in either of these applications. 

2.  SQL Backup Frequency, and Backup Rotation.

The frequency and number of SQL backups made are largely at your discretion. The following factors should be considered:

• The load that the backup may put on the server (if done during normal working hours), as it may affect server and application performance.

• The amount of total available disk space available on your hard disk and/or external backup media.

• Normally, when CCS Retail sets-up MS SQL Server 2005, the MS SQL Management Studio software is used to create an internal backup of ALL databases with a (7) seven day rotational cycle.

• The size of your existing databases, database logs, and existing backups, and the number of backup rotations being done.  As an example, using the above mentioned (7) seven day rotation cycle:

          15 GB of database and database logs (Current).
          14 GB of database backups (Rotation #1 - Last Backup)
          13.25 GB of database backups (Rotation #2)
          12.78 GB of database backups (Rotation #3)
          12 GB of database backups (Rotation #4)
          12 GB of database backups (Rotation #5)
          12 GB of database backups (Rotation #6)
          11.5 GB of database backups (Rotation #7)
          ========

         102.53 GB

This example needs a total of 102.53 GB of disk space in order to store and backup just what is currently there.  If just this data alone is being backed up to other media, then that media would need to have a greater total  capacity for future needs.  In this example, both local and external  300+ GB drives would probably be merited.  With 1TB (1,000G) drives starting at $100 this is a wise investment.

3. Data backups to Media.

When making any media backup, software can always be re-installed as long as you have the media. What's most critical is to backup the current database.  This means that your system backups should include both the current database and your SQL database backup files.

Software can always be re-installed as long as you have the media, so this is less critical.

Your media backups should include ALL of the files in the root (default) install path that MS SQL uses for It's installation. Normally this is "C:\Program Files\Microsoft SQL Server", by default.    Generally, the live data bases are in the "MSSQL.1\MSSQL\Data" sub-folder, and the backups are in the "MSSQL.1\MSSQL\Backups" sub-folder.

If you have questions or concerns related to the above information, or if you need assistance with the set-up, please contact the CCS Retail Systems Support Department.

- John