SQL Server - Creating a Tail Log Backup when Data files are not available

Who is online?  469 guests and 0 members
home  »  blogs  »  Vishal Gajjar  »  SQL Server - Creating a Tail Log Backup when Data files are not available

SQL Server - Creating a Tail Log Backup when Data files are not available

change text size: A A A

posted  5/27/2012 5:37:45 PM  by Vishal Gajjar -  Views: [2063] 

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):
USE [master]
GO

CREATE DATABASE [SQLServerGeeks] ON PRIMARY 
(
	NAME = N'SQLServerGeeks',
	FILENAME = N'F:\Database\2005Dev\Data\SQLServerGeeks.mdf'
)
LOG ON 
(
	NAME = N'SQLServerGeeks_Log',
	FILENAME = N'C:\Database\2005Dev\Log\SQLServerGeeks_Log.LDF'
)
GO

USE [SQLServerGeeks]
GO

CREATE TABLE TestTable1
(
	Col1 INT
)
GO

Now, let’s create a full database backup to start with:
BACKUP DATABASE [SQLServerGeeks] TO DISK = N'C:\SQLServerGeeks_FULL_Backup.bak'
GO

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:
USE [SQLServerGeeks]
GO

CREATE TABLE TestTable2
(
	Col1 INT
)
GO

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:
Object Explorer View
 
 
But, running any command against it will fail:
USE [SQLServerGeeks]
GO

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:
USE [master]
GO

BACKUP LOG [SQLServerGeeks] TO DISK = N'C:\SQLServerGeeks_TailLog_Backup.Log'
GO
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 take a tail log backup.
USE [master]
GO

BACKUP LOG [SQLServerGeeks] TO DISK = N'C:\SQLServerGeeks_TailLog_Backup.bak' WITH NO_TRUNCATE
GO
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.
USE [master]
GO

RESTORE DATABASE [SQLServerGeeks]
FROM DISK = N'C:\SQLServerGeeks_FULL_Backup.bak'
WITH FILE = 1,
MOVE N'SQLServerGeeks' TO N'C:\Database\2005Dev\Data\SQLServerGeeks.mdf',
NORECOVERY, REPLACE, STATS = 10
GO

RESTORE LOG [SQLServerGeeks]
FROM DISK = N'C:\SQLServerGeeks_TailLog_Backup.bak'
WITH FILE = 1,
NOUNLOAD, STATS = 10
GO

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:
USE [SQLServerGeeks]
GO

SELECT	name
FROM	sys.tables
GO

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!
Vishal
Email me your questions at Vishal@SqlAndMe.com
Find me on Twitter -> @SqlAndMe
Find me on Google Plus – Vishal Gajjar

If you liked this post, do like us on Facebook at http://www.facebook.com/SQLServerGeeks
 
tags : SQL Server, database offline log backup, log backup, log backup no_truncate, log backup without data files, no truncate, no_truncate, Tail log backup
  Rate This Blog Entry:  register  or  login

Author

Vishal.Gajjar Vishal Gajjar (Member since: 4/22/2011 7:43:43 AM)

View Vishal Gajjar 's profile

Comments (2)

SQLHULK
7/21/2012 12:10:15 PM SQLHULK said:

You mentioned that you took F: offline

 

N'F:\Database\2005Dev\Data\SQLServerGeeks.mdf'

But your primary filegroup is sitting on the F drive so the entire database would be offline.  Did you intended to take the drive that has the log file offline instead? 

by
Vishal.Gajjar
7/22/2012 3:56:22 AM Vishal Gajjar said:

Hi SQLHULK,

That's exactly what I wanted to demonstrate. You can take a log backup even when the data files are not available.

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Vishal Gajjar's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Parse DBCC MemoryStatus using Powershell
    5/19/2013 10:58:46 AM by Ahmad Osama  - Views: 172  - Comments: 0
    A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...
  • DB-Migrate v2: Powershell Module to Migrate Databases
    5/14/2013 8:11:24 PM by Ahmad Osama  - Views: 249  - Comments: 0
    This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...
  • The TSQL Classes Stored Procedures: Transaction Handling
    5/12/2013 4:14:12 PM by Ahmad Osama  - Views: 271  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...
  • Reporting With SSRS Part 1 -With Sample Report.
    5/9/2013 4:33:03 AM by Syed Asrar Ali  - Views: 159  - Comments: 0
    Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...
  • SQL Server – What is IDENTITY_INSERT?
    5/8/2013 2:31:43 PM by Piyush Bajaj  - Views: 1052  - Comments: 3
    Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....
  • DB-Migrate: A PowerShell Module to Migrate Databases.
    5/5/2013 8:48:46 PM by Ahmad Osama  - Views: 437  - Comments: 0
    Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...
  • SQL Server blogs from Ahmad Osama for the month of April
    5/3/2013 10:22:05 AM by Admin Admin  - Views: 347  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...
  • The TSQL Classes Stored Procedures: Error Handling
    5/1/2013 9:05:11 PM by Ahmad Osama  - Views: 801  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...
  • Get free space in all files in an Instance
    4/8/2013 9:01:44 AM by Manohar Punna  - Views: 1663  - Comments: 1
    As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scri...
  • SQL Server: Using Powershell to Fix Orphan Users
    4/2/2013 7:05:50 PM by Ahmad Osama  - Views: 1705  - Comments: 0
    One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell ...
  • The TSQL Classes Stored Procedures: Parameter sniffing
    4/1/2013 10:55:43 PM by Ahmad Osama  - Views: 2437  - Comments: 3
    This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-cachin...