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
RodgerAre 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...
> > 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
>
>|||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:
> > 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
> >
> >
> >|||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...
> >
> > 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...
> > > > 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 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
> 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...
> > >
> > > 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...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >|||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...
> > 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...
> > > >
> > > > 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...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||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...
> 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...
> > > 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...
> > > > >
> > > > > 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...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >