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 can I truncate the log file

Dear All,
The database XXX has size 1GB and the log has size 1.5GB.
The recovery model is FULL. (SQL-2000)
How can I truncate the log file (reduce the size) through
a plan?
Harris
Do you have any log shipping or any other log backup jobs scheduled? If yes,
have the job run more often, so that it truncates the log after the log
backup is taken (may be every hour or so, depending on the transaction
volume) . If no, set the recovery model to SIMPLE and do a truncate log.
Everything should be OK after you do either of these.
Rathna Raj
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan?
>
> Harris
|||Hi,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn'
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If your data is not that critical (Development Server) and if you do not
require a time based
recovery go for SIMPLE recovery Model for your database. This require less
monitoring of tranasction log usage
Thanks
Hari
MCDBA
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan?
>
> Harris

How can I truncate the log file

Dear All,
The database XXX has size 1GB and the log has size 1.5GB.
The recovery model is FULL. (SQL-2000)
How can I truncate the log file (reduce the size) through
a plan'
HarrisDo you have any log shipping or any other log backup jobs scheduled? If yes,
have the job run more often, so that it truncates the log after the log
backup is taken (may be every hour or so, depending on the transaction
volume) . If no, set the recovery model to SIMPLE and do a truncate log.
Everything should be OK after you do either of these.
Rathna Raj
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris|||Hi,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn'
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If your data is not that critical (Development Server) and if you do not
require a time based
recovery go for SIMPLE recovery Model for your database. This require less
monitoring of tranasction log usage
Thanks
Hari
MCDBA
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris

How can I truncate the log file

Dear All,
The database XXX has size 1GB and the log has size 1.5GB.
The recovery model is FULL. (SQL-2000)
How can I truncate the log file (reduce the size) through
a plan'
HarrisDo you have any log shipping or any other log backup jobs scheduled? If yes,
have the job run more often, so that it truncates the log after the log
backup is taken (may be every hour or so, depending on the transaction
volume) . If no, set the recovery model to SIMPLE and do a truncate log.
Everything should be OK after you do either of these.
Rathna Raj
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris|||Hi,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn'
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If your data is not that critical (Development Server) and if you do not
require a time based
recovery go for SIMPLE recovery Model for your database. This require less
monitoring of tranasction log usage
Thanks
Hari
MCDBA
"Harris Aristotelous" <harris@.glprodata.com> wrote in message
news:667101c4754e$15e58a00$a501280a@.phx.gbl...
> Dear All,
> The database XXX has size 1GB and the log has size 1.5GB.
> The recovery model is FULL. (SQL-2000)
>
> How can I truncate the log file (reduce the size) through
> a plan'
>
> Harris