Post reply

Tail log tsql staement not working before restoring database in sql 2012

  • September 1, 2014 at 4:22 am

    #298965

    I am working in sql 2012 enterprise edition wherein I am trying to take diff kind of backup of database with t-sql and it works fine.

    The issue comes when I try to restore it using tsql ,after taking last(tail) log backup using tsql command.

    It gives me error that tail log backup has not been taken.Although I am doing restore offline,I restart service also but still can not start restore.

    Only when I take tail log backup from Object explorer-task using backup wizard then i can restore it successfully.What is going wrong here....

  • September 1, 2014 at 4:41 am

    #1742202

    Can you show us your syntax?

  • September 1, 2014 at 11:59 pm

    #1742289

    Backup Database db1

    to disk='c:\myfolder\Fullbkup.bak'

    with init

    go

    Backup log db1

    to disk='c:\myfolder\logbkup.trn'

    go

    I make changes in datatbase and before restoring take last log backup

    Backup log db1

    to disk='c:\myfolder\Taillog.trn'

    go

    Use Master

    go

    Restore database db1

    from disk='c:\myfolder\Fullbkup.bak'

    with Norecovery

    go

    I get error of Tail log has not been take.

  • September 2, 2014 at 2:27 am

    #1742324

    sej2008 (9/1/2014)

    I make changes in datatbase and before restoring take last log backup

    Backup log db1

    to disk='c:\myfolder\Taillog.trn'

    That's not a tail-log backup. That's just a normal log backup. It doesn't have the option specified that would make it a tail log backup (calling it one doesn't make it so)

    The message is perfectly correct, no tail log backup has been taken.

    See BACKUP LOG in books online for the details of all the options. There's a section in there on tail log backups.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 2, 2014 at 3:59 am

    #1742352

    Tail log is taken using the following (assuming the db is online and undamaged)

    Backup log db1

    to disk='c:\myfolder\Taillog.trn' with norecovery

    If the database is damaged you could use

    Backup log db1

    to disk='c:\myfolder\Taillog.trn' with norecovery, no_truncate

    Note: if the database is in use the command will fail

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • September 2, 2014 at 5:40 am

    #1742391

    yes,Thank you

    I missed using Norecovery option.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply