SQL Server allows a log backup to be created even when all data files are not available for the database. This is very useful in restoring the transactions that occurred right before disaster occurred.

The BACKUP LOG command has an option NO_TRUNCATE which allows us to create a log backup when database is inaccessible. To quote BOL, Using NO_TRUNCATE specifies that the log not to be truncated and causes the Database Engine to attempt the backup regardless of the state of the database.

To demonstrate this scenario I have created a database with a single data and log file, both reside on different disks, I have also created a table (so that backup will have some data):

Now, let’s create a full database backup to start with:

Result Set:

Processed 152 pages for database ‘SQLServerGeeks’, file ‘SQLServerGeeks’ on file 1.
Processed 2 pages for database ‘SQLServerGeeks’, file ‘SQLServerGeeks_Log’ on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.453 seconds (2.769 MB/sec).

After the backup completes let us create another table in the database:

Now, I’ll simulate a disaster by taking Disk 2 (F:) Offline, and restart the instance. You can see from the below screen shot that even though the database if inaccessible, it is still available in Object Explorer:

1_SQL_Server_Creating_a_Tail_Log_Backup_when_Data_files_are_not_available

But, running any command against it will fail:

Result Set:

Msg 945, Level 14, State 2, Line 1
Database ‘SQLServerGeeks’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Let us first try and create a log backup without using any additional options:

Result Set:

Msg 945, Level 14, State 2, Line 2
Database ‘SQLServerGeeks’ 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 2
BACKUP LOG is terminating abnormally.

This fails since it tries to clear the log. Now let’s try with NO_TRUNCATE option to Create tail log backup.

Result Set:

Processed 7 pages for database ‘SQLServerGeeks’, file ‘SQLServerGeeks_Log’ on file 1.
BACKUP LOG successfully processed 7 pages in 0.011 seconds (4.794 MB/sec).

This works perfectly as it does not clear the log and hence no need to update the metadata in data file.

Now let’s check if this backup actually contains data up to the disaster point. Our backup should also restore TestTable2 which was created after the full backup was taken. Since Disk 2 (F:) is offline we’ll move data file to Disk 1 (C:), and subsequently apply the tail log backup to bring database online.

Result Set:

10 percent processed.
20 percent processed.
31 percent processed.
41 percent processed.
52 percent processed.
62 percent processed.
73 percent processed.
83 percent processed.
94 percent processed.
100 percent processed.
Processed 152 pages for database ‘SQLServerGeeks’, file ‘SQLServerGeeks’ on file 1.
Processed 2 pages for database ‘SQLServerGeeks’, file ‘SQLServerGeeks_Log’ on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.112 seconds (11.200 MB/sec).

100 percent processed.
Processed 0 pages for database ‘SQLServerGeeks’, file ‘SQLServerGeeks’ on file 1.
Processed 7 pages for database ‘SQLServerGeeks’, file ‘SQLServerGeeks_Log’ on file 1.
RESTORE LOG successfully processed 7 pages in 0.006 seconds (8.789 MB/sec).

And finally let’s see if it contains all data:

Result Set:

name
——————————————————————————————–
TestTable1
TestTable2

(1 row(s) affected)

You can see that we have recovered all data successfully. That’s all folks.

Hope This Helps!

 

Regards

Vishal Gajjar

Like us on FaceBook Follow us on Twitter

Follow me on Twitter | Join the fastest growing SQL Server group on FaceBook