How Do I Automate A Recovery Process With Lots of Transaction Logs

I have to automate a process that will restore the latest BAK file and all the TRN files that occured afterwards on a remote server. I can't use the log shipping process. The BAK file is created nightly, and TRN files are created every 15 mins. I'm using a maintenance plan, so the file names change
Can anyone think of a good way to recover all those files, without all the clicking and typing
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.com/sql_server_log_shipping.asp
HTH,
Paul Ibison|||It's purely political
I'm using a maintenance plan, so the names of the transaction logs change
I think I could code my way out, but I hate to spend the time if there is a better solution
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeffsql

How Do I Automate A Recovery Process With Lots of Transaction Logs

I have to automate a process that will restore the latest BAK file and all t
he TRN files that occured afterwards on a remote server. I can't use the lo
g shipping process. The BAK file is created nightly, and TRN files are crea
ted every 15 mins. I'm usi
ng a maintenance plan, so the file names change.
Can anyone think of a good way to recover all those files, without all the c
licking and typing?
Jeff ZuerleinJeff
Create JOB under Management -SQL Server Agent folders with RESTORE DATABASE
..... command.
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:367639E5-7A83-4D3D-8FA4-2507A221F958@.microsoft.com...
> I have to automate a process that will restore the latest BAK file and all
the TRN files that occured afterwards on a remote server. I can't use the
log shipping process. The BAK file is created nightly, and TRN files are
created every 15 mins. I'm using a maintenance plan, so the file names
change.
> Can anyone think of a good way to recover all those files, without all the
clicking and typing?
> Jeff Zuerlein|||Jeff,
I'm interested in why you can't use log shipping. If it is because you're
not using Enterprise Edition, then there are scripts in the Resource Kit to
do it manually for Standard Edition and below, or online there are a few
people who provide them for free:
http://www.sql-server-performance.c...og_shipping.asp
HTH,
Paul Ibison|||It's purely political.
I'm using a maintenance plan, so the names of the transaction logs change.
I think I could code my way out, but I hate to spend the time if there is a
better solution.
Jeff|||To load logs from a folder you can get the file names into a temp table in
date order (earliest first) using something like this pseudo code
declare @.files int
create table #files(filename varchar(255))
insert #files exec master..xp_cmdshell 'dir /B /A-D /O-D c:\logs\*.trn'
delete #files where filename is null or filename like '%File Not Found%'
select @.files = count(*) from #files
If @.files >0
begin
-- loop through files in a cursor issuing a restore log command
end
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Zuerlein" <anonymous@.discussions.microsoft.com> wrote in message
news:FCFA3898-9DAB-46EA-8BD8-0AF5982572EF@.microsoft.com...
> It's purely political.
> I'm using a maintenance plan, so the names of the transaction logs change.
> I think I could code my way out, but I hate to spend the time if there is
a better solution.
> Jeff

How do I "suspend" merge replication while I extract data from central table?

Is there a standard practice to "hold" Merge replication so that the
data collected at the central server (in a remote sales order
application) can be periodically 'removed' to another part of the
system without clashing with ongoing replication from clients?
I'm experiencing my clients initiating replication, and therefore
inserting more rows, whilst the server SSIS job transfers and deletes
rows collected in the central table to the order processing tables,
thus losing data.
My implementation is SQL 2005 Express clients using Merge Websync to a
SQL 2005 standard central server.
Any advice/recommendations would be appreciated!
the best approach is to drop the subscriptions and publications and make the
changes. You can try to disable the triggers make changes, and then
re-enable them. However the results may be unpredictable.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<reefbreakbda@.hotmail.com> wrote in message
news:1175416990.053051.174730@.y80g2000hsf.googlegr oups.com...
> Is there a standard practice to "hold" Merge replication so that the
> data collected at the central server (in a remote sales order
> application) can be periodically 'removed' to another part of the
> system without clashing with ongoing replication from clients?
> I'm experiencing my clients initiating replication, and therefore
> inserting more rows, whilst the server SSIS job transfers and deletes
> rows collected in the central table to the order processing tables,
> thus losing data.
> My implementation is SQL 2005 Express clients using Merge Websync to a
> SQL 2005 standard central server.
> Any advice/recommendations would be appreciated!
>

How can you enable remote connections using TSQL?

I know how to enable remote connections using the Surface Area Configuration tool.
Seems there would also be a way to configure it using a TSQL command/procedure call.
thanks,
- Paul -
You can use sp_configure to enable the features exposed by Surface Area Configuration tool. To see the options, run following first:
exec sp_configure 'show advanced options', 1;
reconfigure with override;
Please take a look at topic below in Books Online for more details.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/scsql9/html/f741169c-1453-4ad2-830b-bf2be27d712f.htm
For remote connections, you have to use SMO to do it. Doing it from TSQL directly is not possible since it requires manipulation of registry values and please don't use the undocumented XPs for registry manipulation.

How can SQL Server DTC be stopped from automatically enlisting a remote server in a distributed

How do I stop a remote query that INSERTS into a local table from being automatically "upgraded" to a distributed transaction?

I am using Windows 2000 server and SQL Server 2000 SP3a on both machines.

I am executing the following statement in Query analyzer.

INSERT MyLocalServer (col1)
EXECUTE MyRemoteServer.Master.dbo.sp_executesql
@.RemoteQuery,
@.ParameterDefinition,
@.Paramter = 'somevalue'

@.RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.

The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".

The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:

"Server: Msg 7391, Level 16, State 1, Line 17
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."

The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.

Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.

How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?

More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.

I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.

Anyone know the answer?

Moving to the SQL Server Database Engine forum.|||We are running into the exact same issue; were you ever able to find a resolution?

How can SQL Server DTC be stopped from automatically enlisting a remote server in a distributed

How do I stop a remote query that INSERTS into a local table from being automatically "upgraded" to a distributed transaction?

I am using Windows 2000 server and SQL Server 2000 SP3a on both machines.

I am executing the following statement in Query analyzer.

INSERT MyLocalServer (col1)
EXECUTE MyRemoteServer.Master.dbo.sp_executesql
@.RemoteQuery,
@.ParameterDefinition,
@.Paramter = 'somevalue'

@.RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.

The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".

The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:

"Server: Msg 7391, Level 16, State 1, Line 17
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."

The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.

Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.

How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?

More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.

I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.

Anyone know the answer?

Moving to the SQL Server Database Engine forum.|||We are running into the exact same issue; were you ever able to find a resolution?