 | |
STANDBY mode
microsoft.public.sqlserver.server microsoft.public.sqlserver.server
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: STANDBY mode -
02-08-2010
, 03:23 PM
|
Are you asking of one can get to a database restore using NORECOVERY? If so, the answer is no. That is what STANDBY is for (or, of course, RECOVERY). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OlHuqqPqKHA.1144 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thanks Tibor, Using NORECOVERY mode, is it possbile to run some query to simple check the secondary data, for example, how many records in clients table compare with primary database. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:%23e$dsLJqKHA.5696 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is something strange going on here. You say you do STANDBY with below and undo file name: STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' But the error message from SQL Server tells a very different story. According to the error message, the prior restore was done with below as standby file: \\X-Database\SecondaryData\Logs\x_20100205054523.tuf' And, according to the error message, above file doesn't exist. So, the process seems messed up. Or perhaps above is the standby file for a following log restore? Ah, yes, I now see that in the error message. So, probably the first log restore using the standby file (the one you created) was OK. But a following log restore failed, because a prior standby file was inaccessible (network drive not available, somebody deleted the file or something like that). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OCJa4XnpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Thanks Tibor, I restore as: RESTORE DATABASE cashx FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.ba k' WITH STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' The old log files system will handle: Skipped log backup file. Secondary DB: xxx and the database is Standby/Read-Only, but store log failed: Message 2010-02-05 00:45:26.50 *** Error: Could not apply log backup file '\\Database\SecondaryData\Logs\x_20100205011501.tr n' to secondary database 'cashx'.(Microsoft.SqlServer.Management.LogShippin g) *** 2010-02-05 00:45:26.50 *** Error: During startup of warm standby database 'x' (database ID 7), its standby file ('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'x', file 'x_Data' on file 1. Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient Data Provider) *** I checked, I don't have tuf file. Can you please help here? "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:OBZrblRpKHA.4348 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Use the STANDBY option of the RESTORE command (along with a file to be created by the restore process where to keep the info needed in order to undo the UNDO performed by the recovery process). Or, you can also use NORECOVERY. What you can't use is ECOVERY (or none of these options). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:ugSsJMRpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Hi Tibor, How to put database in standby mode when restoring from a full back? Thanks. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:uGm2jmQpKHA.3748 (AT) TK2MSFTNGP02 (DOT) phx.gbl... You can't. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:#ww1cVQpKHA.5224 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... |
#12
| |||
| |||
|
|
Thansk Tibor, After swith with RECOVERY, how to make it restoring, ready for restoring log again? I'm thinking sometimes need to check the data integrity. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:u59TN9PqKHA.4284 (AT) TK2MSFTNGP04 (DOT) phx.gbl... Are you asking of one can get to a database restore using NORECOVERY? If so, the answer is no. That is what STANDBY is for (or, of course, RECOVERY). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OlHuqqPqKHA.1144 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thanks Tibor, Using NORECOVERY mode, is it possbile to run some query to simple check the secondary data, for example, how many records in clients table compare with primary database. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:%23e$dsLJqKHA.5696 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is something strange going on here. You say you do STANDBY with below and undo file name: STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' But the error message from SQL Server tells a very different story. According to the error message, the prior restore was done with below as standby file: \\X-Database\SecondaryData\Logs\x_20100205054523.tuf' And, according to the error message, above file doesn't exist. So, the process seems messed up. Or perhaps above is the standby file for a following log restore? Ah, yes, I now see that in the error message. So, probably the first log restore using the standby file (the one you created) was OK. But a following log restore failed, because a prior standby file was inaccessible (network drive not available, somebody deleted the file or something like that). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OCJa4XnpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Thanks Tibor, I restore as: RESTORE DATABASE cashx FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.ba k' WITH STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' The old log files system will handle: Skipped log backup file. Secondary DB: xxx and the database is Standby/Read-Only, but store log failed: Message 2010-02-05 00:45:26.50 *** Error: Could not apply log backup file '\\Database\SecondaryData\Logs\x_20100205011501.tr n' to secondary database 'cashx'.(Microsoft.SqlServer.Management.LogShippin g) *** 2010-02-05 00:45:26.50 *** Error: During startup of warm standby database 'x' (database ID 7), its standby file ('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'x', file 'x_Data' on file 1. Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient Data Provider) *** I checked, I don't have tuf file. Can you please help here? "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:OBZrblRpKHA.4348 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Use the STANDBY option of the RESTORE command (along with a file to be created by the restore process where to keep the info needed in order to undo the UNDO performed by the recovery process). Or, you can also use NORECOVERY. What you can't use is ECOVERY (or none of these options). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:ugSsJMRpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Hi Tibor, How to put database in standby mode when restoring from a full back? Thanks. "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:uGm2jmQpKHA.3748 (AT) TK2MSFTNGP02 (DOT) phx.gbl... You can't. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:#ww1cVQpKHA.5224 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... |
#13
| |||
| |||
|
|
Once you have run RECOVERY, you cannot restore any more logs. It is exactly the purpose of STANDBY to let access the db to check values and integrity, and then to keep restoring logs. As Tibor has said already "That is what STANDBY is for"! -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com someone (AT) js (DOT) com> wrote in message news:unwL0TQqKHA.4492 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thansk Tibor, After swith with RECOVERY, how to make it restoring, ready for restoring log again? I'm thinking sometimes need to check the data integrity. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:u59TN9PqKHA.4284 (AT) TK2MSFTNGP04 (DOT) phx.gbl... Are you asking of one can get to a database restore using NORECOVERY? If so, the answer is no. That is what STANDBY is for (or, of course, RECOVERY). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OlHuqqPqKHA.1144 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thanks Tibor, Using NORECOVERY mode, is it possbile to run some query to simple check the secondary data, for example, how many records in clients table compare with primary database. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:%23e$dsLJqKHA.5696 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is something strange going on here. You say you do STANDBY with below and undo file name: STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' But the error message from SQL Server tells a very different story. According to the error message, the prior restore was done with below as standby file: \\X-Database\SecondaryData\Logs\x_20100205054523.tuf' And, according to the error message, above file doesn't exist. So, the process seems messed up. Or perhaps above is the standby file for a following log restore? Ah, yes, I now see that in the error message. So, probably the first log restore using the standby file (the one you created) was OK. But a following log restore failed, because a prior standby file was inaccessible (network drive not available, somebody deleted the file or something like that). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OCJa4XnpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Thanks Tibor, I restore as: RESTORE DATABASE cashx FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.ba k' WITH STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' The old log files system will handle: Skipped log backup file. Secondary DB: xxx and the database is Standby/Read-Only, but store log failed: Message 2010-02-05 00:45:26.50 *** Error: Could not apply log backup file '\\Database\SecondaryData\Logs\x_20100205011501.tr n' to secondary database 'cashx'.(Microsoft.SqlServer.Management.LogShippin g) *** 2010-02-05 00:45:26.50 *** Error: During startup of warm standby database 'x' (database ID 7), its standby file ('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'x', file 'x_Data' on file 1. Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient Data Provider) *** I checked, I don't have tuf file. Can you please help here? "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:OBZrblRpKHA.4348 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Use the STANDBY option of the RESTORE command (along with a file to be created by the restore process where to keep the info needed in order to undo the UNDO performed by the recovery process). Or, you can also use NORECOVERY. What you can't use is ECOVERY (or none of these options). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:ugSsJMRpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Hi Tibor, How to put database in standby mode when restoring from a full back? Thanks. "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:uGm2jmQpKHA.3748 (AT) TK2MSFTNGP02 (DOT) phx.gbl... You can't. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:#ww1cVQpKHA.5224 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... |
#14
| |||
| |||
|
|
I'm thinking put it to recovery and then apply next transation log file with NOCOVERY, can I do that? |
Quote:
|
I still can't make standby mode works, actually there are no tuf files in primary/secondary, it related to restore process, and also with nocovery mode, copy/restore works fine. |
Standby work just fine. Probably you are using some tool whcih constructs
the restore commands for you and possibly copies the undo file to some other
location - which is what is causing your problems. You need to troubleshoot
this tool - that is where the problem is, not with the RESTORE and STANDBY
process. Possibly the tool is the log shipping functionality which is
shipped with the product.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<someone (AT) js (DOT) com> wrote
Quote:
|
Thanks Kalen, I'm thinking put it to recovery and then apply next transation log file with NOCOVERY, can I do that? I still can't make standby mode works, actually there are no tuf files in primary/secondary, it related to restore process, and also with nocovery mode, copy/restore works fine. How to find out why and fix for stanndby mode? "Kalen Delaney" <please_reply_on_newsgroups (AT) domain (DOT) com> wrote in message news:37B55CFE-5C33-4E32-B960-E11F9062095A (AT) microsoft (DOT) com... Once you have run RECOVERY, you cannot restore any more logs. It is exactly the purpose of STANDBY to let access the db to check values and integrity, and then to keep restoring logs. As Tibor has said already "That is what STANDBY is for"! -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com someone (AT) js (DOT) com> wrote in message news:unwL0TQqKHA.4492 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thansk Tibor, After swith with RECOVERY, how to make it restoring, ready for restoring log again? I'm thinking sometimes need to check the data integrity. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:u59TN9PqKHA.4284 (AT) TK2MSFTNGP04 (DOT) phx.gbl... Are you asking of one can get to a database restore using NORECOVERY? If so, the answer is no. That is what STANDBY is for (or, of course, RECOVERY). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OlHuqqPqKHA.1144 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thanks Tibor, Using NORECOVERY mode, is it possbile to run some query to simple check the secondary data, for example, how many records in clients table compare with primary database. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:%23e$dsLJqKHA.5696 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is something strange going on here. You say you do STANDBY with below and undo file name: STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' But the error message from SQL Server tells a very different story. According to the error message, the prior restore was done with below as standby file: \\X-Database\SecondaryData\Logs\x_20100205054523.tuf' And, according to the error message, above file doesn't exist. So, the process seems messed up. Or perhaps above is the standby file for a following log restore? Ah, yes, I now see that in the error message. So, probably the first log restore using the standby file (the one you created) was OK. But a following log restore failed, because a prior standby file was inaccessible (network drive not available, somebody deleted the file or something like that). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OCJa4XnpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Thanks Tibor, I restore as: RESTORE DATABASE cashx FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.ba k' WITH STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' The old log files system will handle: Skipped log backup file. Secondary DB: xxx and the database is Standby/Read-Only, but store log failed: Message 2010-02-05 00:45:26.50 *** Error: Could not apply log backup file '\\Database\SecondaryData\Logs\x_20100205011501.tr n' to secondary database 'cashx'.(Microsoft.SqlServer.Management.LogShippin g) *** 2010-02-05 00:45:26.50 *** Error: During startup of warm standby database 'x' (database ID 7), its standby file ('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'x', file 'x_Data' on file 1. Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient Data Provider) *** I checked, I don't have tuf file. Can you please help here? "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:OBZrblRpKHA.4348 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Use the STANDBY option of the RESTORE command (along with a file to be created by the restore process where to keep the info needed in order to undo the UNDO performed by the recovery process). Or, you can also use NORECOVERY. What you can't use is ECOVERY (or none of these options). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:ugSsJMRpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Hi Tibor, How to put database in standby mode when restoring from a full back? Thanks. "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:uGm2jmQpKHA.3748 (AT) TK2MSFTNGP02 (DOT) phx.gbl... You can't. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:#ww1cVQpKHA.5224 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... |
#15
| |||
| |||
|
|
Once you have run RECOVERY, you cannot restore any more logs. |
Quote:
|
Once you have restored a log WITH RECOVERY, you cannot restore _any_ more logs WITH RECOVERY, WITH NORECOVERY or WITH STANDBY. Once you have restored a log WITH RECOVERY, you are done restoring! -- |
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com
<someone (AT) js (DOT) com> wrote
Quote:
|
Thanks Kalen, I'm thinking put it to recovery and then apply next transation log file with NOCOVERY, can I do that? I still can't make standby mode works, actually there are no tuf files in primary/secondary, it related to restore process, and also with nocovery mode, copy/restore works fine. How to find out why and fix for stanndby mode? "Kalen Delaney" <please_reply_on_newsgroups (AT) domain (DOT) com> wrote in message news:37B55CFE-5C33-4E32-B960-E11F9062095A (AT) microsoft (DOT) com... Once you have run RECOVERY, you cannot restore any more logs. It is exactly the purpose of STANDBY to let access the db to check values and integrity, and then to keep restoring logs. As Tibor has said already "That is what STANDBY is for"! -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com someone (AT) js (DOT) com> wrote in message news:unwL0TQqKHA.4492 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thansk Tibor, After swith with RECOVERY, how to make it restoring, ready for restoring log again? I'm thinking sometimes need to check the data integrity. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:u59TN9PqKHA.4284 (AT) TK2MSFTNGP04 (DOT) phx.gbl... Are you asking of one can get to a database restore using NORECOVERY? If so, the answer is no. That is what STANDBY is for (or, of course, RECOVERY). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OlHuqqPqKHA.1144 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thanks Tibor, Using NORECOVERY mode, is it possbile to run some query to simple check the secondary data, for example, how many records in clients table compare with primary database. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:%23e$dsLJqKHA.5696 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is something strange going on here. You say you do STANDBY with below and undo file name: STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' But the error message from SQL Server tells a very different story. According to the error message, the prior restore was done with below as standby file: \\X-Database\SecondaryData\Logs\x_20100205054523.tuf' And, according to the error message, above file doesn't exist. So, the process seems messed up. Or perhaps above is the standby file for a following log restore? Ah, yes, I now see that in the error message. So, probably the first log restore using the standby file (the one you created) was OK. But a following log restore failed, because a prior standby file was inaccessible (network drive not available, somebody deleted the file or something like that). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OCJa4XnpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Thanks Tibor, I restore as: RESTORE DATABASE cashx FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.ba k' WITH STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' The old log files system will handle: Skipped log backup file. Secondary DB: xxx and the database is Standby/Read-Only, but store log failed: Message 2010-02-05 00:45:26.50 *** Error: Could not apply log backup file '\\Database\SecondaryData\Logs\x_20100205011501.tr n' to secondary database 'cashx'.(Microsoft.SqlServer.Management.LogShippin g) *** 2010-02-05 00:45:26.50 *** Error: During startup of warm standby database 'x' (database ID 7), its standby file ('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'x', file 'x_Data' on file 1. Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient Data Provider) *** I checked, I don't have tuf file. Can you please help here? "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:OBZrblRpKHA.4348 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Use the STANDBY option of the RESTORE command (along with a file to be created by the restore process where to keep the info needed in order to undo the UNDO performed by the recovery process). Or, you can also use NORECOVERY. What you can't use is ECOVERY (or none of these options). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:ugSsJMRpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Hi Tibor, How to put database in standby mode when restoring from a full back? Thanks. "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:uGm2jmQpKHA.3748 (AT) TK2MSFTNGP02 (DOT) phx.gbl... You can't. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:#ww1cVQpKHA.5224 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... |
#16
| |||
| |||
|
|
I'm thinking put it to recovery and then apply next transation log file with NOCOVERY, can I do that? As already stated: No. I still can't make standby mode works, actually there are no tuf files in primary/secondary, it related to restore process, and also with nocovery mode, copy/restore works fine. I'm sorry, but above sentence is hard to make heads and tails out of. Standby work just fine. Probably you are using some tool whcih constructs the restore commands for you and possibly copies the undo file to some other location - which is what is causing your problems. You need to troubleshoot this tool - that is where the problem is, not with the RESTORE and STANDBY process. Possibly the tool is the log shipping functionality which is shipped with the product. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:eMtEmPaqKHA.4636 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Thanks Kalen, I'm thinking put it to recovery and then apply next transation log file with NOCOVERY, can I do that? I still can't make standby mode works, actually there are no tuf files in primary/secondary, it related to restore process, and also with nocovery mode, copy/restore works fine. How to find out why and fix for stanndby mode? "Kalen Delaney" <please_reply_on_newsgroups (AT) domain (DOT) com> wrote in message news:37B55CFE-5C33-4E32-B960-E11F9062095A (AT) microsoft (DOT) com... Once you have run RECOVERY, you cannot restore any more logs. It is exactly the purpose of STANDBY to let access the db to check values and integrity, and then to keep restoring logs. As Tibor has said already "That is what STANDBY is for"! -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com someone (AT) js (DOT) com> wrote in message news:unwL0TQqKHA.4492 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thansk Tibor, After swith with RECOVERY, how to make it restoring, ready for restoring log again? I'm thinking sometimes need to check the data integrity. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:u59TN9PqKHA.4284 (AT) TK2MSFTNGP04 (DOT) phx.gbl... Are you asking of one can get to a database restore using NORECOVERY? If so, the answer is no. That is what STANDBY is for (or, of course, RECOVERY). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OlHuqqPqKHA.1144 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Thanks Tibor, Using NORECOVERY mode, is it possbile to run some query to simple check the secondary data, for example, how many records in clients table compare with primary database. "Tibor Karaszi" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com wrote in message news:%23e$dsLJqKHA.5696 (AT) TK2MSFTNGP04 (DOT) phx.gbl... There is something strange going on here. You say you do STANDBY with below and undo file name: STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' But the error message from SQL Server tells a very different story. According to the error message, the prior restore was done with below as standby file: \\X-Database\SecondaryData\Logs\x_20100205054523.tuf' And, according to the error message, above file doesn't exist. So, the process seems messed up. Or perhaps above is the standby file for a following log restore? Ah, yes, I now see that in the error message. So, probably the first log restore using the standby file (the one you created) was OK. But a following log restore failed, because a prior standby file was inaccessible (network drive not available, somebody deleted the file or something like that). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:OCJa4XnpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Thanks Tibor, I restore as: RESTORE DATABASE cashx FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.ba k' WITH STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' The old log files system will handle: Skipped log backup file. Secondary DB: xxx and the database is Standby/Read-Only, but store log failed: Message 2010-02-05 00:45:26.50 *** Error: Could not apply log backup file '\\Database\SecondaryData\Logs\x_20100205011501.tr n' to secondary database 'cashx'.(Microsoft.SqlServer.Management.LogShippin g) *** 2010-02-05 00:45:26.50 *** Error: During startup of warm standby database 'x' (database ID 7), its standby file ('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'x', file 'x_Data' on file 1. Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient Data Provider) *** I checked, I don't have tuf file. Can you please help here? "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:OBZrblRpKHA.4348 (AT) TK2MSFTNGP06 (DOT) phx.gbl... Use the STANDBY option of the RESTORE command (along with a file to be created by the restore process where to keep the info needed in order to undo the UNDO performed by the recovery process). Or, you can also use NORECOVERY. What you can't use is ECOVERY (or none of these options). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:ugSsJMRpKHA.1548 (AT) TK2MSFTNGP02 (DOT) phx.gbl... Hi Tibor, How to put database in standby mode when restoring from a full back? Thanks. "Tibor Karaszi" tibor_please.no.email_karaszi (AT) h...OT) nomail.com wrote in message news:uGm2jmQpKHA.3748 (AT) TK2MSFTNGP02 (DOT) phx.gbl... You can't. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi someone (AT) js (DOT) com> wrote in message news:#ww1cVQpKHA.5224 (AT) TK2MSFTNGP05 (DOT) phx.gbl... Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... |
#17
| ||||||||||||||||
| ||||||||||||||||
|
|
On Wednesday, February 03, 2010 2:16 PM <someon wrote: Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... |
Quote:
|
On Wednesday, February 03, 2010 2:48 PM Tibor Karaszi wrote: You cannot. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi |
Quote:
|
On Wednesday, February 03, 2010 2:57 PM <someon wrote: Thanks Tibor, In this case, I am going to do a full backup and restore from there, but for restoring log, I have few days logs in folder, is it the SQL server will handle that or I have to sepcify from which log file to begin? |
Quote:
|
On Wednesday, February 03, 2010 3:05 PM Tibor Karaszi wrote: I do not know, I am afraid since I use my own home-brew code for log shipping. Easiest would be, methinks, to setup a test system a give it a spin. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi |
Quote:
|
On Wednesday, February 03, 2010 3:54 PM <someon wrote: Hi Tibor, How to put database in standby mode when restoring from a full back? Thanks. |
Quote:
|
On Wednesday, February 03, 2010 4:40 PM Tibor Karaszi wrote: Use the STANDBY option of the RESTORE command (along with a file to be created by the restore process where to keep the info needed in order to undo the UNDO performed by the recovery process). Or, you can also use NORECOVERY. What you cannot use is ECOVERY (or none of these options). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi |
Quote:
|
On Friday, February 05, 2010 10:14 AM <someon wrote: Thanks Tibor, I restore as: RESTORE DATABASE cashx FROM disk ='C:\Database\SecondaryData\DatabaseBackup\mydb.ba k' WITH STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' The old log files system will handle: Skipped log backup file. Secondary DB: xxx and the database is Standby/Read-Only, but store log failed: Message 2010-02-05 00:45:26.50 *** Error: Could not apply log backup file '\\Database\SecondaryData\Logs\x_20100205011501.tr n' to secondary database 'cashx'.(Microsoft.SqlServer.Management.LogShippin g) *** 2010-02-05 00:45:26.50 *** Error: During startup of warm standby database 'x' (database ID 7), its standby file ('\\X-Database\SecondaryData\Logs\x_20100205054523.tuf') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. RESTORE LOG is terminating abnormally. Processed 0 pages for database 'x', file 'x_Data' on file 1. Processed 841 pages for database 'x', file 'x_Log' on file 1.(.Net SqlClient Data Provider) *** I checked, I do not have tuf file. Can you please help here? going |
Quote:
|
On Monday, February 08, 2010 2:48 AM Tibor Karaszi wrote: There is something strange going on here. You say you do STANDBY with below and undo file name: STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterDatabase.BAK' But the error message from SQL Server tells a very different story. According to the error message, the prior restore was done with below as standby file: \\X-Database\SecondaryData\Logs\x_20100205054523.tuf' And, according to the error message, above file does not exist. So, the process seems messed up. Or perhaps above is the standby file for a following log restore? Ah, yes, I now see that in the error message. So, probably the first log restore using the standby file (the one you created) was OK. But a following log restore failed, because a prior standby file was inaccessible (network drive not available, somebody deleted the file or something like that). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi |
Quote:
|
On Monday, February 08, 2010 3:09 PM <someon wrote: Thanks Tibor, Using NORECOVERY mode, is it possbile to run some query to simple check the secondary data, for example, how many records in clients table compare with primary database. below STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData base.BAK' created) was STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData database database inaccessible to |
Quote:
|
On Monday, February 08, 2010 3:44 PM Tibor Karaszi wrote: Are you asking of one can get to a database restore using NORECOVERY? If so, the answer is no. That is what STANDBY is for (or, of course, RECOVERY). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi |
Quote:
|
On Monday, February 08, 2010 4:23 PM <someon wrote: Thansk Tibor, After swith with RECOVERY, how to make it restoring, ready for restoring log again? I am thinking sometimes need to check the data integrity. so, STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData as So, file STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData |
Quote:
|
On Monday, February 08, 2010 6:46 PM Kalen Delaney wrote: Once you have run RECOVERY, you cannot restore any more logs. It is exactly the purpose of STANDBY to let access the db to check values and integrity, and then to keep restoring logs. As Tibor has said already "That is what STANDBY is for"! -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com |
Quote:
|
On Tuesday, February 09, 2010 11:21 AM <someon wrote: Thanks Kalen, I am thinking put it to recovery and then apply next transation log file with NOCOVERY, can I do that? I still cannot make standby mode works, actually there are no tuf files in primary/secondary, it related to restore process, and also with nocovery mode, copy/restore works fine. How to find out why and fix for stanndby mode? restoring If RECOVERY). check compare with STANDBY='C:\Database\SecondaryData\DatabaseBackup\ ROLLBACK_UNDO_DisasterData below the |
Quote:
|
On Tuesday, February 09, 2010 11:31 AM Tibor Karaszi wrote: As already stated: No. I am sorry, but above sentence is hard to make heads and tails out of. Standby work just fine. Probably you are using some tool whcih constructs the restore commands for you and possibly copies the undo file to some other location - which is what is causing your problems. You need to troubleshoot this tool - that is where the problem is, not with the RESTORE and STANDBY process. Possibly the tool is the log shipping functionality which is shipped with the product. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi |
Quote:
|
On Tuesday, February 09, 2010 1:05 PM Kalen Delaney wrote: I thought this was pretty clear in my last answer: But apparently not. Let me try again: -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com |
Quote:
|
On Wednesday, February 10, 2010 1:44 PM <someon wrote: Thanks for the help. Works now. it is permission related. in other troubleshoot in values NORECOVERY? |
#18
| |||
| |||
|
|
I need to recover a full backup with standby...this is what I think to do: Pause all jobs of log shipping restore with standby restart the jobs this is the syntax but its not working: (Someone help me) RESTORE FILELISTONLY FROM DISK = '\\Server\DISKPATH\ERPiWacsInterfaceDB_Sunday.BAK' RESTORE DATABASE ERPiWacsInterfaceDB FROM DISK = '\\Server\DISKPATH\ERPiWacsInterfaceDB_Sunday.BAK' WITH STANDBY 'ERPiWacsInterfaceDB' TO 'E:\SQLData\ERPiWacsInterfaceDB.mdf', MOVE 'ERPiWacsInterfaceDB_log' TO 'E:\SQLLogs\ERPiWacsInterfaceDB_1.ldf' GO |
unexpected results? Something else?
You say want to want to recover a full backup with standby. What more
exactly is the background? Has the main database gone bellyup? Do you
need to resync the log-shipping mirror?
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.