Remove inactive entries from transaction log and log shipping

When I try to take a Transaction log backup of a database (Recovery model
set to Full), I get an option to truncate the transaction log by checking
"Remove inactive entries from transaction log" check box in the backup
dialog. This basically causes the inactive portion of the transaction log
(where transaction is completes, either rolled back are committed) to be
truncated. So this potentially leads to saving of huge amount of hard disk
space.
Now, when I setup log shipping is there any similar option I can set on the
primary database? After every transaction log backup I want inactive entries
from the transaction log to be removed. Is there any way to do this? If yes,
how?. If no, why is that so? Once the backup is taken inactive log entries
on the primary database are of no use to log ship, right?
Rathna RajThe option in the backup dialog is very misleading. It basically means that
EM does *not* send the WITH NO_TRUNCATE option. This option is designed for
doing an "emergency" backup in case the database is corrupted, and I have
communicated to MS that the dialog shouldn't be misleading in this way. The
normal way to do log backup is by not specifying this option (having the
checkbox checked (I believe), the default). If the log shipping was written
by a sane individual ;-), then it should not specify this option, I.e., you
already have the desired behavior. To be certain, use a Profiler trace to
see what is going on.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rathna Raj" <RathnaRajT@.icode.com> wrote in message
news:%2365JEWJyDHA.1272@.TK2MSFTNGP12.phx.gbl...
> When I try to take a Transaction log backup of a database (Recovery model
> set to Full), I get an option to truncate the transaction log by checking
> "Remove inactive entries from transaction log" check box in the backup
> dialog. This basically causes the inactive portion of the transaction log
> (where transaction is completes, either rolled back are committed) to be
> truncated. So this potentially leads to saving of huge amount of hard disk
> space.
> Now, when I setup log shipping is there any similar option I can set on
the
> primary database? After every transaction log backup I want inactive
entries
> from the transaction log to be removed. Is there any way to do this? If
yes,
> how?. If no, why is that so? Once the backup is taken inactive log entries
> on the primary database are of no use to log ship, right?
> Rathna Raj
>|||Hi Rathna,
Thanks for your post. Based on my research, log shipping does done backup
log part, so transaction log should not be large. Therefore, it seems that
truncating the transaction log of the primary server manually in log
shipping is not necessary.
For more information regarding log shipping, please refer to the following
article on SQL Server Books Online. Also, the example in this article will
help describe how the log shipping works.
Topic; "Log Shipping"
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql

RemoteDataAccess

Dear Friends,

To use RDA, do I still have to use Replication, Publishing? if yes which publication need to be used? is it Transaction

If you can guide me as to how I can imliment RDA, would be a great help.

Check the topic: Remote Data Access (RDA)

at http://msdn2.microsoft.com/en-us/library/aa257442(SQL.80).aspx

It also has examples in the Push/Pull etc methods.

|||

Thankx,

But what I need to know is if i need to use replication publishing of the SQL server db?

Is there any way that I can access the SQL server DB directly?

Regards

RemoteDataAccess

Dear Friends,

To use RDA, do I still have to use Replication, Publishing? if yes which publication need to be used? is it Transaction

If you can guide me as to how I can imliment RDA, would be a great help.

Check the topic: Remote Data Access (RDA)

at http://msdn2.microsoft.com/en-us/library/aa257442(SQL.80).aspx

It also has examples in the Push/Pull etc methods.

|||

Thankx,

But what I need to know is if i need to use replication publishing of the SQL server db?

Is there any way that I can access the SQL server DB directly?

Regards

remote transaction on SQL Server

To support remote transaction on SQL Server i have configured user
options to 16384. However if i make changes in other properties of SQL
Server, user option setting reverts to its original value. I would
like to know 1. Is it possible to protect this setting so that change
in other properties do not effect this setting? 2. What are the
properties of SQL Server that are linked with user option settings
(i.e. Changes in those properties cause reverting in user options)?> However if i make changes in other properties of SQL
> Server, user option setting reverts to its original value. I would
> like to know 1. Is it possible to protect this setting so that change
> in other properties do not effect this setting?

The 'user options' configuration option is a bitmask specification. You
need to perform a bitwise OR in order to leave the other options intact.
The script below will turn on the specified option on and retain the other
option settings.

DECLARE
@.run_value int,
@.new_run_value int
CREATE TABLE #UserOptions
(
name varchar(40) NOT NULL,
minimun int,
maximmun int,
config_value int,
run_value int
)
INSERT INTO #UserOptions
EXEC sp_configure 'user options'

SELECT @.new_run_value = run_value | 16384
FROM #UserOptions

EXEC sp_configure 'user options', @.new_run_value
RECONFIGURE WITH OVERRIDE
DROP TABLE #UserOptions
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"T.S.Negi" <tilak.negi@.mind-infotech.com> wrote in message
news:a1930058.0402152114.6d399d0c@.posting.google.c om...
> To support remote transaction on SQL Server i have configured user
> options to 16384. However if i make changes in other properties of SQL
> Server, user option setting reverts to its original value. I would
> like to know 1. Is it possible to protect this setting so that change
> in other properties do not effect this setting? 2. What are the
> properties of SQL Server that are linked with user option settings
> (i.e. Changes in those properties cause reverting in user options)?

Remote Transaction - ReleaseSchemaLock Failure

Any help or suggestion will be appreciated.
Attempting to perform a distributed transaction and the following is
returned:
Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
interface, but returned0x8004d00e for ReleaseSchemaLock .].
[OLE/DB provider returned message: Unspecified error]
The servers are (from spt_server_info):
Server1:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
Server2:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
The linked server is defined (using SQLOLEDB):
sp_helpserver 'hbd-mrg-qrm-t'
name
network_name
status
id collation_name
connect_timeout query_timeout
------
--
------
-- --
hbd-mrg-qrm-t
hbd-mrg-qrm-t
rpc,rpc out,data access,use remote collation
4 NULL
0 0
The code to initiate the transaction is:
begin distributed transaction
set XACT_ABORT ON
exec @.nError = ArchiveAppCopy
if @.nError = 0
The first DML statement, insert is failing within the procedure below:
CREATE Procedure dbo.ArchiveAppCopy as
declare @.nError int
set nocount on
begin tran
Set identity_insert AlertReminder on
if @.@.error <> 0 goto Commit_Label
alter table AlertReminder disable trigger all
if @.@.error <> 0 goto Commit_Label
Insert into AlertReminder (
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID)
Select
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID
>From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where
AppNumber in (select appnumber from TransferApps)
and not exists (select appnumber from AlertReminder where
AlertReminder.AlertReminderID = tblSource.AlertReminderID
)
An update:
Further testing has determined that any schema changes on the local
server will fail the MSDTC transaction.
The schema changes performed here were to disable/enable triggers and
set identity_inserts on/off before the mass copy.
Without any alternatives, I plan to move all the schema changes to
appropriate stored procecedures and call them outside of the
distributed transaction.
Does anyone know why the OLE/DB provider was returned 'Unknown' in the
below message? Other trace messages returned back 'SQLOLEDB', I wonder
if the server link is being broken with the ReleaseSchemaLock and that
failed the DTC.
Jochen.Markert@.fnf.com wrote:
> Any help or suggestion will be appreciated.
> Attempting to perform a distributed transaction and the following is
> returned:
> Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
> Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction.
> OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
> returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
> interface, but returned0x8004d00e for ReleaseSchemaLock .].
> [OLE/DB provider returned message: Unspecified error]
> The servers are (from spt_server_info):
> Server1:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> Server2:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> The linked server is defined (using SQLOLEDB):
> sp_helpserver 'hbd-mrg-qrm-t'
> name
> network_name
> status
> id collation_name
> connect_timeout query_timeout
> ------
> --
> ----
> --
> ------
> -- --
> hbd-mrg-qrm-t
> hbd-mrg-qrm-t
> rpc,rpc out,data access,use remote collation
> 4 NULL
> 0 0
>
> The code to initiate the transaction is:
> begin distributed transaction
> set XACT_ABORT ON
> exec @.nError = ArchiveAppCopy
> if @.nError = 0
>
> The first DML statement, insert is failing within the procedure below:
> CREATE Procedure dbo.ArchiveAppCopy as
> declare @.nError int
> set nocount on
> begin tran
> Set identity_insert AlertReminder on
> if @.@.error <> 0 goto Commit_Label
> alter table AlertReminder disable trigger all
> if @.@.error <> 0 goto Commit_Label
> Insert into AlertReminder (
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID)
> Select
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID
> AppNumber in (select appnumber from TransferApps)
> and not exists (select appnumber from AlertReminder where
> AlertReminder.AlertReminderID = tblSource.AlertReminderID
> )

Remote Transaction - ReleaseSchemaLock Failure

Any help or suggestion will be appreciated.
Attempting to perform a distributed transaction and the following is
returned:
Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
interface, but returned0x8004d00e for ReleaseSchemaLock .].
[OLE/DB provider returned message: Unspecified error]
The servers are (from spt_server_info):
Server1:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
Server2:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
The linked server is defined (using SQLOLEDB):
sp_helpserver 'hbd-mrg-qrm-t'
name
network_name
status
id collation_name
connect_timeout query_timeout
----
----
--
----
--
--
----
----
-- --
hbd-mrg-qrm-t
hbd-mrg-qrm-t
rpc,rpc out,data access,use remote collation
4 NULL
0 0
The code to initiate the transaction is:
begin distributed transaction
set XACT_ABORT ON
exec @.nError = ArchiveAppCopy
if @.nError = 0
The first DML statement, insert is failing within the procedure below:
CREATE Procedure dbo.ArchiveAppCopy as
declare @.nError int
set nocount on
begin tran
Set identity_insert AlertReminder on
if @.@.error <> 0 goto Commit_Label
alter table AlertReminder disable trigger all
if @.@.error <> 0 goto Commit_Label
Insert into AlertReminder (
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID)
Select
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID[vbcol=seagreen]
>From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where[
/vbcol]
AppNumber in (select appnumber from TransferApps)
and not exists (select appnumber from AlertReminder where
AlertReminder.AlertReminderID = tblSource.AlertReminderID
)An update:
Further testing has determined that any schema changes on the local
server will fail the MSDTC transaction.
The schema changes performed here were to disable/enable triggers and
set identity_inserts on/off before the mass copy.
Without any alternatives, I plan to move all the schema changes to
appropriate stored procecedures and call them outside of the
distributed transaction.
Does anyone know why the OLE/DB provider was returned 'Unknown' in the
below message? Other trace messages returned back 'SQLOLEDB', I wonder
if the server link is being broken with the ReleaseSchemaLock and that
failed the DTC.
Jochen.Markert@.fnf.com wrote:
> Any help or suggestion will be appreciated.
> Attempting to perform a distributed transaction and the following is
> returned:
> Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
> Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction.
> OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
> returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
> interface, but returned0x8004d00e for ReleaseSchemaLock .].
> [OLE/DB provider returned message: Unspecified error]
> The servers are (from spt_server_info):
> Server1:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> Server2:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> The linked server is defined (using SQLOLEDB):
> sp_helpserver 'hbd-mrg-qrm-t'
> name
> network_name
> status
> id collation_name
> connect_timeout query_timeout
> ----
---
> --
> ----
--
> --
> ----
---
> -- --
> hbd-mrg-qrm-t
> hbd-mrg-qrm-t
> rpc,rpc out,data access,use remote collation
> 4 NULL
> 0 0
>
> The code to initiate the transaction is:
> begin distributed transaction
> set XACT_ABORT ON
> exec @.nError = ArchiveAppCopy
> if @.nError = 0
>
> The first DML statement, insert is failing within the procedure below:
> CREATE Procedure dbo.ArchiveAppCopy as
> declare @.nError int
> set nocount on
> begin tran
> Set identity_insert AlertReminder on
> if @.@.error <> 0 goto Commit_Label
> alter table AlertReminder disable trigger all
> if @.@.error <> 0 goto Commit_Label
> Insert into AlertReminder (
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID)
> Select
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID
> AppNumber in (select appnumber from TransferApps)
> and not exists (select appnumber from AlertReminder where
> AlertReminder.AlertReminderID = tblSource.AlertReminderID
> )

Remote Transaction - ReleaseSchemaLock Failure

Any help or suggestion will be appreciated.
Attempting to perform a distributed transaction and the following is
returned:
Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction.
OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
interface, but returned0x8004d00e for ReleaseSchemaLock .].
[OLE/DB provider returned message: Unspecified error]
The servers are (from spt_server_info):
Server1:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
Server2:
2 DBMS_VER
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
500 SYS_SPROC_VERSION
8.00.707
The linked server is defined (using SQLOLEDB):
sp_helpserver 'hbd-mrg-qrm-t'
name
network_name
status
id collation_name
connect_timeout query_timeout
------
--
----
--
------
-- --
hbd-mrg-qrm-t
hbd-mrg-qrm-t
rpc,rpc out,data access,use remote collation
4 NULL
0 0
The code to initiate the transaction is:
begin distributed transaction
set XACT_ABORT ON
exec @.nError = ArchiveAppCopy
if @.nError = 0
The first DML statement, insert is failing within the procedure below:
CREATE Procedure dbo.ArchiveAppCopy as
declare @.nError int
set nocount on
begin tran
Set identity_insert AlertReminder on
if @.@.error <> 0 goto Commit_Label
alter table AlertReminder disable trigger all
if @.@.error <> 0 goto Commit_Label
Insert into AlertReminder (
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID)
Select
AlertReminderDateTime,
AlertReminderID,
AlertReminderTime,
AppNumber,
AssignedUser,
FirstName,
LastName,
MessageStatus,
MessageType,
NavCaption,
NavIndex,
Note,
ReplyNote,
ReplyTime,
RouteGroup,
TaskScreen,
UserID
>From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where
AppNumber in (select appnumber from TransferApps)
and not exists (select appnumber from AlertReminder where
AlertReminder.AlertReminderID = tblSource.AlertReminderID
)An update:
Further testing has determined that any schema changes on the local
server will fail the MSDTC transaction.
The schema changes performed here were to disable/enable triggers and
set identity_inserts on/off before the mass copy.
Without any alternatives, I plan to move all the schema changes to
appropriate stored procecedures and call them outside of the
distributed transaction.
Does anyone know why the OLE/DB provider was returned 'Unknown' in the
below message? Other trace messages returned back 'SQLOLEDB', I wonder
if the server link is being broken with the ReleaseSchemaLock and that
failed the DTC.
Jochen.Markert@.fnf.com wrote:
> Any help or suggestion will be appreciated.
> Attempting to perform a distributed transaction and the following is
> returned:
> Server: Msg 8525, Level 16, State 1, Procedure ArchiveAppCopy, Line 12
> Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction.
> OLE DB error trace [OLE/DB Provider 'Unknown' ::ReleaseSchemaLock
> returned 0x8004d00e: OLE DB provider Unknown supported the Schema Lock
> interface, but returned0x8004d00e for ReleaseSchemaLock .].
> [OLE/DB provider returned message: Unspecified error]
> The servers are (from spt_server_info):
> Server1:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> Server2:
> 2 DBMS_VER
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> 500 SYS_SPROC_VERSION
> 8.00.707
> The linked server is defined (using SQLOLEDB):
> sp_helpserver 'hbd-mrg-qrm-t'
> name
> network_name
> status
> id collation_name
> connect_timeout query_timeout
> ------
> --
> ----
> --
> ------
> -- --
> hbd-mrg-qrm-t
> hbd-mrg-qrm-t
> rpc,rpc out,data access,use remote collation
> 4 NULL
> 0 0
>
> The code to initiate the transaction is:
> begin distributed transaction
> set XACT_ABORT ON
> exec @.nError = ArchiveAppCopy
> if @.nError = 0
>
> The first DML statement, insert is failing within the procedure below:
> CREATE Procedure dbo.ArchiveAppCopy as
> declare @.nError int
> set nocount on
> begin tran
> Set identity_insert AlertReminder on
> if @.@.error <> 0 goto Commit_Label
> alter table AlertReminder disable trigger all
> if @.@.error <> 0 goto Commit_Label
> Insert into AlertReminder (
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID)
> Select
> AlertReminderDateTime,
> AlertReminderID,
> AlertReminderTime,
> AppNumber,
> AssignedUser,
> FirstName,
> LastName,
> MessageStatus,
> MessageType,
> NavCaption,
> NavIndex,
> Note,
> ReplyNote,
> ReplyTime,
> RouteGroup,
> TaskScreen,
> UserID
> >From [hbd-mrg-qrm-t].[alscom_mock].[DBO].AlertReminder tblSource where
> AppNumber in (select appnumber from TransferApps)
> and not exists (select appnumber from AlertReminder where
> AlertReminder.AlertReminderID = tblSource.AlertReminderID
> )sql

Remote restore question

Hello everyone,
Quick question. We perform db backups at 10pm on a certain database
that is about 12 gig. Then the transaction log is backed up every
hour. We'd like to restore this database to another off site server,
then apply the logs to keep it in sync w/ production. I've tested this
and it seems to work fine. My only question is regarding the full
backups that are performed every night. Due to bandwidth limitations,
we can't get the full db backup to the remote server in a reasonable
amount of time (the logs can make it over within one hour no problem).
Can we just keep restoring the logs on the remote server without the
nightly db backups being restored? I guess I'm asking if the full db
backup does anything to the log (empties it or the like) that would
make the dbs out of sync?
Thanks in advance.That would be fine. This is one of the reasons why a full backup doesn't
truncate the transaction log. But I suggest you have operational
instructions readily available on how to do this manually, in case the log
restores becomes out of sync, for any reason...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||You can restore logs in sequence on a SQL 7.0 or 2000 and ignore intervening
full backups. This is intentional so that if a full backup goes bad, you
can go to an older full backup and restore more logs to get your database up
to the last committed transaction.
BTW, this is called log-shipping. SQL enterprise edition includes it as
built-in, but many people write their own routines to handle situations and
configurations not directly supported but the built-in tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
news:49e85cd3.0403171311.6b65aab4@.posting.google.com...
> Hello everyone,
> Quick question. We perform db backups at 10pm on a certain database
> that is about 12 gig. Then the transaction log is backed up every
> hour. We'd like to restore this database to another off site server,
> then apply the logs to keep it in sync w/ production. I've tested this
> and it seems to work fine. My only question is regarding the full
> backups that are performed every night. Due to bandwidth limitations,
> we can't get the full db backup to the remote server in a reasonable
> amount of time (the logs can make it over within one hour no problem).
> Can we just keep restoring the logs on the remote server without the
> nightly db backups being restored? I guess I'm asking if the full db
> backup does anything to the log (empties it or the like) that would
> make the dbs out of sync?
> Thanks in advance.|||Excellent. Thanks to both of you.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<enVq$YGDEHA.1588@.tk2msftn
gp13.phx.gbl>...
> You can restore logs in sequence on a SQL 7.0 or 2000 and ignore interveni
ng
> full backups. This is intentional so that if a full backup goes bad, you
> can go to an older full backup and restore more logs to get your database
up
> to the last committed transaction.
> BTW, this is called log-shipping. SQL enterprise edition includes it as
> built-in, but many people write their own routines to handle situations an
d
> configurations not directly supported but the built-in tools.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message
> news:49e85cd3.0403171311.6b65aab4@.posting.google.com...