In Part 1 of the SQL Server Essentials series, we discussed the Database Administrator's primary responsibility. Given that your data is so important, how can you manage and ensure it's safekeeping? Should a data loss event occur, you want to be in a position to enable you to protect the majority, if not all, of your data.

What can I do to defend myself against data loss?

You want to be able to create copies of your database data and to store them in a safe place, so that should a data loss event occur, you are able to recover any lost/damaged data as a result.

Fortunately help is at hand in order to achieve this objective. The most basic and simple defense mechanism at your disposal is the SQL Server backup and restore component. It provides an essential safeguard for protecting critical data stored in SQL Server databases.

SQL Server data backup

There are several scopes of data backups available for use in SQL Server. These being:

• a whole database
• a partial database
• a set of files
• a set of filegroups

For each of these SQL Server supports both FULL and DIFFERENTIAL backups.

• A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
• A differential backup contains only the data that has changed since the differential base (last full backup).

For detailed documentation regarding SQL Server Backups, there is excellent coverage in SQL Server Books Online. A good starting point would be Backing Up and Restoring Databases in SQL Server. You should consider this as part of your Essential Reading List.

Data loss, it will happen to you

You should always work under the assumption that a data loss event will occur at some point and that it will have a detrimental impact to your database. Adopting this frame of mind will assist you with planning accordingly for such an event, so that you are not caught out unexpectedly.

Remember, a data loss event does not have to be a huge catastrophe. It could be something that in essence is quite simple. For example, the dropping of a database table, or perhaps a developer may need to recover a prior version of a Stored Procedure or Trigger's source code. If you would like to learn more about the different types of data loss events refer back to Part 1 of the SQL Server Essentials Series, The Database Administrators Primary Responsibility.

You can protect yourself against all but the most extreme data loss scenarios by taking a few simple steps and ensuring you undertake sufficient planning for your requirements.

You may need to take a FULL database backup right now

Should you discover that you do not have a backup of your database data whatsoever, then you are currently very exposed to a data loss event and should create a database backup either immediately or as soon as it is practical to do so.

• How to: Back Up a Database (SQL Server Management Studio)
• How to: Create a Full Database Backup (Transact-SQL)

There are several ways in which you can determine if your SQL Server database has been backed up. A Database Administrator will often create scripts in order to manage such a task. Some examples include:

• Script to retrieve SQL Server database backup history; last week, most recent and no backups
• SQL Server Backup History Analysis
• Script to check that backup files still exist for SQL Server

Why a FULL database backup alone is not sufficient

As you may have gathered by this point, a SQL Server Full database backup provides a copy of your data that is accurate only as of the time it was taken. So any transactions that have subsequently occurred since your database backup, are exposed and vulnerable to a data loss event.

If your database data is relatively static or perhaps you have a read-only database, then this may not be a concern for you and your regular Full database backups may prove sufficient for your requirements.

If on the other hand, your database experiences frequent transactional activity involving data modifications, for example a typical Online Transaction Processing (OLTP) database, then you will want to ensure your data is protected at all times. You will want to ensure that you can recover your database to a particular point in time.

To facilitate this need, each SQL Server database records all transactions and database modifications made by each transaction, within the Transaction Log. The extent to which this information is logged however, is determined by the Recovery Model that your database is using.

SQL Server Recover Models

As described in SQL Server Books Online, Introduction to Backup and Restore Strategies in SQL Server:

"Backup and restore operations occur within the context of a recovery model. A recovery model is a database property that controls how the transaction log is managed. Also, the recovery model of a database determines what types of backups and what restore scenarios are supported for the database. Typically a database uses either the simple recovery model or the full recovery model. The full recovery model can be supplemented by switching to the bulk-logged recovery model before bulk operations. For an introduction to these recovery models and how they affect transaction log management, see Recovery Models and Transaction Log Management.

The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required. The advantage of using log backups is that they let you restore a database to any point of time that is contained within a log backup (point-in-time recovery). You can use a series of log backups to roll a database forward to any point in time that is contained in one of the log backups. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data."

To summarise the key message here, in order to be able to restore your database to a particular point in time you "must" be using the Full Recovery Model.

For a detailed explanation of these concepts, please refer to SQL Server Books Online:Backup Under the Full Recovery Model which again should be considered as part of your Essential Reading List.

Final Thoughts

I hope you have enjoyed reading this article and that it proves useful in your administration of SQL Server. As always, if you have any queries, comments or suggestions then please do feel free to leave them in the discussion forum or alternatively you can find my direct contact details on my blog.

Database backups and SQL Server disaster recovery planning are detailed topics and require much more consideration, research and planning than are presented in this article alone. The important point to take away with you, is that if you want to ensure that you are able to recover your database to a specific point in time, say in response to a data loss event, then you must be using the Full Recovery Model.

In a future installment of the SQL Server Essentials series, we will be taking a closer look at SQL Server backups and discussing how you can plan and devise your very own backup strategy.