Back up for SQL Servers.

By: Sven Blackwood

In past articles we have established it�s imperative to have a Disaster Recovery Plan to include a Backup Strategy for SQL Servers.

This practice will help you minimize the impact in case of:

  • Accidental or malicious use of the DELETE statement.
  • Hardware or Software failures.
  • Accidental or malicious use of the UPDATE statement � for example forgetting to use a WHERE clause with the UPDATE statement. (All the rows are updated, rather than a single row in a particular table).
  • Destructive Viruses.
  • Natural Disasters.

If you have an appropriate backup strategy, you can restore data with minimal cost to Production time and minimize the chance of permanent data loss.

How frequently you back up your database depends on the amount of data you are willing to lose and the volume of database activity. When you backup user databases, consider the following facts and guidelines:

  • You might back up your database frequently if your system is in an online transaction processing (OLTP) environment.
  • You might back up your database less frequently if your system has little activity or is used primarily for decision support.
  • You should schedule backups when SQL Server is not in the process of being heavily updated.
  • After you determine your backup strategy, you should automate the process by using the Database Maintenance Plan Wizard or by using SQL Server Management Studio.





Three flavors in the Database Recovery Model process

There are three different database recovery models. Each of the models maintains data in the event of a server failure. Additionally, there are key differences in how SQL Server recovers the data as well as in the storage and performance needs in the event of disk failure.

Full Recovery Model

You can use the Full Recovery Model (FRM) when full recovery from damaged media is of the highest priority. This model uses copies of the database and all log information to restore the database. SQL Server logs all the changes to the database, including bulk operations and index creations. Provided of course the logs themselves are not damaged.With this model, SQL can recover all data, except transactions actually in process at the time of the failure.Because all the transactions are logged, it is possible to select the point in time you want. With SQL 2000 the transaction log accepts the insertion of named marks into it allowing the user to make a recovery up to that specific mark.Since Log Transaction Marks consume log space you should use them for transactions or cornerstones that play a significant role in the database recovery strategy only.The main limitation of this model is the large size of the log files, the resulting storage and, most specially, performance costs.

Bulk Logged Recovery Model

This model is similar to the FRM. The Bulk Logged Recovery Model (BLRM) uses both database and log backups to recreate a database.However, this model uses less log space for the following operations:

  • Create Indexs
  • Bulk Load Operations
  • Select Into
  • Write text
  • Update text

    The log notes only the occurrence of these operations as bits in extents instead of storing details of the operations in the log.To preserve the changes for an entire bulk load operation, extents marked as changed are also stored in the log.Because BLRM only stores the final result of multiple operations the log is typically smaller and bulk operations run faster.Using this model can restore all data but with one disadvantage. It is not possible to restore only part of a backup, such as restoring to a specific mark.

    Simple Recovery Model

    Typically, the Simple Recovery Model (SRM) is used for small databases or where data changes infrequently. For example, a database containing the names of the states, and the nomenclature. This model uses full, or differential copies, of the database and recovery is limited to restoring the database to a point when the last backup was made.All Changes made after the backup are lost and must be recreated. The principal benefit of this model is it takes less storage space for logs, and is the simplest model to implement.

    How to change a database recovery Model

    By default, SQL Server 2005 uses the Full Recovery Model. You can change the recovery model at any time, but you must make an additional backup at the time of the change.

    If you are not sure what model your database is using, you can use the DATABASEPROPERTYEX function to determine your current model.The correct syntax to change the Database Recovery Model. ALTER DATABASE database_name SET RECOVERY {FULL | SIMPLE | BULK_LOGGED }Ie. ALTER DATABASE qrelist SET RECOVERY BULK_LOGGED

    Final Considerations

    You should consider, as final thoughts, while you perform a backup your users are able to continue to work with the Database. The original files are not the only thing backed up, but the records, and locations are also stored. Additionally, all the Database Activities that occur during the Backup Process are also stored in the Backup.


    footer for Recovery page