|
sponsored links |
|
|
sponsored links
|
|
1
28th February 11:34
External User
Posts: 1
|
RESTORE LOG
If you do all of your restores WITH STANDBY, you can then have read-only access to the database and have the ability to restore more logs.
-- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql Herez the scenario .. I restored a db by selecting option 'Leave database nonoperational but able to restore additional transaction logs' After restore, I try applying transaction log using RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_01.TRN' WITH NORECOVERY After applying the above RESTORE , my db is in LOADING status until i apply my last transaction log RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH RECOVERY After the last RESTORE statement using WITH RECOVERY , I am not able to apply additional LOGS. Is there any way I can access the database in between of my restores to check the status before applying the last RESTORE statement using WITH RECOVERY ?? Is there any option to continue applying transaction LOG after using WITH RECOVERY when tried I am getting message, "Server: Msg 4306, Level 16, State 2, Line 1 The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally. Thx Sh |
|
|
|
2
28th February 11:34
External User
Posts: 1
|
RESTORE LOG
You could use WITH STANDBY instead of WITH NORECOVERY.
WITH STANDBY will allow you to read data (but not modify) inbetween restores. -- HTH, Vyas, MVP (SQL Server) http://vyaskn.tripod.com/ What hardware is your SQL Server running on? http://vyaskn.tripod.com/poll.htm SQL 7.0 Herez the scenario .. I restored a db by selecting option 'Leave database nonoperational but able to restore additional transaction logs' After restore, I try applying transaction log using RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_01.TRN' WITH NORECOVERY After applying the above RESTORE , my db is in LOADING status until i apply my last transaction log RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH RECOVERY After the last RESTORE statement using WITH RECOVERY , I am not able to apply additional LOGS. Is there any way I can access the database in between of my restores to check the status before applying the last RESTORE statement using WITH RECOVERY ?? Is there any option to continue applying transaction LOG after using WITH RECOVERY when tried I am getting message, "Server: Msg 4306, Level 16, State 2, Line 1 The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally. Thx Sh |
|
|
|
3
28th February 11:34
External User
Posts: 1
|
RESTORE LOG
Thanks for the reply.
When I tried RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH STANDBY I am getting an error message , Server: Msg 155, Level 15, State 1, Line 3 'STANDBY' is not a recognized RESTORE option. I checked in help files, it says - STANDBY = undo_file_name do I have to give my log file name after STANDBY ?? Pls explain Also ,do I need to restore complete backup using STANDBY option , before applying transcaction log in STANDBY Thanks Sh |
|
|
|
4
28th February 11:34
External User
Posts: 1
|
RESTORE LOG
When you use STANDBY, you also need to specify a filename for the file into which it will store information for (temporarily) rolling back transactions. When you go to restore the next log, it looks in this file and rolls forward (sort of) the transactions that were previously rolled back. This puts the database in a position to receive the next log. So, you should have something like:
RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH STANDBY = 'C:\Temp\MYDB.tmp' -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql When I tried RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH STANDBY I am getting an error message , Server: Msg 155, Level 15, State 1, Line 3 'STANDBY' is not a recognized RESTORE option. I checked in help files, it says - STANDBY = undo_file_name do I have to give my log file name after STANDBY ?? Pls explain Also ,do I need to restore complete backup using STANDBY option , before applying transcaction log in STANDBY Thanks Sh "Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message news:%23zUVp67dDHA.3592@tk2msftngp13.phx.gbl... |
|
|
|
5
28th February 11:34
External User
Posts: 1
|
RESTORE LOG
Thanks !!! It worked.
After applying all the transaction logs in STANDBY mode, do I have to apply with RECOVERY option ?? as this option wont allow applying further transaction log.. RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH STANDBY = 'C:\Temp\MYDB.tmp' -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql "Shamim" <shamim.abdul@railamerica.com> wrote in message news:OVrouA8dDHA.1588@TK2MSFTNGP12.phx.gbl... Thanks for the reply. When I tried RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH STANDBY I am getting an error message , Server: Msg 155, Level 15, State 1, Line 3 'STANDBY' is not a recognized RESTORE option. I checked in help files, it says - STANDBY = undo_file_name do I have to give my log file name after STANDBY ?? Pls explain Also ,do I need to restore complete backup using STANDBY option , before applying transcaction log in STANDBY Thanks Sh "Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message news:%23zUVp67dDHA.3592@tk2msftngp13.phx.gbl... |
|
|
|
6
28th February 11:34
External User
Posts: 1
|
RESTORE LOG
You do that with the last log or - if you have already restored the last log with STANDBY, just go with:
RESTORE DATABASE MYDB WITH RECOVERY -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql .. After applying all the transaction logs in STANDBY mode, do I have to apply with RECOVERY option ?? as this option wont allow applying further transaction log.. "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OhAKUE8dDHA.748@TK2MSFTNGP10.phx.gbl... When you use STANDBY, you also need to specify a filename for the file into which it will store information for (temporarily) rolling back transactions. When you go to restore the next log, it looks in this file and rolls forward (sort of) the transactions that were previously rolled back. This puts the database in a position to receive the next log. So, you should have something like: RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH STANDBY = 'C:\Temp\MYDB.tmp' -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql "Shamim" <shamim.abdul@railamerica.com> wrote in message news:OVrouA8dDHA.1588@TK2MSFTNGP12.phx.gbl... Thanks for the reply. When I tried RESTORE LOG MYDB FROM DISK = 'D:\MSSQL7DBfiles\MYDB_TR090903_09.TRN' WITH STANDBY I am getting an error message , Server: Msg 155, Level 15, State 1, Line 3 'STANDBY' is not a recognized RESTORE option. I checked in help files, it says - STANDBY = undo_file_name do I have to give my log file name after STANDBY ?? Pls explain Also ,do I need to restore complete backup using STANDBY option , before applying transcaction log in STANDBY Thanks Sh "Narayana Vyas Kondreddi" <answer_me@hotmail.com> wrote in message news:%23zUVp67dDHA.3592@tk2msftngp13.phx.gbl... |
|
|
Linear Mode
