I'm trying to set up a maintenance plan that backs up the transaction logs
THEN the databases, as I have successfully done with SQL 2000. I get the
following error:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "MARPRO-NTS02".
Maintenance Plan: SIVApplication2 Log
Duration: 00:00:00
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (MARPRO-NTS02)
Backup Database on Local server connection
Databases: SIVApplication2
Type: Transaction Log
Overwrite existing
Task start: 2008-06-13T20:00:03.
Task end: 2008-06-13T20:00:03.
Failed-1073548784) Executing the query "BACKUP LOG [SIVApplication2] TO
DISK = N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN'
WITH NOFORMAT, INIT, NAME = N'SIVApplication2_backup_20080613200003', SKIP,
REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because
there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.

Command:BACKUP LOG [SIVApplication2] TO DISK =
N''C:\Data\Accounting\Backups\sivapplication2\SIVA pplication2.TRN'' WITH
NOFORMAT, INIT, NAME = N''SIVApplication2_backup_20080613200003'', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''SIVApplication2'' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where
database_name=N''SIVApplication2'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information
for database ''''SIVApplication2'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK =
N''C:\Data\Accounting\Backups\sivapplication2\SIVA pplication2.TRN'' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND


Back Up Database (Transaction Log) (MARPRO-NTS02)
Backup Database on Local server connection
Databases: SIVApplication2
Type: Transaction Log
Overwrite existing
Task start: 2008-06-13T20:00:03.
Task end: 2008-06-13T20:00:03.
Failed-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N'SIVApplication2' and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N'SIVApplication2' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information
for database ''SIVApplication2'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK =
N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device
'C:\\Data\\Accounting\\Backups\\sivapplication2\\S IVApplication2.TRN'.
Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.

Command:BACKUP LOG [SIVApplication2] TO DISK =
N''C:\Data\Accounting\Backups\sivapplication2\SIVA pplication2.TRN'' WITH
NOFORMAT, INIT, NAME = N''SIVApplication2_backup_20080613200003'', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where
database_name=N''SIVApplication2'' and backup_set_id=(select
max(backup_set_id) from msdb..backupset where
database_name=N''SIVApplication2'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information
for database ''''SIVApplication2'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK =
N''C:\Data\Accounting\Backups\sivapplication2\SIVA pplication2.TRN'' WITH
FILE = @backupSetId, NOUNLOAD, NOREWIND

I presume something has changed.

Any will be appreciated.

Thanks,

John

On Jun 14, 5:23 pm, jwkh <j...@discussions.microsoft.com> wrote:
> I'm trying to set up a maintenance plan that backs up the transaction logs
> THEN the databases, as I have successfully done with SQL 2000. I get the
> following error:
>
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
> Report was generated on "MARPRO-NTS02".
> Maintenance Plan: SIVApplication2 Log
> Duration: 00:00:00
> Status: Warning: One or more tasks failed..
> Details:
> Back Up Database (Transaction Log) (MARPRO-NTS02)
> Backup Database on Local server connection
> Databases: SIVApplication2
> Type: Transaction Log
> Overwrite existing
> Task start: 2008-06-13T20:00:03.
> Task end: 2008-06-13T20:00:03.
> Failed-1073548784) Executing the query "BACKUP LOG [SIVApplication2] TO
> DISK = N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN'
> WITH NOFORMAT, INIT, NAME = N'SIVApplication2_backup_20080613200003', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> " failed with the following error: "BACKUP LOG cannot be performed because
> there is no current database backup.


You can't do a log backup until a full backup has been done. The full
backup provides a clean "starting point", upon which your log backups
will "append" to should you need to restore.

I appreciate your very timely reply, but I'm a bit confussed. SQL 2000
needed the logs backed before the databases. Has SQL 2005 changed that? At
any rate, I have a maintenance plan that backs up all the log fills M-F THEN
the databases. Since the log backups failed on Friday night BUT the database
backups were okay, will the Monday night log BUs work?

In other words, how should I set up the SQL backups. I tried the SQL docs
but got a lot of "technet newsgroup" results that didn't .

Again, thanks for your response!

John



"Tracy McKibben (http://www.realsqlguy.co" wrote:

> On Jun 14, 5:23 pm, jwkh <j...@discussions.microsoft.com> wrote:
> > I'm trying to set up a maintenance plan that backs up the transaction logs
> > THEN the databases, as I have successfully done with SQL 2000. I get the
> > following error:
> >
> > NEW COMPONENT OUTPUT
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
> > Report was generated on "MARPRO-NTS02".
> > Maintenance Plan: SIVApplication2 Log
> > Duration: 00:00:00
> > Status: Warning: One or more tasks failed..
> > Details:
> > Back Up Database (Transaction Log) (MARPRO-NTS02)
> > Backup Database on Local server connection
> > Databases: SIVApplication2
> > Type: Transaction Log
> > Overwrite existing
> > Task start: 2008-06-13T20:00:03.
> > Task end: 2008-06-13T20:00:03.
> > Failed-1073548784) Executing the query "BACKUP LOG [SIVApplication2] TO
> > DISK = N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN'
> > WITH NOFORMAT, INIT, NAME = N'SIVApplication2_backup_20080613200003', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > " failed with the following error: "BACKUP LOG cannot be performed because
> > there is no current database backup.
>
>
> You can't do a log backup until a full backup has been done. The full
> backup provides a clean "starting point", upon which your log backups
> will "append" to should you need to restore.
>

Hi
create database test
go
alter database test set recovery full
go
backup log test to disk ='c:\temp\test.bak'

/*
There is no current database backup. This log backup cannot be used to roll
forward a preceding database backup.
Processed 1 pages for database 'test', file 'test_log' on file 2.
BACKUP LOG successfully processed 1 pages in 0.196 seconds (0.005 MB/sec).
*/

You need to BACKUP DATABASE .. first and then issue BACKUP LOG .....






"jwkh" <jwkh@discussions.microsoft.com> wrote in message
news:3E07AEE3-4E80-43BD-836C-0BAE361BC661@microsoft.com...
>I appreciate your very timely reply, but I'm a bit confussed. SQL 2000
> needed the logs backed before the databases. Has SQL 2005 changed that?
> At
> any rate, I have a maintenance plan that backs up all the log fills M-F
> THEN
> the databases. Since the log backups failed on Friday night BUT the
> database
> backups were okay, will the Monday night log BUs work?
>
> In other words, how should I set up the SQL backups. I tried the SQL docs
> but got a lot of "technet newsgroup" results that didn't .
>
> Again, thanks for your response!
>
> John
>
>
>
> "Tracy McKibben (http://www.realsqlguy.co" wrote:
>
>> On Jun 14, 5:23 pm, jwkh <j...@discussions.microsoft.com> wrote:
>> > I'm trying to set up a maintenance plan that backs up the transaction
>> > logs
>> > THEN the databases, as I have successfully done with SQL 2000. I get
>> > the
>> > following error:
>> >
>> > NEW COMPONENT OUTPUT
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
>> > Report was generated on "MARPRO-NTS02".
>> > Maintenance Plan: SIVApplication2 Log
>> > Duration: 00:00:00
>> > Status: Warning: One or more tasks failed..
>> > Details:
>> > Back Up Database (Transaction Log) (MARPRO-NTS02)
>> > Backup Database on Local server connection
>> > Databases: SIVApplication2
>> > Type: Transaction Log
>> > Overwrite existing
>> > Task start: 2008-06-13T20:00:03.
>> > Task end: 2008-06-13T20:00:03.
>> > Failed-1073548784) Executing the query "BACKUP LOG [SIVApplication2]
>> > TO
>> > DISK =
>> > N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN'
>> > WITH NOFORMAT, INIT, NAME = N'SIVApplication2_backup_20080613200003',
>> > SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > " failed with the following error: "BACKUP LOG cannot be performed
>> > because
>> > there is no current database backup.
>>
>>
>> You can't do a log backup until a full backup has been done. The full
>> backup provides a clean "starting point", upon which your log backups
>> will "append" to should you need to restore.
>>

Uri,

I really appreciate your response, but the databases already exist. And,
unfortunately, I know very little about SQL.

John


"Uri Dimant" wrote:

> Hi
> create database test
> go
> alter database test set recovery full
> go
> backup log test to disk ='c:\temp\test.bak'
>
> /*
> There is no current database backup. This log backup cannot be used to roll
> forward a preceding database backup.
> Processed 1 pages for database 'test', file 'test_log' on file 2.
> BACKUP LOG successfully processed 1 pages in 0.196 seconds (0.005 MB/sec).
> */
>
> You need to BACKUP DATABASE .. first and then issue BACKUP LOG .....
>
>
>
>
>
>
> "jwkh" <jwkh@discussions.microsoft.com> wrote in message
> news:3E07AEE3-4E80-43BD-836C-0BAE361BC661@microsoft.com...
> >I appreciate your very timely reply, but I'm a bit confussed. SQL 2000
> > needed the logs backed before the databases. Has SQL 2005 changed that?
> > At
> > any rate, I have a maintenance plan that backs up all the log fills M-F
> > THEN
> > the databases. Since the log backups failed on Friday night BUT the
> > database
> > backups were okay, will the Monday night log BUs work?
> >
> > In other words, how should I set up the SQL backups. I tried the SQL docs
> > but got a lot of "technet newsgroup" results that didn't .
> >
> > Again, thanks for your response!
> >
> > John
> >
> >
> >
> > "Tracy McKibben (http://www.realsqlguy.co" wrote:
> >
> >> On Jun 14, 5:23 pm, jwkh <j...@discussions.microsoft.com> wrote:
> >> > I'm trying to set up a maintenance plan that backs up the transaction
> >> > logs
> >> > THEN the databases, as I have successfully done with SQL 2000. I get
> >> > the
> >> > following error:
> >> >
> >> > NEW COMPONENT OUTPUT
> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
> >> > Report was generated on "MARPRO-NTS02".
> >> > Maintenance Plan: SIVApplication2 Log
> >> > Duration: 00:00:00
> >> > Status: Warning: One or more tasks failed..
> >> > Details:
> >> > Back Up Database (Transaction Log) (MARPRO-NTS02)
> >> > Backup Database on Local server connection
> >> > Databases: SIVApplication2
> >> > Type: Transaction Log
> >> > Overwrite existing
> >> > Task start: 2008-06-13T20:00:03.
> >> > Task end: 2008-06-13T20:00:03.
> >> > Failed-1073548784) Executing the query "BACKUP LOG [SIVApplication2]
> >> > TO
> >> > DISK =
> >> > N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN'
> >> > WITH NOFORMAT, INIT, NAME = N'SIVApplication2_backup_20080613200003',
> >> > SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > " failed with the following error: "BACKUP LOG cannot be performed
> >> > because
> >> > there is no current database backup.
> >>
> >>
> >> You can't do a log backup until a full backup has been done. The full
> >> backup provides a clean "starting point", upon which your log backups
> >> will "append" to should you need to restore.
> >>
>
>
>

There can be other reasons for this situation. Never have performed a database, as Tracy and Uri
exemplified is one reason.

Another is that you run in full recovery and then you truncate your log without doing a backup of it
(BACKUP LOG using TRUNCATE_ONLY or NO_LOG options).

Yet another is having the data in full recovery, then switching it to simple and back to full.

All above are reasons for a broken log backup chain which this is all about and what the error
message try to communicate. SQL Server 2000 was stupid because in these cases it did perform the
backup (even though it was useless) and it returned text (not an error) that the backup it performed
was useless. 2005 is better since it does produce a proper error, so that the dba can react and
investigate.

There is plenty of information about backup, restore and recovery models in Books Online. Make sure
you allocate the amount of time that the topic deserves, though.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"jwkh" <jwkh@discussions.microsoft.com> wrote in message
news:E4E65FF1-67A8-4D6C-9240-87BE99F60EAA@microsoft.com...
> Uri,
>
> I really appreciate your response, but the databases already exist. And,
> unfortunately, I know very little about SQL.
>
> John
>
>
> "Uri Dimant" wrote:
>
>> Hi
>> create database test
>> go
>> alter database test set recovery full
>> go
>> backup log test to disk ='c:\temp\test.bak'
>>
>> /*
>> There is no current database backup. This log backup cannot be used to roll
>> forward a preceding database backup.
>> Processed 1 pages for database 'test', file 'test_log' on file 2.
>> BACKUP LOG successfully processed 1 pages in 0.196 seconds (0.005 MB/sec).
>> */
>>
>> You need to BACKUP DATABASE .. first and then issue BACKUP LOG .....
>>
>>
>>
>>
>>
>>
>> "jwkh" <jwkh@discussions.microsoft.com> wrote in message
>> news:3E07AEE3-4E80-43BD-836C-0BAE361BC661@microsoft.com...
>> >I appreciate your very timely reply, but I'm a bit confussed. SQL 2000
>> > needed the logs backed before the databases. Has SQL 2005 changed that?
>> > At
>> > any rate, I have a maintenance plan that backs up all the log fills M-F
>> > THEN
>> > the databases. Since the log backups failed on Friday night BUT the
>> > database
>> > backups were okay, will the Monday night log BUs work?
>> >
>> > In other words, how should I set up the SQL backups. I tried the SQL docs
>> > but got a lot of "technet newsgroup" results that didn't .
>> >
>> > Again, thanks for your response!
>> >
>> > John
>> >
>> >
>> >
>> > "Tracy McKibben (http://www.realsqlguy.co" wrote:
>> >
>> >> On Jun 14, 5:23 pm, jwkh <j...@discussions.microsoft.com> wrote:
>> >> > I'm trying to set up a maintenance plan that backs up the transaction
>> >> > logs
>> >> > THEN the databases, as I have successfully done with SQL 2000. I get
>> >> > the
>> >> > following error:
>> >> >
>> >> > NEW COMPONENT OUTPUT
>> >> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
>> >> > Report was generated on "MARPRO-NTS02".
>> >> > Maintenance Plan: SIVApplication2 Log
>> >> > Duration: 00:00:00
>> >> > Status: Warning: One or more tasks failed..
>> >> > Details:
>> >> > Back Up Database (Transaction Log) (MARPRO-NTS02)
>> >> > Backup Database on Local server connection
>> >> > Databases: SIVApplication2
>> >> > Type: Transaction Log
>> >> > Overwrite existing
>> >> > Task start: 2008-06-13T20:00:03.
>> >> > Task end: 2008-06-13T20:00:03.
>> >> > Failed-1073548784) Executing the query "BACKUP LOG [SIVApplication2]
>> >> > TO
>> >> > DISK =
>> >> > N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN'
>> >> > WITH NOFORMAT, INIT, NAME = N'SIVApplication2_backup_20080613200003',
>> >> > SKIP,
>> >> > REWIND, NOUNLOAD, STATS = 10
>> >> > " failed with the following error: "BACKUP LOG cannot be performed
>> >> > because
>> >> > there is no current database backup.
>> >>
>> >>
>> >> You can't do a log backup until a full backup has been done. The full
>> >> backup provides a clean "starting point", upon which your log backups
>> >> will "append" to should you need to restore.
>> >>
>>
>>
>>

Do you really only run one transaction log backup a night and also run a
full backup each night? If so, why keep the database in full recovery
model - just switch to simple recovery and forget about transaction log
backups.

If you need the ability to recover to a point in time - then you need to
run the transaction log backups more often. How often you do this all
depends upon your recovery requirements and how much data loss you can
afford. If you can afford up to 24 hours of data loss - simple recovery
model is best - anything less than that will be how often you run your
transaction log backups.

Jeff

jwkh wrote:
> I appreciate your very timely reply, but I'm a bit confussed. SQL 2000
> needed the logs backed before the databases. Has SQL 2005 changed that? At
> any rate, I have a maintenance plan that backs up all the log fills M-F THEN
> the databases. Since the log backups failed on Friday night BUT the database
> backups were okay, will the Monday night log BUs work?
>
> In other words, how should I set up the SQL backups. I tried the SQL docs
> but got a lot of "technet newsgroup" results that didn't .
>
> Again, thanks for your response!
>
> John
>
>
>
> "Tracy McKibben (http://www.realsqlguy.co" wrote:
>
>> On Jun 14, 5:23 pm, jwkh <j...@discussions.microsoft.com> wrote:
>>> I'm trying to set up a maintenance plan that backs up the transaction logs
>>> THEN the databases, as I have successfully done with SQL 2000. I get the
>>> following error:
>>>
>>> NEW COMPONENT OUTPUT
>>> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
>>> Report was generated on "MARPRO-NTS02".
>>> Maintenance Plan: SIVApplication2 Log
>>> Duration: 00:00:00
>>> Status: Warning: One or more tasks failed..
>>> Details:
>>> Back Up Database (Transaction Log) (MARPRO-NTS02)
>>> Backup Database on Local server connection
>>> Databases: SIVApplication2
>>> Type: Transaction Log
>>> Overwrite existing
>>> Task start: 2008-06-13T20:00:03.
>>> Task end: 2008-06-13T20:00:03.
>>> Failed-1073548784) Executing the query "BACKUP LOG [SIVApplication2] TO
>>> DISK = N'C:\\Data\\Accounting\\Backups\\sivapplication2\\ SIVApplication2.TRN'
>>> WITH NOFORMAT, INIT, NAME = N'SIVApplication2_backup_20080613200003', SKIP,
>>> REWIND, NOUNLOAD, STATS = 10
>>> " failed with the following error: "BACKUP LOG cannot be performed because
>>> there is no current database backup.
>>
>> You can't do a log backup until a full backup has been done. The full
>> backup provides a clean "starting point", upon which your log backups
>> will "append" to should you need to restore.
>>

My client does not have that many transactions in a day. With SQL 2000, I
would do a nightly log THEN database backup. With SQL 2005, it won't do the
log backup because the database backup didn't exist yet.

I'm confused as to the change.

Doing a single backup in the evening, how do I get that day's transactions
AND the database backed up? I use the maintenance wizard to create the plans
and overwrite the previous day's files each night.

Thanks,

John


"Jeffrey Williams" wrote:

> Do you really only run one transaction log backup a night and also run a
> full backup each night? If so, why keep the database in full recovery
> model - just switch to simple recovery and forget about transaction log
> backups.
>
> If you need the ability to recover to a point in time - then you need to
> run the transaction log backups more often. How often you do this all
> depends upon your recovery requirements and how much data loss you can
> afford. If you can afford up to 24 hours of data loss - simple recovery
> model is best - anything less than that will be how often you run your
> transaction log backups.
>
> Jeff
>

As I understand it if you do a full backup of the database you get the
logs rolled in, cleared out and ready to go again. I use TLog backups
so I can recover to a point in time - say noon if the system goes down
and my backup is at 2:00 AM. I can restore the full and then restore
the logs until I reach the time I went down.

On Sun, 15 Jun 2008 13:28:02 -0700, jwkh
<jwkh@discussions.microsoft.com> wrote:

>My client does not have that many transactions in a day. With SQL 2000, I
>would do a nightly log THEN database backup. With SQL 2005, it won't do the
>log backup because the database backup didn't exist yet.
>
>I'm confused as to the change.
>
>Doing a single backup in the evening, how do I get that day's transactions
>AND the database backed up? I use the maintenance wizard to create the plans
>and overwrite the previous day's files each night.
>
>Thanks,
>
>John
>
>
>"Jeffrey Williams" wrote:
>
>> Do you really only run one transaction log backup a night and also run a
>> full backup each night? If so, why keep the database in full recovery
>> model - just switch to simple recovery and forget about transaction log
>> backups.
>>
>> If you need the ability to recover to a point in time - then you need to
>> run the transaction log backups more often. How often you do this all
>> depends upon your recovery requirements and how much data loss you can
>> afford. If you can afford up to 24 hours of data loss - simple recovery
>> model is best - anything less than that will be how often you run your
>> transaction log backups.
>>
>> Jeff
>>

Brett I. Holcomb wrote:
> As I understand it if you do a full backup of the database you get the
> logs rolled in, cleared out and ready to go again. I use TLog backups
> so I can recover to a point in time - say noon if the system goes down
> and my backup is at 2:00 AM. I can restore the full and then restore
> the logs until I reach the time I went down.
>
> On Sun, 15 Jun 2008 13:28:02 -0700, jwkh
> <jwkh@discussions.microsoft.com> wrote:
>
>> My client does not have that many transactions in a day. With SQL 2000, I
>> would do a nightly log THEN database backup. With SQL 2005, it won't do the
>> log backup because the database backup didn't exist yet.
>>
>> I'm confused as to the change.
>>
>> Doing a single backup in the evening, how do I get that day's transactions
>> AND the database backed up? I use the maintenance wizard to create the plans
>> and overwrite the previous day's files each night.
>>
>> Thanks,
>>
>> John
>>
>>
>> "Jeffrey Williams" wrote:
>>
>>> Do you really only run one transaction log backup a night and also run a
>>> full backup each night? If so, why keep the database in full recovery
>>> model - just switch to simple recovery and forget about transaction log
>>> backups.
>>>
>>> If you need the ability to recover to a point in time - then you need to
>>> run the transaction log backups more often. How often you do this all
>>> depends upon your recovery requirements and how much data loss you can
>>> afford. If you can afford up to 24 hours of data loss - simple recovery
>>> model is best - anything less than that will be how often you run your
>>> transaction log backups.
>>>
>>> Jeff
>>>

Brett, a database backup (including differential) does not affect the
transaction log. The only thing that affects the transaction log is a
transaction log backup.

How often you backup the transaction log really depends upon what the
business requirements are. If the business can afford to lose up to 24
hours (1 day) of transactions - setting the recovery model to simple is
the best way to go. If the business cannot accept that amount of loss -
then you need to perform the transaction log backups more often.
Generally, I see most systems setup to perform transaction log backups
at least every hour (I perform mine every 15 minutes).

John, overwriting the backup files (both transaction and database) is
not a good idea unless you are absolutely sure that those backup files
have been copied somewhere else.

Let's say you perform you transaction log/database backups at 10pm and
overwrite the backup files. At 1am you find out that there was a
problem at 9pm the previous evening that you need to recover. How are
you going to recover to 9pm the previous night? You've overwritten the
backup file that you would need to restore from - and the transaction
log backup is useless because it can't be applied to your current backup.

You really should have two jobs setup - the first job should backup the
database every night. The second job should backup the transaction log
every xx hours/minutes. At least once a day (and more often if needed)
those backup files should be copied to tape or another server/storage
system.

Now, you need additional steps in the backup plan or a separate job to
cleanup the old backup files. I always recommend keeping at least two
full backups (the one just created and the previous days) and all
transaction logs newer than the previous backup online for ease of recovery.

Jeff

I REALLY appreciate all the that all of you have given me. I'll work on
the plan tomorrow with my client.

Thank You All!,

John



"Jeffrey Williams" wrote:

> Brett I. Holcomb wrote:
> > As I understand it if you do a full backup of the database you get the
> > logs rolled in, cleared out and ready to go again. I use TLog backups
> > so I can recover to a point in time - say noon if the system goes down
> > and my backup is at 2:00 AM. I can restore the full and then restore
> > the logs until I reach the time I went down.
> >
> > On Sun, 15 Jun 2008 13:28:02 -0700, jwkh
> > <jwkh@discussions.microsoft.com> wrote:
> >
> >> My client does not have that many transactions in a day. With SQL 2000, I
> >> would do a nightly log THEN database backup. With SQL 2005, it won't do the
> >> log backup because the database backup didn't exist yet.
> >>
> >> I'm confused as to the change.
> >>
> >> Doing a single backup in the evening, how do I get that day's transactions
> >> AND the database backed up? I use the maintenance wizard to create the plans
> >> and overwrite the previous day's files each night.
> >>
> >> Thanks,
> >>
> >> John
> >>
> >>
> >> "Jeffrey Williams" wrote:
> >>
> >>> Do you really only run one transaction log backup a night and also run a
> >>> full backup each night? If so, why keep the database in full recovery
> >>> model - just switch to simple recovery and forget about transaction log
> >>> backups.
> >>>
> >>> If you need the ability to recover to a point in time - then you need to
> >>> run the transaction log backups more often. How often you do this all
> >>> depends upon your recovery requirements and how much data loss you can
> >>> afford. If you can afford up to 24 hours of data loss - simple recovery
> >>> model is best - anything less than that will be how often you run your
> >>> transaction log backups.
> >>>
> >>> Jeff
> >>>
>
> Brett, a database backup (including differential) does not affect the
> transaction log. The only thing that affects the transaction log is a
> transaction log backup.
>
> How often you backup the transaction log really depends upon what the
> business requirements are. If the business can afford to lose up to 24
> hours (1 day) of transactions - setting the recovery model to simple is
> the best way to go. If the business cannot accept that amount of loss -
> then you need to perform the transaction log backups more often.
> Generally, I see most systems setup to perform transaction log backups
> at least every hour (I perform mine every 15 minutes).
>
> John, overwriting the backup files (both transaction and database) is
> not a good idea unless you are absolutely sure that those backup files
> have been copied somewhere else.
>
> Let's say you perform you transaction log/database backups at 10pm and
> overwrite the backup files. At 1am you find out that there was a
> problem at 9pm the previous evening that you need to recover. How are
> you going to recover to 9pm the previous night? You've overwritten the
> backup file that you would need to restore from - and the transaction
> log backup is useless because it can't be applied to your current backup.
>
> You really should have two jobs setup - the first job should backup the
> database every night. The second job should backup the transaction log
> every xx hours/minutes. At least once a day (and more often if needed)
> those backup files should be copied to tape or another server/storage
> system.
>
> Now, you need additional steps in the backup plan or a separate job to
> cleanup the old backup files. I always recommend keeping at least two
> full backups (the one just created and the previous days) and all
> transaction logs newer than the previous backup online for ease of recovery.
>
> Jeff
>

Ahh, I see - that's why I said as I understand <G>. That s then.
If I go to simple mode and backup do the transaction logs simply get
thrown away? I know in simple mode there are not TLogs.



On Sun, 15 Jun 2008 14:06:35 -0700, Jeffrey Williams
<jeff.williams3188@verizon.ent> wrote:

>Brett I. Holcomb wrote:
>> As I understand it if you do a full backup of the database you get the
>> logs rolled in, cleared out and ready to go again. I use TLog backups
>> so I can recover to a point in time - say noon if the system goes down
>> and my backup is at 2:00 AM. I can restore the full and then restore
>> the logs until I reach the time I went down.
>>
>> On Sun, 15 Jun 2008 13:28:02 -0700, jwkh
>> <jwkh@discussions.microsoft.com> wrote:
snop
>>>> Jeff
>>>>
>
>Brett, a database backup (including differential) does not affect the
>transaction log. The only thing that affects the transaction log is a
>transaction log backup.
>
>
>Jeff

> Ahh, I see - that's why I said as I understand <G>. That s then.
> If I go to simple mode and backup do the transaction logs simply get
> thrown away? I know in simple mode there are not TLogs.

Of course, the transaction log is still used in simple recovery mode. After
all, this is often the only way a transaction could be rolled back. The
difference is, the inactive space in the log can be re-used without having
to back up the log, and this often prevents the log from growing at all
(except during rather large transactions or abnormal data moves, or if you
are intentionally shrinking the log and keeping it very small).

I understand more now. Thanks. It sure s to have a place like
this when you've been declared a DBA because you are the windows
server sys admin <G>.


On Sun, 15 Jun 2008 22:22:36 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

>> Ahh, I see - that's why I said as I understand <G>. That s then.
>> If I go to simple mode and backup do the transaction logs simply get
>> thrown away? I know in simple mode there are not TLogs.
>
>Of course, the transaction log is still used in simple recovery mode. After
>all, this is often the only way a transaction could be rolled back. The
>difference is, the inactive space in the log can be re-used without having
>to back up the log, and this often prevents the log from growing at all
>(except during rather large transactions or abnormal data moves, or if you
>are intentionally shrinking the log and keeping it very small).
>