Database Restore on a Linked Server

I have a log shipping mechanism which backs up database on the primary server
and then restores the database on a standby server, the database is restored
with the help of a stored procedure on the standby server executed from the
primary server, After this the tran log is restored on the standby server, I
get the following error when i execute the stored procedure for restoring the
tran log
File 'YRS_TMP_Data' was only partially restored by a database or file
restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Procedure restore_YRS_Log_backup, Line 5
RESTORE LOG is terminating abnormally.
Thanks
Rodger
Are you sure your full and/or differential backups where successfully
restored on your standby server?
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> I have a log shipping mechanism which backs up database on the primary
server
> and then restores the database on a standby server, the database is
restored
> with the help of a stored procedure on the standby server executed from
the
> primary server, After this the tran log is restored on the standby server,
I
> get the following error when i execute the stored procedure for restoring
the
> tran log
> File 'YRS_TMP_Data' was only partially restored by a database or file
> restore.
> The entire file must be successfully restored before applying the log.
> Server: Msg 3013, Level 16, State 1, Procedure restore_YRS_Log_backup,
Line 5
> RESTORE LOG is terminating abnormally.
> Thanks
> Rodger
|||Thanks greg
here is the command i execute to restore my initial database on the standby
server
and the error which i get, it is related to the linked server entry, after
which i try the second query for restoring my log.
exec yrswebnyc.master.dbo.restore_YRS_backup
go
[OLE/DB provider returned message: Timeout expired]
"Gregory A. Larsen" wrote:

> Are you sure your full and/or differential backups where successfully
> restored on your standby server?
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:ECA030C0-A162-4749-943D-035FB67A2AC9@.microsoft.com...
> server
> restored
> the
> I
> the
> Line 5
>
>
|||I'm guessing that your initial restore never happened, since you gota
timeout. Did you review the errorlog file on the yrswebnyc server to verify
that your linked server request did/didn't run your SP.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:5E99F9D7-E42F-4E5D-AD7A-E818E0C52CB3@.microsoft.com...
> Thanks greg
> here is the command i execute to restore my initial database on the
standby
> server
> and the error which i get, it is related to the linked server entry, after
> which i try the second query for restoring my log.
> exec yrswebnyc.master.dbo.restore_YRS_backup
> go
> [OLE/DB provider returned message: Timeout expired]
>
> "Gregory A. Larsen" wrote:
>
> ----
--
> ----
--[vbcol=seagreen]
from[vbcol=seagreen]
server,[vbcol=seagreen]
restoring[vbcol=seagreen]
|||Yes my original restore never happened it gives me the error after 10 min
[OLE/DB provider returned message: Timeout expired]
I execute the stored proc from primary server which restores database on
standby server, but i receive the error above, i am not sure if i have to
make changes in my linked server settings, maybe connection timeout or query
timeout. any sugesstion
"Gregory A. Larsen" wrote:

> I'm guessing that your initial restore never happened, since you gota
> timeout. Did you review the errorlog file on the yrswebnyc server to verify
> that your linked server request did/didn't run your SP.
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:5E99F9D7-E42F-4E5D-AD7A-E818E0C52CB3@.microsoft.com...
> standby
> --
> --
> from
> server,
> restoring
>
>
|||Are you able to do simple "SELECT" statements via your linked server? I
would verify that your linked server definition is in fact working.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:BC1B0F2A-21C9-46C1-A22B-B067030BADB7@.microsoft.com...
> Yes my original restore never happened it gives me the error after 10 min
> [OLE/DB provider returned message: Timeout expired]
> I execute the stored proc from primary server which restores database on
> standby server, but i receive the error above, i am not sure if i have to
> make changes in my linked server settings, maybe connection timeout or
query[vbcol=seagreen]
> timeout. any sugesstion
> "Gregory A. Larsen" wrote:
verify
> ----
--
> ----
--[vbcol=seagreen]
after[vbcol=seagreen]
successfully[vbcol=seagreen]
> ----
> ----
primary[vbcol=seagreen]
is[vbcol=seagreen]
file[vbcol=seagreen]
log.[vbcol=seagreen]
restore_YRS_Log_backup,[vbcol=seagreen]
|||Hi Gregory
The select works but the stored proc fails after running for 8 min, here is
the stored proc script and the command which i execute from standby is
exec yrswebnyc.master.dbo.restore_YRS_backup
and the error message i get when i execute the command from primary server
query analyzer is
[OLE/DB provider returned message: Timeout expired]
CREATE PROCEDURE restore_YRS_backup
AS
RESTORE DATABASE YRS
FROM yrs_prod2
WITH
DBO_ONLY,
REPLACE,
--STANDBY = 'F:\undo_yrs.ldf',
MOVE 'yrs_tmp_data' TO 'Y:\yrs.mdf',
MOVE 'yrs_tmp_log' TO 'E:\yrs_log.ldf'
WAITFOR DELAY '00:00:05'
GO
"Gregory A. Larsen" wrote:

> Are you able to do simple "SELECT" statements via your linked server? I
> would verify that your linked server definition is in fact working.
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Rodger" <Rodger@.discussions.microsoft.com> wrote in message
> news:BC1B0F2A-21C9-46C1-A22B-B067030BADB7@.microsoft.com...
> query
> verify
> --
> --
> after
> successfully
> primary
> is
> file
> log.
> restore_YRS_Log_backup,
>
>
|||Perhaps you are just experiencing a timeout, i.e. the restore takes longer time than the time out
period for a query on a remote server? I believe that there are some timeout settings for linked
servers in sp_configure and possibly also for the linked server setup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rodger" <Rodger@.discussions.microsoft.com> wrote in message
news:C81B7DFA-5B19-416A-806F-64729C030387@.microsoft.com...[vbcol=seagreen]
> Hi Gregory
> The select works but the stored proc fails after running for 8 min, here is
> the stored proc script and the command which i execute from standby is
> exec yrswebnyc.master.dbo.restore_YRS_backup
> and the error message i get when i execute the command from primary server
> query analyzer is
> [OLE/DB provider returned message: Timeout expired]
>
>
> CREATE PROCEDURE restore_YRS_backup
> AS
> RESTORE DATABASE YRS
> FROM yrs_prod2
> WITH
> DBO_ONLY,
> REPLACE,
> --STANDBY = 'F:\undo_yrs.ldf',
> MOVE 'yrs_tmp_data' TO 'Y:\yrs.mdf',
> MOVE 'yrs_tmp_log' TO 'E:\yrs_log.ldf'
> WAITFOR DELAY '00:00:05'
> GO
> "Gregory A. Larsen" wrote: