none
Database in restoring state - Can I browse data? RRS feed

  • Question

  • I am playing around with log shipping and I cant find this anywhere... Can I browse data if the database is in restoring state.. (READ ONLY).

    If I put the database out of of that state (restore database <DBNAME> with recovery;) I can not put it back, right?

    Thank you

    Monday, July 9, 2012 10:05 AM

Answers

  • Can you please write an example how you can switch that?

    Microsoft MSDN  / Technet web sites contains many samples:

    restore a database backup description: http://msdn.microsoft.com/en-us/library/ms177429.aspx

    restore syntax: http://msdn.microsoft.com/en-us/library/ms186858.aspx

    Sample:

    RESTORE DATABASE MyDatabase FROM DISK='C:\demo.bak' WITH NORECOVERY GO -- Restore the log backups. RESTORE LOG MyDatabase FROM DISK='C:\demo1.trn' WITH NORECOVERY GO -- standby DB not accessable RESTORE LOG MyDatabase FROM DISK='C:\demo2.trn' WITH STANDBY GO -- standby DB is now accessable readonly

    • RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.

    • RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.

    • RESTORE WITH STANDBY which leaves the database in read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 9, 2012 7:01 PM

All replies

  • if you're using SQL Server Enterprise Edition you can restore a log shipping database in standby mode and than you can access the database in readonly mode.

    during the time you're restoring transaction log, any users are disconnected from the standby database. Therefore you should somehow coordinate the process of restoring transaction log and the access to the standby database.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, July 9, 2012 11:13 AM
  • no you can t access the data from the database when it is in No Recovery Mode,

    In Log Shipping you can read the data from the database in StandBy Mode.

    You can move the database from  norecovery mode to stabndby mode, and vice versa

    But when  database is set to Recovery mode we can't  keep it backup NO Recovery/StandBy Mode. in Log shipping






    Ramesh Babu Vavilla MCTS,MSBI

    Monday, July 9, 2012 11:17 AM
  • Can you please write an example how you can switch that?

    Thank you very much!

    Monday, July 9, 2012 11:58 AM
  • Can you please write an example how you can switch that?

    Microsoft MSDN  / Technet web sites contains many samples:

    restore a database backup description: http://msdn.microsoft.com/en-us/library/ms177429.aspx

    restore syntax: http://msdn.microsoft.com/en-us/library/ms186858.aspx

    Sample:

    RESTORE DATABASE MyDatabase FROM DISK='C:\demo.bak' WITH NORECOVERY GO -- Restore the log backups. RESTORE LOG MyDatabase FROM DISK='C:\demo1.trn' WITH NORECOVERY GO -- standby DB not accessable RESTORE LOG MyDatabase FROM DISK='C:\demo2.trn' WITH STANDBY GO -- standby DB is now accessable readonly

    • RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.

    • RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.

    • RESTORE WITH STANDBY which leaves the database in read-only mode. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 9, 2012 7:01 PM