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 come Im getting error saying transaction log Full?

Hello,

I have a process that failed with the following error message. The SQL server error messages also said the Transaction log was full and there was not enough disc space.

Strangely, when I checked all the drives on the server, there was plenty of free space, the smallest amount free on one drive, where SQL server is located was 20GB. I am confused.

I backed up the DB to another server and deleted a lot of the transaction logs and now the drive has 30GB and is okay.

Does anyone know how the transaction log fills up? Can I change a setting somewhere to increase the maximum size of the transaction log? Or maybe change the location where it is saved to another drive? Or have it automatically cleared out every once in a while?

Any of your input is greatly appreciated.

Thanks.

Rodney

-----------------------
DS-DBMS-E400: UDA driver reported the following on connection 'Data Target
(ODBC)':
DMS-E-DBPARSER, The underlying database detected an error during processing of
the SQL request.
[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database
'ancosalesdm' is full. Back up the transaction log for the database to free up
some log space.
(for details, see Build_SAL_FA_ShipSKU_0584.log)
[PROGRESS - 00:32:42] Build Node 68 'SAL_FA_ShipSKU'; failed
DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74':
DMS-E-GENERAL, A general exception has occurred during operation 'execute
immediate'.
The log file for database 'ancosalesdm' is full. Back up the transaction log
for the database to free up some log space.
DMS-E-GENERAL, A general exception has occurred during operation 'execute
immediate'.
General SQL Server error: Check messages from the SQL Server.
DS-DBMS-E400: UDA driver reported the following on connection 'ALIAS_00DF1E74':
DMS-E-GENERAL, A general exception has occurred during operation 'rollback
transaction'.
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
DMS-E-GENERAL, A general exception has occurred during operation 'rollback
transaction'.
General SQL Server error: Check messages from the SQL Server.
-----------------------In Enterprise Manager, right click the DB and go to Properties. From there you can check to see if your Transaction Log has a size limit, and can adjust it if necessary.|||If you are not doing transaction log backups, you need to set the recovery mode on this database to Simple. This will make sure it doesn't just keep growing until it fills up.|||all this is good, but if a transaction affects a really large amount of data the recovery mode is irrelevant. the log will continue growing (if the setting to allow growth is on) until it reaches the limit specified or the limit of the disk.|||That's true, and if this is the case, you will need to break the transaction into smaller pieces. You can also insert CHECKPOINT at various places in your transaction if you need to, but this would be a pretty rare case.sql