Log backup of offline database

Question

How can we take the log backup of a offline database?

Answer 1

you cannot take backup of offline database (logbackup or any backup), you can do file system backup..

incase if your MDF file doesnot exists and your log file not corrupted you can do taillog backup(fyi..)

---

Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 2



I read in MSDN that "Tail log backups capture the tail of log even if the database is offline, damaged or missing data files". What does it mean?

Answer 3

Hi Chandra,

see below -

Scenarios That Require a Tail-Log Backup

---

We recommend that you take a tail-log backup in the following scenarios:

• If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.

• If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no transactions can occur at this time, using the WITH NORECOVERY is optional.

• If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.

  On a damaged database backing up the tail of the log can succeed only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes. If a tail-log backup cannot be created, any transactions committed after the latest log backup are lost.

The following table summarizes the BACKUP NORECOVERY and CONTINUE_AFTER_ERROR options.

BACKUP LOG option	Comments
NORECOVERY	Use NORECOVERY whenever you intend to continue with a restore operation on the database. NORECOVERY takes the database into the restoring state. This guarantees that the database does not change after the tail-log backup. The log is truncated unless the NO_TRUNCATE option or COPY_ONLY option is also specified. Important We recommend that you avoid using NO_TRUNCATE, except when the database is damaged.
CONTINUE_AFTER_ERROR	Use CONTINUE_AFTER_ERROR only if you are backing up the tail of a damaged database. Note When you use back up the tail of the log on a damaged database, some of the metadata ordinarily captured in log backups might be unavailable. For more information, see Tail-Log Backups That Have Incomplete Backup Metadata, later in this topic.

---

Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 4







I am talking about the second scenario where database is offline. Here it's mentioned that first take the tail log backup. My question is HOW.

Answer 5

The one way you can do copy the log file to some other server & create an new dummy database with help of that you can perform tail log backup.

see

http://sqlskills.com/blogs/paul/category/Transaction-Log.aspx

the second one it is Improper shutdown database cases...

---

Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 6









The statement is

If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no transactions can occur at this time, using the WITH NORECOVERY is optional. 

Here it's saying to restore the same database.

So still looking for satisfactory answer. 

Answer 7

That tail log backup works only if the database goes offline in case during the start up of the database is fail to come online due to some reasons or damaged due to some reason, incase if we did purpose fully then that will not work (i,e the database didnt have any issues at all).

---

Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 8

So the status OFFLINE is internally different in different scenario. Any reliable source of your information or way to proof it?

Answer 9

we can take the backup of database which is in offline,standby,recovering .emergency modes of a database,

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 10

Yes, in that text, they are not refering to the database option OFFLINE, but a more generic scenario where the database isn't accessible for some reason (but *not* set to OFFLINE). Here is proof that you cannot backup a database which is OFFLINE:

CREATE DATABASE x 
GO
ALTER DATABASE x SET RECOVERY FULL
BACKUP DATABASE X TO DISK = 'nul'
BACKUP LOG X TO DISK = 'nul' --Success
ALTER DATABASE x SET OFFLINE
BACKUP LOG X TO DISK = 'nul' --Error
GO
BACKUP LOG X TO DISK = 'nul' WITH NO_TRUNCATE --Error
GO
BACKUP LOG X TO DISK = 'nul' WITH CONTINUE_AFTER_ERROR --Error

For generic info on backing up a damaged database, see http://sqlblog.com/blogs/tibor_karaszi/archive/2010/03/27/restore-database-to-the-point-of-disaster.aspx

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 11

Yes Tibor, I checked that and that's why I asked this question. But still here Ramesh is saying that it's possible to take backup of offline database.

Ramesh,

Please let me know in detail how can we take backup while database is in OFFLINE mode.

Answer 12

Hi Subhash,

Seems it is typo Mistake I think(@Ramesh correct me If Iam wrong here)..

Dont mind plz,Also you can refer-
http://sqlskills.com/blogs/paul/category/Transaction-Log.aspx
look at the Disaster recovery 101: backing up the tail of the log in the same page  or refer the Tibor one.. or BOL for tail log backup when you can take!

If its your general question ,then you can do in your test server by referring as mentioned above..

---

Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 13

Thank you Rama and Tibor. :)