none
perform tail log-backup

    Question

  • Hi,

    I want to know how to perform tail log-backup when database has crashed and is not online. Since SSMS cannot be opened

    how to take the tail log backup in that situation

    Regards

    Thursday, September 26, 2013 7:42 AM

Answers

  • you can use sqlcmd to take tail backup

    sqlcmd -d dbname -Q "BACKUP LOG database_name TO <backup_device> WITH NORECOVERY "


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, September 26, 2013 8:35 AM
  • A crashed database will not cause SSMS to not "open".

    You might not be able to get a context-menu if you right-click the database, meaning you won't get to the Backup dialog from there. But then you just righ-click *another* database, open the backup dialog and then select the crashed database from *within* the backup dialog. No problem at all!

    You will in the backup dialog, the "Options" page select "Backup the Tail of the log ..." option. This will cause two parameters to be specified for the BACKUP LOG command: NO_TRUNCATE and NORECOVERY. The one we're after is NO_TRUNCATE since this is required to be able to backup the log of a crashed database. And you can always specify this yourself in a BACKUP command from a query window, if the GUI acts up on you!


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, September 26, 2013 2:39 PM
    Moderator
  • Hi Orbitdba,

    Please go through the below article which will explain you how to connect to your SQL Server instance via SQLCMD utility using both SQL Server authentication and Windows authetication

    http://www.mssqltips.com/sqlservertip/2478/connecting-to-sql-server-using-sqlcmd-utility/

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, September 30, 2013 8:32 AM

All replies

  • Tail log backup is possible if only MDF file is missing/corrupt. Are you able to start SQL Server? What is the message in Errorlog when database is trying to come online?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Thursday, September 26, 2013 7:45 AM
    Moderator
  • Hi,

    I want to know how to perform tail log-backup when database has crashed and is not online. Since SSMS cannot be opened

    how to take the tail log backup in that situation

    Regards

    Hello,

    BOL:A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/57d157a1-9635-4d67-8085-f3aa15f659b3/what-is-the-exact-command-for-taking-taillog-backup


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, September 26, 2013 7:47 AM
  • Hi,

    I want to know how to perform tail log-backup when database has crashed and is not online. Since SSMS cannot be opened

    how to take the tail log backup in that situation

    Regards

    Does the instance start?

    If your database has crashed or gone offline, this won't affect the startup of the SQL instance, unless it's one of the system databases.

    Thursday, September 26, 2013 7:53 AM
  • Hi Orbitdba,

    The equivalent T-sql statement is

    BACKUP LOG database_name TO <backup_device> WITH NORECOVERY 

    But if your instance is not up and running, this is of no use. Corruption of user DB is not going to prevent your instance from coming up.

    What is the exact issue you are facing

    1)You are not able to connect to your instance via ssms

    or

    2)Do you have trouble opening SSMS itself

    As others suggested, can you please post your error log here.

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, September 26, 2013 8:16 AM
  • you can use sqlcmd to take tail backup

    sqlcmd -d dbname -Q "BACKUP LOG database_name TO <backup_device> WITH NORECOVERY "


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, September 26, 2013 8:35 AM
  • SSMS is just an Friendly tool thats it you can use t-sql to perform using the sqlcmd command

    see here cases explained By Paul to get more understand yourself-

    backing up the tail of the log
    Case 1: tail-of-the-log backup when server is available
    Case 2: tail-of-the-log backup when server is no longer available

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- 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.

    Thursday, September 26, 2013 9:09 AM
  • Hi Rama Udaya,

    OrbitDBA said that SSMS Cannot be opened, thats the reason i specified SQLCMD


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, September 26, 2013 11:18 AM
  • A crashed database will not cause SSMS to not "open".

    You might not be able to get a context-menu if you right-click the database, meaning you won't get to the Backup dialog from there. But then you just righ-click *another* database, open the backup dialog and then select the crashed database from *within* the backup dialog. No problem at all!

    You will in the backup dialog, the "Options" page select "Backup the Tail of the log ..." option. This will cause two parameters to be specified for the BACKUP LOG command: NO_TRUNCATE and NORECOVERY. The one we're after is NO_TRUNCATE since this is required to be able to backup the log of a crashed database. And you can always specify this yourself in a BACKUP command from a query window, if the GUI acts up on you!


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, September 26, 2013 2:39 PM
    Moderator

  • Thanks all for the reply,

    Actually I am new to SQL Server, I know how to work in SSMS but not in CLI

    How can I login into CLI? i mean syntax. I tried to login using sqldmc, but i could not connect. please suggest me the syntax to login in sql server db from cli

    Regards

    Monday, September 30, 2013 6:35 AM
  • Hi Orbitdba,

    Please go through the below article which will explain you how to connect to your SQL Server instance via SQLCMD utility using both SQL Server authentication and Windows authetication

    http://www.mssqltips.com/sqlservertip/2478/connecting-to-sql-server-using-sqlcmd-utility/

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, September 30, 2013 8:32 AM