SQL Recovery models

For each database that you create in SQL Server, with the exception of the system databases, you can configure it to use one of three possible recovery models (simple, full, bulk-logged).  Here is a simple script to display the recovery models of all on-line databases:

SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name

If you are running SQL Server 2005 or later, you can use this script instead:

SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

This is how you can change a database's recovery model to the simple recovery model:

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

to the full recovery model:

ALTER DATABASE AdventureWorks SET RECOVERY FULL

and to the bulk-logged recovery model:

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

Which recovery model do you need to use?  It depends on how much data you can afford to lose.  Let's use the following diagram to illustrate the difference between the recovery models, where a full database backup is performed at 9:00 a.m, and 11 a.m.

The simple recovery model

Assume that there was a hardware failure at 10:45  a.m.  If the database was using the simple recovery model, you would have lost 105 minutes of work.  The latest point at which you can recover the database to is 9:00 a.m, since that was the time the last full backup that was made.  You could schedule differential backups to run periodically e.g.

In this case, you would lose 45 minutes of work.  Now, assuming that a user deleted a critical table at 9:50 a.m.  Can you recover to the point in time just before the deletion?  No.  The differential backup contains only the changed data pages.  It cannot be used to restore to a specific point in time.  You'll have to restore the database to its 9 a.m state, and redo 49 minutes of work.  Then, you'll also need to redo the work that was performed after the deletion up to the time the error was discovered.

The full recovery model

If no transaction log backups are made between 9 a.m and 11 a.m, you would face the same situation as you would if the database had been using the simple recovery model.  In addition, your transaction log file would be much larger, as SQL Server will not truncate committed and checkpointed transactions until they have been backed up.

Assume that the transaction log is backed up every 30 minutes:

If a hardware failure occurs at 10:45 a.m, you would lose 15 minutes of work.  You can use the full database backup at 9 a.m, and apply the transaction logs to bring the database to its state at 10:30 a.m.  What if some critical data was deleted at 9:50 a.m?  Using the transaction log backup that was made at 10:00 a.m, you can restore the database to its state at 9:49 a.m.  You would still need to redo the work that was performed between the time of the deletion up to the time the error was discovered, as you cannot restore up to 9:49 a.m, skip the 9:50 a.m transactions, and restore the later transactions.  Admittedly, that would be great.

There are tools in the market that can simply reverse the changes created by user errors, while keeping the other transactions intact.  You still need transaction log backups to do that if the transaction is no longer in the active transaction log i.e. it has been backed up.

The bulk-logged recovery model

The situation with the bulk-logged recovery model is identical to the full recovery model if no minimally logged transactions are created in the database.  An example of a minimally logged transaction is a SELECT INTO operation.  Say that such a transaction occurred at 9:40 a.m.

This transaction would be minimally logged, which means that SQL Server only records the changed database pages arising from the transaction.  It does not log every insert into the new table.  Again, say that a critical table was deleted at 9:50 a.m.  What happens now?  You can no longer restore the database to its state at 9:49 a.m, as the transaction log backup created at 10:00 a.m cannot be used to restore to a specific point in time.  The best you can do is to restore the database to its state at 9:30 a.m.  Whenever a transaction log backup contains one or more minimally logged transactions, you cannot use that backup to perform a point in time restore.

Why then would anyone use the bulk-logged recovery model?  The main reason is performance.  Let's use the example of a SELECT INTO operation, say creating a large table from the results of a query.  If using the full recovery model, the details of every insertion into the new table is logged, and the transaction log will consume more space.  If using the bulk-logged recovery model, only details of the modified data pages are logged, allowing for better performance.  As we have seen, this comes at the expense of being able to perform a point in time restore using the transaction log.

The operations that are minimally logged are as follows (taken from SQL Server 2005 Books Online):

·Bulk import operations (bcp, INSERT ... SELECT * FROM OPENROWSET(BULK...), and BULK INSERT).

·text, ntext, and image operations using the WRITETEXT and UPDATETEXT statements when inserting or appending new data. Note that minimal logging is not used when existing values are updated.

·SELECT INTO operations.

·Partial updates to large value data types using the UPDATE statement's .WRITE clauses when inserting or appending new data. Note that minimal logging is not used when existing values are updated.

·If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged whether the operation is executed offline or online.

·DROP INDEX new heap rebuild (if applicable).

Tail log backing (SQL Server: Detecting and Correcting Database Corruption)

One of the first things you should always check when a database has been damaged and you’re going to perform a restore operation is whether you need to back up the tail of the log.

The tail of the log contains the transaction log that’s been generated since the most recent log backup was taken. If you want to be able to recover right up to the point of the disaster, you need to be able to get those log records so they can be part of the restore sequence.

There are two cases to consider here 1) the server is still available 2) the server is not available. For case 1, you can just perform a regular tail-of-the-log backup. For case 2, you’ll need to hack-attach the log into another server to be able to back it up.

Case 1: tail-of-the-log backup when server is available

If the database is damaged but the server is still available, taking a tail-of-the-log backup is simple. The only exception is when there has been a minimally-logged operation performed in the BULK_LOGGED recovery model since the previous log backup – in that case a tail-of-the-log backup is not possible at all, and you’ll have lost all transaction log generated since the last log backup. See A SQL Server DBA myth a day: (28/30) BULK_LOGGED recovery model for more details.

When the data files are damaged and you want to take a log backup, you’ll get an error if you try to back up the log normally.

As an example, I’ll create a database and put some transactions in it:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

CREATE DATABASE [DBMaint2008]; GO USE [DBMaint2008]; GO CREATE TABLE [TestTable] ([C1] INT IDENTITY, [C2] CHAR (100)); GO -- Take a full backup BACKUP DATABASE [DBMaint2008] TO DISK = N'D:\SQLskills\DemoBackups\DBMaint_Full.bck' WITH INIT; GO -- Insert some rows INSERT INTO [TestTable] VALUES ('Transaction 1'); INSERT INTO [TestTable] VALUES ('Transaction 2'); GO -- Take a log backup BACKUP LOG [DBMaint2008] TO DISK = N'D:\SQLskills\DemoBackups\DBMaint_Log1.bck' WITH INIT; GO -- Insert some more rows INSERT INTO [TestTable] VALUES ('Transaction 3'); INSERT INTO [TestTable] VALUES ('Transaction 4'); GO

Now disaster will strike – I’ll do the following to simulate a disaster:

1. ALTER DATABASE [DBMaint2008] SET OFFLINE;
2. Delete the data file

If I try to then access the database, I’ll get:

1 2	ALTER DATABASE DBMaint2008 SET ONLINE; GO

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBMaint2008.mdf". Operating system error 2: "2(The system cannot find the file specified.)". Msg 945, Level 14, State 2, Line 1 Database 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

So I’ll try a normal log backup:

1 2	BACKUP LOG [DBMaint2008] TO DISK = N'D:\SQLskills\DemoBackups\DBMaint_Log_Tail.bck' WITH INIT; GO

Msg 945, Level 14, State 2, Line 1 Database 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.

It doesn’t work – as the data files aren’t all accessible.

The trick is to use the NO_TRUNCATE option, which allows the log backup even if the database files aren’t there:

1 2	BACKUP LOG [DBMaint2008] TO DISK = N'D:\SQLskills\DemoBackups\DBMaint_Log_Tail.bck' WITH INIT, NO_TRUNCATE; GO

Processed 2 pages for database 'DBMaint2008', file 'DBMaint2008_log' on file 1. BACKUP LOG successfully processed 2 pages in 0.010 seconds (0.972 MB/sec).

I can then use the tail-of-the-log backup as the final backup in the restore sequence, saving transactions 3 and 4 from above. Try it for yourself.

Case 2: tail-of-the-log backup when server is no longer available

This is the case where the server crashed and cannot be brought online. If you have access to all the data and log files for the database, you can attach it to another server and crash recovery will run automatically. If you only have access to the log file, you’ll need to perform some extra steps to allow a tail-of-the-log backup to be performed – basically performing what I call a hack-attach.

Assuming I’ve run the script above to setup the database, this time I’ll do the following to simulate a server-crash disaster:

1. ALTER DATABASE [DBMaint2008] SET OFFLINE;
2. Delete the data file
3. Copy the log file somewhere else
4. DROP DATABASE [DBMaint2008];

Now all I have is some backups and a log file. I’ll need to attach the log file to SQL Server somehow so that I can perform the vital tail-of-the-log backup. The way to do it is:

1. Create a dummy database with the same name as the one that we’re interested in (make sure you have instant file initialization enabled so the file creations don’t take ages – see this blog post)
2. Set the database offline (or shutdown the server)
3. Delete all the files from the dummy database
4. Drop in the log file from our real database

Like so for steps 1 and 2:

1 2 3 4

CREATE DATABASE [DBMaint2008]; GO ALTER DATABASE [DBMaint2008] SET OFFLINE; GO

Now I’ll perform steps 3 and 4.

I need to attempt to bring the database online again:

1 2	ALTER DATABASE [DBMaint2008] SET ONLINE; GO

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBMaint2008.mdf". Operating system error 2: "2(The system cannot find the file specified.)". Msg 945, Level 14, State 2, Line 1 Database 'DBMaint2008' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

And then I can perform the tail-of-the-log backup as before, and use it to recover everything up to the point of the disaster.

Note: This procedure does not work if I try to perform the hack-attach to a more recent version of SQL Server. I tried hacking a 2005 log into a 2008 server and taking the tail-of-the-log backup - which worked fine, but the tail-of-the-log backup could not be used in conjunction with the first set of backups from the 2005 server. The reason for this is that the database version number in the tail-of-the-log backup is 655 (SQL Server 2008) and those for the 2005 backups are 611 (SQL Server 2005). The database doesn’t get upgraded when restoring on the 2008 server until the end of the restore sequence – so as far as the 2008 server is concerned, my 2008 tail-of-the-log backup can’t be used in the restore sequence of a still-2005-really database. Hope that makes sense!

Summary

Checking whether a tail-of-the-log backup is required is essential during a disaster recovery, and it’s not hard to do. As with any disaster recovery procedures, make sure you’ve practiced doing it in advance!