SQL restore tail log

[Verneti_Berny]

Hi, 

I have a problem trying to restore tail log of SQL database.

Error: 

OPERATION BACKUP
DATABASE "T01"
SQLHOST "SQLServer"
NBSERVER "masterserver.COM.BR"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
POLICY sql-test
OBJECTTYPE TRXLOG
TRXOPTION TAILLOG
SCRATCHDIR "C:\Program Files\Veritas\NetBackup\dbext\mssql\temp"
NUMBUFS 2
ENDOPER TRUE

USER - Operation inhibited by NetBackup for Microsoft SQL Server: Database <T01> cannot be backed up because it is in <loading> state.
INF - OPERATION #1 of batch C:\tmp\rest_tail.bch FAILED with STATUS 1 (0 is normal). Elapsed time = 42(42) seconds.
INF - Results of executing <C:\tmp\rest_tail.bch>: 
<0> operations succeeded. <1> operations failed.
INF - The following object(s) were not backed up successfully.
INF - T01

Netbackup server and clients 7.6.0.3

Master and Client Windows 2012 R2

Can anybody help me?

Thanks

[Will_Restore]

See this thread for possible solution: https://www-secure.symantec.com/connect/forums/failed-sql-2008-backup-status-code-2

If the database is in a loading state and needs to be restarted, log into the Microsoft SQL Query Analyzer and run the following command ...

[Verneti_Berny]

Thanks Will, but I followed this doc and database return ok, but I lost the last transaction.

The information that would be on tail log was lost.

Thanks.

[Will_Restore]

Are you trying to RESTORE the taillog?  Your script above says OPERATION BACKUP. 

[Verneti_Berny]

Ok, but the operation in NBU is "Backup and restore tail log".

It doesn´t have only "restore tail log" option.

[Marianne]

Have a look at this section in:  NetBackup for Microsoft SQL Server Administrator's Guide 

Restoring a SQL transaction log image without staging a full recovery

[Verneti_Berny]

Hi Marianne,

I did restore transaction log, I cannot restore tail log.

My DBA sad to me something like this:

- When I´d crash database, I need to backup tail log before restore transaction logs and database, but I cannot do this. At this point what I can is restore database until the last transaction log, but I lost the last inserts on database.

Do you understand me?

[Marianne]

Problem is that you cannot do any kind of backup with database in <loading> state.

Your dba will agree.

See http://www.symantec.com/docs/TECH45390 

[Verneti_Berny]

Marianne, I would like to post here my step by step to try restore an crash SQL database, ok?

I´m sorry for my english is not so good.

I made this steps:

1) I have one full backup;

2) two transaction logs with no recovery option;

3) I crashed my database, for this, put it in offline and rename mdf file;

4) try put db on line, it became in "recovery pending" state;

5) try backup tail log;

6) it show me error "DBMS MSG - ODBC return code <-1>, SQL State <37000>, SQL Message <945><[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'T01' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.>."

Attached follow my backup scripts.

Could you help me?

Thank you very much.

[jberks]

Hi,

I have exactly the same question.

I am testing out Netbackup with SQL Server and it works well except for this.

To do a full recovery from the above scenario, I need to manually take a disk based backup of the tail log first, using SQL Management studio and the following command

USE master;

--Create tail-log backup.

BACKUP LOG AdventureWorksLT2012

TO DISK = 'E:\Microsoft SQL Server\MSSQL11.TEST\MSSQL\Backup\AdventureWorksFullRM.bak' 

   WITH NORECOVERY, NO_TRUNCATE;

before carrying out a "no recovery" restore of the last transaction log from netbackup.

Then, using sql management studio restore the transaction log backup taken above.

This option looked interesting as it would be a one-click solution to such a failure but from what I can see, it doesn't work as it requires the database to be up and active before it can connect, precicely the scenario where you wouldn't need it.

Either that or it's badly named and isn't intended to do what it says.

I take the lack of response to imply nobody knows, nobody has tried or used this option and it was never tested before release. Just shabby development.

Happy to be corrected

Anyone?