We have an SQL Server backup job that backups up a database every two
minutes. We also have an alert where if the percent log used is greater then
75 then the response is to run this same backup job.

The backup file is named as such:

Databasename_backup_YYYYMMDDHHMM.trn

The database is named to the nearest minute.
We had a situation where the percent log used got greater then 75 and this
triggered the alert and the transaction log was backed up. Before the minute
increased the scheduled backup ran. As it is named to the nearest minute,
the scheduled backup over wrote the previous triggered backup as they were
named the same.

The client asked us to do a restore and we were not able to do so in that
MSDB two files are in the history and they are named the same. Has anyone
else seen this?

1) Develop a naming scheme that guarantees uniqueness.

2) Do the restores using hand-written RESTORE... commands.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Loren Z" <anonymous@discussions.microsoft.com> wrote in message
news:eZegIthCJHA.5316@TK2MSFTNGP04.phx.gbl...
> We have an SQL Server backup job that backups up a database every two
> minutes. We also have an alert where if the percent log used is greater
> then 75 then the response is to run this same backup job.
>
> The backup file is named as such:
>
> Databasename_backup_YYYYMMDDHHMM.trn
>
> The database is named to the nearest minute.
> We had a situation where the percent log used got greater then 75 and this
> triggered the alert and the transaction log was backed up. Before the
> minute increased the scheduled backup ran. As it is named to the nearest
> minute, the scheduled backup over wrote the previous triggered backup as
> they were named the same.
>
> The client asked us to do a restore and we were not able to do so in that
> MSDB two files are in the history and they are named the same. Has anyone
> else seen this?
>

Did the backup commands have INIT or FORMAT? If not, you probably have two backup in the same file.
Verify using RESTORE HEADERONLY.

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


"Loren Z" <anonymous@discussions.microsoft.com> wrote in message
news:eZegIthCJHA.5316@TK2MSFTNGP04.phx.gbl...
> We have an SQL Server backup job that backups up a database every two minutes. We also have an
> alert where if the percent log used is greater then 75 then the response is to run this same
> backup job.
>
> The backup file is named as such:
>
> Databasename_backup_YYYYMMDDHHMM.trn
>
> The database is named to the nearest minute.
> We had a situation where the percent log used got greater then 75 and this triggered the alert and
> the transaction log was backed up. Before the minute increased the scheduled backup ran. As it is
> named to the nearest minute, the scheduled backup over wrote the previous triggered backup as they
> were named the same.
>
> The client asked us to do a restore and we were not able to do so in that MSDB two files are in
> the history and they are named the same. Has anyone else seen this?
>

Hi,

We are using a maintenance plan to generate the backup job running SQL2005
build 3239 (CU7). Did something change in the post SP2 builds in that I am
sure we would have had a backup failure because of the duplicate name? Looks
like it now creates file 1/file 2 etc into the same backup file.

Thanks

Chris

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:%23nIoHIiCJHA.5808@TK2MSFTNGP04.phx.gbl...
> Did the backup commands have INIT or FORMAT? If not, you probably have two
> backup in the same file. Verify using RESTORE HEADERONLY.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Loren Z" <anonymous@discussions.microsoft.com> wrote in message
> news:eZegIthCJHA.5316@TK2MSFTNGP04.phx.gbl...
>> We have an SQL Server backup job that backups up a database every two
>> minutes. We also have an alert where if the percent log used is greater
>> then 75 then the response is to run this same backup job.
>>
>> The backup file is named as such:
>>
>> Databasename_backup_YYYYMMDDHHMM.trn
>>
>> The database is named to the nearest minute.
>> We had a situation where the percent log used got greater then 75 and
>> this triggered the alert and the transaction log was backed up. Before
>> the minute increased the scheduled backup ran. As it is named to the
>> nearest minute, the scheduled backup over wrote the previous triggered
>> backup as they were named the same.
>>
>> The client asked us to do a restore and we were not able to do so in that
>> MSDB two files are in the history and they are named the same. Has anyone
>> else seen this?
>>
>

Hi Chris,

Why would build level come into play here? If you do a backup to a file which already exist, it will
quite simply be appended. Unless you specify INIT, which I'm pretty certain that main plan do not
specify. If you have INIT, then it will overwrite. No error will be produced in any case.

An error *will* be produced if your earlier backup specified RETAINDAYS or EXPIREDATE and the date
when the backup will expire hasn't come yet. These options are mostly useless (TM) and exposing
these in maint wiz was a bad idea to start with since most users do not understand what these
options do and what they don't do.

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


"Chris Wood" <anonymous@microsoft.com> wrote in message
news:eQFJlGdDJHA.5196@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> We are using a maintenance plan to generate the backup job running SQL2005 build 3239 (CU7). Did
> something change in the post SP2 builds in that I am sure we would have had a backup failure
> because of the duplicate name? Looks like it now creates file 1/file 2 etc into the same backup
> file.
>
> Thanks
>
> Chris
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:%23nIoHIiCJHA.5808@TK2MSFTNGP04.phx.gbl...
>> Did the backup commands have INIT or FORMAT? If not, you probably have two backup in the same
>> file. Verify using RESTORE HEADERONLY.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Loren Z" <anonymous@discussions.microsoft.com> wrote in message
>> news:eZegIthCJHA.5316@TK2MSFTNGP04.phx.gbl...
>>> We have an SQL Server backup job that backups up a database every two minutes. We also have an
>>> alert where if the percent log used is greater then 75 then the response is to run this same
>>> backup job.
>>>
>>> The backup file is named as such:
>>>
>>> Databasename_backup_YYYYMMDDHHMM.trn
>>>
>>> The database is named to the nearest minute.
>>> We had a situation where the percent log used got greater then 75 and this triggered the alert
>>> and the transaction log was backed up. Before the minute increased the scheduled backup ran. As
>>> it is named to the nearest minute, the scheduled backup over wrote the previous triggered backup
>>> as they were named the same.
>>>
>>> The client asked us to do a restore and we were not able to do so in that MSDB two files are in
>>> the history and they are named the same. Has anyone else seen this?
>>>
>>
>
>

Thanks Tibor.

It might have been in SQL2000 that gave the error. As I cannot see under the
hood at what the SSIS package has for parameters I will assume that we could
now have many 'files' in the log backup so a recovery would need to look at
this aspect as well.

Thanks

Chris

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:%23TAPzjfDJHA.1628@TK2MSFTNGP02.phx.gbl...
> Hi Chris,
>
> Why would build level come into play here? If you do a backup to a file
> which already exist, it will quite simply be appended. Unless you specify
> INIT, which I'm pretty certain that main plan do not specify. If you have
> INIT, then it will overwrite. No error will be produced in any case.
>
> An error *will* be produced if your earlier backup specified RETAINDAYS or
> EXPIREDATE and the date when the backup will expire hasn't come yet. These
> options are mostly useless (TM) and exposing these in maint wiz was a bad
> idea to start with since most users do not understand what these options
> do and what they don't do.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Chris Wood" <anonymous@microsoft.com> wrote in message
> news:eQFJlGdDJHA.5196@TK2MSFTNGP04.phx.gbl...
>> Hi,
>>
>> We are using a maintenance plan to generate the backup job running
>> SQL2005 build 3239 (CU7). Did something change in the post SP2 builds in
>> that I am sure we would have had a backup failure because of the
>> duplicate name? Looks like it now creates file 1/file 2 etc into the same
>> backup file.
>>
>> Thanks
>>
>> Chris
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>> in message news:%23nIoHIiCJHA.5808@TK2MSFTNGP04.phx.gbl...
>>> Did the backup commands have INIT or FORMAT? If not, you probably have
>>> two backup in the same file. Verify using RESTORE HEADERONLY.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Loren Z" <anonymous@discussions.microsoft.com> wrote in message
>>> news:eZegIthCJHA.5316@TK2MSFTNGP04.phx.gbl...
>>>> We have an SQL Server backup job that backups up a database every two
>>>> minutes. We also have an alert where if the percent log used is greater
>>>> then 75 then the response is to run this same backup job.
>>>>
>>>> The backup file is named as such:
>>>>
>>>> Databasename_backup_YYYYMMDDHHMM.trn
>>>>
>>>> The database is named to the nearest minute.
>>>> We had a situation where the percent log used got greater then 75 and
>>>> this triggered the alert and the transaction log was backed up. Before
>>>> the minute increased the scheduled backup ran. As it is named to the
>>>> nearest minute, the scheduled backup over wrote the previous triggered
>>>> backup as they were named the same.
>>>>
>>>> The client asked us to do a restore and we were not able to do so in
>>>> that MSDB two files are in the history and they are named the same. Has
>>>> anyone else seen this?
>>>>
>>>
>>
>>
>

We have had the possibility to have several files in a backup since 1.0. So there's no behavior
changes in that aspect.

As I said earlier, the only thing that would cause an error is if the prior backup was performed
using EXPIREDATE or RETAINDAYS and the second one is done using INIT. I would be surprised if that
is how maint plan backups are done in 2000, I know I've used Profiler to trace the backup commands
some years ago, but I don't remember exactly what settings was used.

As for maint plan in 2005, we have the option to control EXPOREDATE and RETAINDAYS but they are both
not included by default. As for having INIT in there, we need to specify for the backup task to
*not* create files with timestamp in the name, but we instead select a backup device/file (an option
seldom used since we get no with backup generation handling).

:-)

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


"Chris Wood" <anonymous@microsoft.com> wrote in message
news:e9xaEmgDJHA.2484@TK2MSFTNGP06.phx.gbl...
> Thanks Tibor.
>
> It might have been in SQL2000 that gave the error. As I cannot see under the hood at what the SSIS
> package has for parameters I will assume that we could now have many 'files' in the log backup so
> a recovery would need to look at this aspect as well.
>
> Thanks
>
> Chris
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:%23TAPzjfDJHA.1628@TK2MSFTNGP02.phx.gbl...
>> Hi Chris,
>>
>> Why would build level come into play here? If you do a backup to a file which already exist, it
>> will quite simply be appended. Unless you specify INIT, which I'm pretty certain that main plan
>> do not specify. If you have INIT, then it will overwrite. No error will be produced in any case.
>>
>> An error *will* be produced if your earlier backup specified RETAINDAYS or EXPIREDATE and the
>> date when the backup will expire hasn't come yet. These options are mostly useless (TM) and
>> exposing these in maint wiz was a bad idea to start with since most users do not understand what
>> these options do and what they don't do.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Chris Wood" <anonymous@microsoft.com> wrote in message
>> news:eQFJlGdDJHA.5196@TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We are using a maintenance plan to generate the backup job running SQL2005 build 3239 (CU7). Did
>>> something change in the post SP2 builds in that I am sure we would have had a backup failure
>>> because of the duplicate name? Looks like it now creates file 1/file 2 etc into the same backup
>>> file.
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
>>> news:%23nIoHIiCJHA.5808@TK2MSFTNGP04.phx.gbl...
>>>> Did the backup commands have INIT or FORMAT? If not, you probably have two backup in the same
>>>> file. Verify using RESTORE HEADERONLY.
>>>>
>>>> --
>>>> Tibor Karaszi, SQL Server MVP
>>>> http://www.karaszi.com/sqlserver/default.asp
>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>
>>>>
>>>> "Loren Z" <anonymous@discussions.microsoft.com> wrote in message
>>>> news:eZegIthCJHA.5316@TK2MSFTNGP04.phx.gbl...
>>>>> We have an SQL Server backup job that backups up a database every two minutes. We also have an
>>>>> alert where if the percent log used is greater then 75 then the response is to run this same
>>>>> backup job.
>>>>>
>>>>> The backup file is named as such:
>>>>>
>>>>> Databasename_backup_YYYYMMDDHHMM.trn
>>>>>
>>>>> The database is named to the nearest minute.
>>>>> We had a situation where the percent log used got greater then 75 and this triggered the alert
>>>>> and the transaction log was backed up. Before the minute increased the scheduled backup ran.
>>>>> As it is named to the nearest minute, the scheduled backup over wrote the previous triggered
>>>>> backup as they were named the same.
>>>>>
>>>>> The client asked us to do a restore and we were not able to do so in that MSDB two files are
>>>>> in the history and they are named the same. Has anyone else seen this?
>>>>>
>>>>
>>>
>>>
>>
>
>