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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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:

Database Restore Causes Log Full

Hi,
I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
size is about 5 GB.
I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is set
to Full.
As I don't have off-peak hours, I can not detach/attach database and move it
to SQL2. once a day, I need to move a full backup from SQL1 to SQL2. There's
no problem in backup and copying it to SQL2, but when I want to restore it on
SQL2, restore progress starts and finishes (as I see in sql logs that restore
is finished successfuly) but in "recovery is checkpointing database ..."
step, my database log file starts to grow, it grows until my log become full
and sql get into errors.
in this stage, I can not "backup log" (probably because it's in checkpoiting
process).
Also I tried to "backup and shrink log" in SQL1 before Full Backup, to have
minimum log size, but still after restoring on SQL1, when restore progress
bar is still visible and it's on 100% position, log file on SQL2 starts to
grow...
I suspect that the problem might be because of Full Recovery Model in SQL1
during Full Backup, but because of T-Logs I need this recovery model.
How can I restore my full backup in this case?!!
Best Regards,
Pierre.
Hi
http://www.sql-server-performance.co...g_shipping.asp
"Pierre" <Pierre@.discussions.microsoft.com> wrote in message
news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> Hi,
> I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> size is about 5 GB.
> I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> set
> to Full.
> As I don't have off-peak hours, I can not detach/attach database and move
> it
> to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> There's
> no problem in backup and copying it to SQL2, but when I want to restore it
> on
> SQL2, restore progress starts and finishes (as I see in sql logs that
> restore
> is finished successfuly) but in "recovery is checkpointing database ..."
> step, my database log file starts to grow, it grows until my log become
> full
> and sql get into errors.
> in this stage, I can not "backup log" (probably because it's in
> checkpoiting
> process).
> Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> have
> minimum log size, but still after restoring on SQL1, when restore progress
> bar is still visible and it's on 100% position, log file on SQL2 starts to
> grow...
> I suspect that the problem might be because of Full Recovery Model in SQL1
> during Full Backup, but because of T-Logs I need this recovery model.
> How can I restore my full backup in this case?!!
> Best Regards,
> Pierre.
|||Hi Uri,
Thanks for URL, the restore query in this article was:
RESTORE DATABASE database_name
FROM DISK = 'g:\mssql7\backup\database_name\database_name_back up_device.bak'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'g:\mssql7\backup\database_name\undo_database_name .ldf',
MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
I checked it up, what option of this command will help me overcome this
problem?
What's difference between this command and restore wizard that I'm using
currently and get me to this problem?
Regards,
Pierre.
"Uri Dimant" wrote:

> Hi
> http://www.sql-server-performance.co...g_shipping.asp
>
>
>
> "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
>
>
|||Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
instead, so can then restore the transaction log.

> STANDBY = 'g:\mssql7\backup\database_name\undo_database_name .ldf',
...
NORECOVERY,
...
AMB
"Pierre" wrote:
[vbcol=seagreen]
> Hi Uri,
> Thanks for URL, the restore query in this article was:
> RESTORE DATABASE database_name
> FROM DISK = 'g:\mssql7\backup\database_name\database_name_back up_device.bak'
> WITH
> DBO_ONLY,
> REPLACE,
> STANDBY = 'g:\mssql7\backup\database_name\undo_database_name .ldf',
> MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> I checked it up, what option of this command will help me overcome this
> problem?
> What's difference between this command and restore wizard that I'm using
> currently and get me to this problem?
>
> Regards,
> Pierre.
> "Uri Dimant" wrote:
|||Dear Alejandro,
At this time, I just want to restore my full backup on secondary server
(which growing log file causes restore wizard to fail), then if with STANDBY
options restore succeeded, I'll use NONRECOVERY to be able to restore my
t-logs.
Regards,
Pierre.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
> instead, so can then restore the transaction log.
>
> ...
> NORECOVERY,
> ...
>
> AMB
> "Pierre" wrote:
|||Hi Again,
restore database db_name with standby ... was successfull and restore
compeleted (database became readonly). at this time I dont have any t-log
backups, and I want to bring this server online, as I suspect I should run
"restore database db_name with recovery" , but when I run this query log file
starts to grow and will become full...
why running "with recovery" fills my log file?! How can I make my database
operational?
Regards,
Pierre.
"Pierre" wrote:
[vbcol=seagreen]
> Dear Alejandro,
> At this time, I just want to restore my full backup on secondary server
> (which growing log file causes restore wizard to fail), then if with STANDBY
> options restore succeeded, I'll use NONRECOVERY to be able to restore my
> t-logs.
> Regards,
> Pierre.
> "Alejandro Mesa" wrote:

Database Restore Causes Log Full

Hi,
I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
size is about 5 GB.
I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is set
to Full.
As I don't have off-peak hours, I can not detach/attach database and move it
to SQL2. once a day, I need to move a full backup from SQL1 to SQL2. There's
no problem in backup and copying it to SQL2, but when I want to restore it o
n
SQL2, restore progress starts and finishes (as I see in sql logs that restor
e
is finished successfuly) but in "recovery is checkpointing database ..."
step, my database log file starts to grow, it grows until my log become full
and sql get into errors.
in this stage, I can not "backup log" (probably because it's in checkpoiting
process).
Also I tried to "backup and shrink log" in SQL1 before Full Backup, to have
minimum log size, but still after restoring on SQL1, when restore progress
bar is still visible and it's on 100% position, log file on SQL2 starts to
grow...
I suspect that the problem might be because of Full Recovery Model in SQL1
during Full Backup, but because of T-Logs I need this recovery model.
How can I restore my full backup in this case?!!
Best Regards,
Pierre.Hi
http://www.sql-server-performance.c...og_shipping.asp
"Pierre" <Pierre@.discussions.microsoft.com> wrote in message
news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> Hi,
> I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> size is about 5 GB.
> I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> set
> to Full.
> As I don't have off-peak hours, I can not detach/attach database and move
> it
> to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> There's
> no problem in backup and copying it to SQL2, but when I want to restore it
> on
> SQL2, restore progress starts and finishes (as I see in sql logs that
> restore
> is finished successfuly) but in "recovery is checkpointing database ..."
> step, my database log file starts to grow, it grows until my log become
> full
> and sql get into errors.
> in this stage, I can not "backup log" (probably because it's in
> checkpoiting
> process).
> Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> have
> minimum log size, but still after restoring on SQL1, when restore progress
> bar is still visible and it's on 100% position, log file on SQL2 starts to
> grow...
> I suspect that the problem might be because of Full Recovery Model in SQL1
> during Full Backup, but because of T-Logs I need this recovery model.
> How can I restore my full backup in this case?!!
> Best Regards,
> Pierre.|||Hi Uri,
Thanks for URL, the restore query in this article was:
RESTORE DATABASE database_name
FROM DISK = 'g:\mssql7\backup\database_name\database
_name_backup_device.bak'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'g:\mssql7\backup\database_name\undo_dat
abase_name.ldf',
MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
I checked it up, what option of this command will help me overcome this
problem?
What's difference between this command and restore wizard that I'm using
currently and get me to this problem?
Regards,
Pierre.
"Uri Dimant" wrote:

> Hi
> http://www.sql-server-performance.c...og_shipping.asp
>
>
>
> "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
>
>|||Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
instead, so can then restore the transaction log.

> STANDBY = 'g:\mssql7\backup\database_name\undo_dat
abase_name.ldf',
...
NORECOVERY,
...
AMB
"Pierre" wrote:
[vbcol=seagreen]
> Hi Uri,
> Thanks for URL, the restore query in this article was:
> RESTORE DATABASE database_name
> FROM DISK = 'g:\mssql7\backup\database_name\database
_name_backup_device.ba
k'
> WITH
> DBO_ONLY,
> REPLACE,
> STANDBY = 'g:\mssql7\backup\database_name\undo_dat
abase_name.ldf',
> MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> I checked it up, what option of this command will help me overcome this
> problem?
> What's difference between this command and restore wizard that I'm using
> currently and get me to this problem?
>
> Regards,
> Pierre.
> "Uri Dimant" wrote:
>|||Dear Alejandro,
At this time, I just want to restore my full backup on secondary server
(which growing log file causes restore wizard to fail), then if with STANDBY
options restore succeeded, I'll use NONRECOVERY to be able to restore my
t-logs.
Regards,
Pierre.
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
> instead, so can then restore the transaction log.
>
> ...
> NORECOVERY,
> ...
>
> AMB
> "Pierre" wrote:
>|||Hi Again,
restore database db_name with standby ... was successfull and restore
compeleted (database became readonly). at this time I dont have any t-log
backups, and I want to bring this server online, as I suspect I should run
"restore database db_name with recovery" , but when I run this query log fil
e
starts to grow and will become full...
why running "with recovery" fills my log file?! How can I make my database
operational?
Regards,
Pierre.
"Pierre" wrote:
[vbcol=seagreen]
> Dear Alejandro,
> At this time, I just want to restore my full backup on secondary server
> (which growing log file causes restore wizard to fail), then if with STAND
BY
> options restore succeeded, I'll use NONRECOVERY to be able to restore my
> t-logs.
> Regards,
> Pierre.
> "Alejandro Mesa" wrote:
>

Database Restore Causes Log Full

Hi,
I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
size is about 5 GB.
I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is set
to Full.
As I don't have off-peak hours, I can not detach/attach database and move it
to SQL2. once a day, I need to move a full backup from SQL1 to SQL2. There's
no problem in backup and copying it to SQL2, but when I want to restore it on
SQL2, restore progress starts and finishes (as I see in sql logs that restore
is finished successfuly) but in "recovery is checkpointing database ..."
step, my database log file starts to grow, it grows until my log become full
and sql get into errors.
in this stage, I can not "backup log" (probably because it's in checkpoiting
process).
Also I tried to "backup and shrink log" in SQL1 before Full Backup, to have
minimum log size, but still after restoring on SQL1, when restore progress
bar is still visible and it's on 100% position, log file on SQL2 starts to
grow...
I suspect that the problem might be because of Full Recovery Model in SQL1
during Full Backup, but because of T-Logs I need this recovery model.
How can I restore my full backup in this case?!!
Best Regards,
Pierre.Hi
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Pierre" <Pierre@.discussions.microsoft.com> wrote in message
news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> Hi,
> I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> size is about 5 GB.
> I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> set
> to Full.
> As I don't have off-peak hours, I can not detach/attach database and move
> it
> to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> There's
> no problem in backup and copying it to SQL2, but when I want to restore it
> on
> SQL2, restore progress starts and finishes (as I see in sql logs that
> restore
> is finished successfuly) but in "recovery is checkpointing database ..."
> step, my database log file starts to grow, it grows until my log become
> full
> and sql get into errors.
> in this stage, I can not "backup log" (probably because it's in
> checkpoiting
> process).
> Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> have
> minimum log size, but still after restoring on SQL1, when restore progress
> bar is still visible and it's on 100% position, log file on SQL2 starts to
> grow...
> I suspect that the problem might be because of Full Recovery Model in SQL1
> during Full Backup, but because of T-Logs I need this recovery model.
> How can I restore my full backup in this case?!!
> Best Regards,
> Pierre.|||Hi Uri,
Thanks for URL, the restore query in this article was:
RESTORE DATABASE database_name
FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
I checked it up, what option of this command will help me overcome this
problem?
What's difference between this command and restore wizard that I'm using
currently and get me to this problem?
Regards,
Pierre.
"Uri Dimant" wrote:
> Hi
> http://www.sql-server-performance.com/sql_server_log_shipping.asp
>
>
>
> "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > Hi,
> >
> > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > size is about 5 GB.
> > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > set
> > to Full.
> > As I don't have off-peak hours, I can not detach/attach database and move
> > it
> > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > There's
> > no problem in backup and copying it to SQL2, but when I want to restore it
> > on
> > SQL2, restore progress starts and finishes (as I see in sql logs that
> > restore
> > is finished successfuly) but in "recovery is checkpointing database ..."
> > step, my database log file starts to grow, it grows until my log become
> > full
> > and sql get into errors.
> > in this stage, I can not "backup log" (probably because it's in
> > checkpoiting
> > process).
> > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > have
> > minimum log size, but still after restoring on SQL1, when restore progress
> > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > grow...
> >
> > I suspect that the problem might be because of Full Recovery Model in SQL1
> > during Full Backup, but because of T-Logs I need this recovery model.
> >
> > How can I restore my full backup in this case?!!
> >
> > Best Regards,
> > Pierre.
>
>|||Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
instead, so can then restore the transaction log.
> STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
...
NORECOVERY,
...
AMB
"Pierre" wrote:
> Hi Uri,
> Thanks for URL, the restore query in this article was:
> RESTORE DATABASE database_name
> FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
> WITH
> DBO_ONLY,
> REPLACE,
> STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> I checked it up, what option of this command will help me overcome this
> problem?
> What's difference between this command and restore wizard that I'm using
> currently and get me to this problem?
>
> Regards,
> Pierre.
> "Uri Dimant" wrote:
> > Hi
> > http://www.sql-server-performance.com/sql_server_log_shipping.asp
> >
> >
> >
> >
> >
> >
> > "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> > news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > > Hi,
> > >
> > > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > > size is about 5 GB.
> > > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > > set
> > > to Full.
> > > As I don't have off-peak hours, I can not detach/attach database and move
> > > it
> > > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > > There's
> > > no problem in backup and copying it to SQL2, but when I want to restore it
> > > on
> > > SQL2, restore progress starts and finishes (as I see in sql logs that
> > > restore
> > > is finished successfuly) but in "recovery is checkpointing database ..."
> > > step, my database log file starts to grow, it grows until my log become
> > > full
> > > and sql get into errors.
> > > in this stage, I can not "backup log" (probably because it's in
> > > checkpoiting
> > > process).
> > > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > > have
> > > minimum log size, but still after restoring on SQL1, when restore progress
> > > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > > grow...
> > >
> > > I suspect that the problem might be because of Full Recovery Model in SQL1
> > > during Full Backup, but because of T-Logs I need this recovery model.
> > >
> > > How can I restore my full backup in this case?!!
> > >
> > > Best Regards,
> > > Pierre.
> >
> >
> >|||Dear Alejandro,
At this time, I just want to restore my full backup on secondary server
(which growing log file causes restore wizard to fail), then if with STANDBY
options restore succeeded, I'll use NONRECOVERY to be able to restore my
t-logs.
Regards,
Pierre.
"Alejandro Mesa" wrote:
> Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
> instead, so can then restore the transaction log.
> > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> ...
> NORECOVERY,
> ...
>
> AMB
> "Pierre" wrote:
> > Hi Uri,
> >
> > Thanks for URL, the restore query in this article was:
> >
> > RESTORE DATABASE database_name
> > FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
> > WITH
> > DBO_ONLY,
> > REPLACE,
> > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> > MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> > MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> >
> > I checked it up, what option of this command will help me overcome this
> > problem?
> >
> > What's difference between this command and restore wizard that I'm using
> > currently and get me to this problem?
> >
> >
> > Regards,
> > Pierre.
> >
> > "Uri Dimant" wrote:
> >
> > > Hi
> > > http://www.sql-server-performance.com/sql_server_log_shipping.asp
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> > > news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > > > Hi,
> > > >
> > > > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > > > size is about 5 GB.
> > > > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > > > set
> > > > to Full.
> > > > As I don't have off-peak hours, I can not detach/attach database and move
> > > > it
> > > > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > > > There's
> > > > no problem in backup and copying it to SQL2, but when I want to restore it
> > > > on
> > > > SQL2, restore progress starts and finishes (as I see in sql logs that
> > > > restore
> > > > is finished successfuly) but in "recovery is checkpointing database ..."
> > > > step, my database log file starts to grow, it grows until my log become
> > > > full
> > > > and sql get into errors.
> > > > in this stage, I can not "backup log" (probably because it's in
> > > > checkpoiting
> > > > process).
> > > > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > > > have
> > > > minimum log size, but still after restoring on SQL1, when restore progress
> > > > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > > > grow...
> > > >
> > > > I suspect that the problem might be because of Full Recovery Model in SQL1
> > > > during Full Backup, but because of T-Logs I need this recovery model.
> > > >
> > > > How can I restore my full backup in this case?!!
> > > >
> > > > Best Regards,
> > > > Pierre.
> > >
> > >
> > >|||Hi Again,
restore database db_name with standby ... was successfull and restore
compeleted (database became readonly). at this time I dont have any t-log
backups, and I want to bring this server online, as I suspect I should run
"restore database db_name with recovery" , but when I run this query log file
starts to grow and will become full...
why running "with recovery" fills my log file?! How can I make my database
operational?
Regards,
Pierre.
"Pierre" wrote:
> Dear Alejandro,
> At this time, I just want to restore my full backup on secondary server
> (which growing log file causes restore wizard to fail), then if with STANDBY
> options restore succeeded, I'll use NONRECOVERY to be able to restore my
> t-logs.
> Regards,
> Pierre.
> "Alejandro Mesa" wrote:
> > Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
> > instead, so can then restore the transaction log.
> >
> > > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> >
> > ...
> > NORECOVERY,
> > ...
> >
> >
> > AMB
> >
> > "Pierre" wrote:
> >
> > > Hi Uri,
> > >
> > > Thanks for URL, the restore query in this article was:
> > >
> > > RESTORE DATABASE database_name
> > > FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
> > > WITH
> > > DBO_ONLY,
> > > REPLACE,
> > > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> > > MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> > > MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> > >
> > > I checked it up, what option of this command will help me overcome this
> > > problem?
> > >
> > > What's difference between this command and restore wizard that I'm using
> > > currently and get me to this problem?
> > >
> > >
> > > Regards,
> > > Pierre.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Hi
> > > > http://www.sql-server-performance.com/sql_server_log_shipping.asp
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> > > > news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > > > > size is about 5 GB.
> > > > > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > > > > set
> > > > > to Full.
> > > > > As I don't have off-peak hours, I can not detach/attach database and move
> > > > > it
> > > > > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > > > > There's
> > > > > no problem in backup and copying it to SQL2, but when I want to restore it
> > > > > on
> > > > > SQL2, restore progress starts and finishes (as I see in sql logs that
> > > > > restore
> > > > > is finished successfuly) but in "recovery is checkpointing database ..."
> > > > > step, my database log file starts to grow, it grows until my log become
> > > > > full
> > > > > and sql get into errors.
> > > > > in this stage, I can not "backup log" (probably because it's in
> > > > > checkpoiting
> > > > > process).
> > > > > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > > > > have
> > > > > minimum log size, but still after restoring on SQL1, when restore progress
> > > > > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > > > > grow...
> > > > >
> > > > > I suspect that the problem might be because of Full Recovery Model in SQL1
> > > > > during Full Backup, but because of T-Logs I need this recovery model.
> > > > >
> > > > > How can I restore my full backup in this case?!!
> > > > >
> > > > > Best Regards,
> > > > > Pierre.
> > > >
> > > >
> > > >

Database Replication SQL 2000

I have two SQL 2000 servers. I want to make the primary and one the
secondary. I want to databases to stay replicated. If the Primary goes down
the Secondary would be used while we fix the Primary. Once the the Primary
is fixed it would be brought back online and the Secondary would replicate
it's data to the Primary. Then they would be in sync again.
What are my options here? What is the recomended way to accomplish this?
Thanks,
Scott
Scott,
there are many threads (and opinions!) on this sort of thing over the last
few months. I would recommend looking at queued updating subscribers
provided you aren't using BLOB datatypes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use Transaction Replication with updating Subscriber or use Log Shipping.

Database Replication

I'm a newbie so bare with me. If I have two servers running SQL Server, a primary and a secondary, how can I make sure that both databases (One on each server) are always syncronized? In our situation the secondary server is a hot backup in case the primary fails. If the primary fails then data gets written to the secondary. When the primary comes back up then its database needs to be synced with the secondary. Can anyone tell me how to do this?Have a look on books online about Transactional Replication...

This is a form of replication that will make your two server in sync.

Any changes that are made on one server are automatically replicated over to your other server.

http://www.databasejournal.com/features/mssql/article.php/1438201

http://www.sql-server-performance.com/transactional_replication.asp

Hope this helps mate|||Thanks for the links! Do you know if you are doing transactional replication, if the other server is down, will the server que the transactions and keep trying to send them until it succeees?|||no,

As far as I know it will publish on an all or nothing basis.

If your primary server goes down then replication will stop because it has no server to replicate to (i.e no subscribers)

This is the time you flip the primary to your hot spare and fix the original server. After you have sorted the problem on the original server just kick off replication again.

Cheers|||Why don't you create a 2 node cluster?|||Not using Enterprise. I don't think it is available on standard.|||You're right, Only on Enterprize.

database problem

i have 2 database tables and want the primary key from table 1 to appear in table 2. The tables are connected to two web forms, so in other words, the user enters personal details into form1 which is sent to table1..then the user enters company details into form 2 which is sent to table 2, the trouble is, is that at the moment there is no way of telling which personal details relate to which company details...

any ideas?

When you direct users to form 2, send a parameter: Response.Redirect("Form2.aspx?ID=" + ID.ToString())

Alternately, if users login, then you have some way to identify which user is logged in (User.Identity.Name will likely have some useful information on who the user is). Another alternative is to use a session variable.

|||

ok, how would that ...Response.Redirect("Form2.aspx?ID=" + ID.ToString())...store it in database table 2?

i dont understand how this works