Hello:
In continuing with a posting that I made yesterday here on the Newsgroups, I
have today reviewed the SQL Error Log for a database maintenance plan that I
have that backups my databases to an external hard drive.
The backup to this external hard drive is failing for all of the databases!
The error in the log report says "The system cannot find the path specified".
The drive letter of the hard drive is Q and, therefore, the path to the
backup device (external hard drive) is "Q:".
I tested this a few weeks ago and it worked. But, when we put the plan into
production to run overnight, it failed.
Should I UNC the pathing to the external hard drive? Is there any other
reason as to why it would not be able to find the hard drive?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
> In continuing with a posting that I made yesterday here on the Newsgroups, I
> have today reviewed the SQL Error Log for a database maintenance plan that I
> have that backups my databases to an external hard drive.
> The backup to this external hard drive is failing for all of the databases!
> The error in the log report says "The system cannot find the path specified".
> The drive letter of the hard drive is Q and, therefore, the path to the
> backup device (external hard drive) is "Q:".
> I tested this a few weeks ago and it worked. But, when we put the plan into
> production to run overnight, it failed.
> Should I UNC the pathing to the external hard drive? Is there any other
> reason as to why it would not be able to find the hard drive?
> Thanks!
> childofthe1980s
Is this external drive a USB disc? If so can you see it when logged in as
the the service account?
Does a step with that calls xp_fixeddrives return the drive information?
John
Showing posts with label specified. Show all posts
Showing posts with label specified. Show all posts
Sunday, March 11, 2012
Backup plan cannot find the path specified
Hello:
In continuing with a posting that I made yesterday here on the Newsgroups, I
have today reviewed the SQL Error Log for a database maintenance plan that I
have that backups my databases to an external hard drive.
The backup to this external hard drive is failing for all of the databases!
The error in the log report says "The system cannot find the path specified"
.
The drive letter of the hard drive is Q and, therefore, the path to the
backup device (external hard drive) is "Q:".
I tested this a few weeks ago and it worked. But, when we put the plan into
production to run overnight, it failed.
Should I UNC the pathing to the external hard drive? Is there any other
reason as to why it would not be able to find the hard drive?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
> In continuing with a posting that I made yesterday here on the Newsgroups,
I
> have today reviewed the SQL Error Log for a database maintenance plan that
I
> have that backups my databases to an external hard drive.
> The backup to this external hard drive is failing for all of the databases
!
> The error in the log report says "The system cannot find the path specifie
d".
> The drive letter of the hard drive is Q and, therefore, the path to the
> backup device (external hard drive) is "Q:".
> I tested this a few weeks ago and it worked. But, when we put the plan in
to
> production to run overnight, it failed.
> Should I UNC the pathing to the external hard drive? Is there any other
> reason as to why it would not be able to find the hard drive?
> Thanks!
> childofthe1980s
Is this external drive a USB disc? If so can you see it when logged in as
the the service account?
Does a step with that calls xp_fixeddrives return the drive information?
John
In continuing with a posting that I made yesterday here on the Newsgroups, I
have today reviewed the SQL Error Log for a database maintenance plan that I
have that backups my databases to an external hard drive.
The backup to this external hard drive is failing for all of the databases!
The error in the log report says "The system cannot find the path specified"
.
The drive letter of the hard drive is Q and, therefore, the path to the
backup device (external hard drive) is "Q:".
I tested this a few weeks ago and it worked. But, when we put the plan into
production to run overnight, it failed.
Should I UNC the pathing to the external hard drive? Is there any other
reason as to why it would not be able to find the hard drive?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
> In continuing with a posting that I made yesterday here on the Newsgroups,
I
> have today reviewed the SQL Error Log for a database maintenance plan that
I
> have that backups my databases to an external hard drive.
> The backup to this external hard drive is failing for all of the databases
!
> The error in the log report says "The system cannot find the path specifie
d".
> The drive letter of the hard drive is Q and, therefore, the path to the
> backup device (external hard drive) is "Q:".
> I tested this a few weeks ago and it worked. But, when we put the plan in
to
> production to run overnight, it failed.
> Should I UNC the pathing to the external hard drive? Is there any other
> reason as to why it would not be able to find the hard drive?
> Thanks!
> childofthe1980s
Is this external drive a USB disc? If so can you see it when logged in as
the the service account?
Does a step with that calls xp_fixeddrives return the drive information?
John
Backup plan cannot find the path specified
Hello:
In continuing with a posting that I made yesterday here on the Newsgroups, I
have today reviewed the SQL Error Log for a database maintenance plan that I
have that backups my databases to an external hard drive.
The backup to this external hard drive is failing for all of the databases!
The error in the log report says "The system cannot find the path specified".
The drive letter of the hard drive is Q and, therefore, the path to the
backup device (external hard drive) is "Q:".
I tested this a few weeks ago and it worked. But, when we put the plan into
production to run overnight, it failed.
Should I UNC the pathing to the external hard drive? Is there any other
reason as to why it would not be able to find the hard drive?
Thanks!
childofthe1980s
Hi
"childofthe1980s" wrote:
> Hello:
> In continuing with a posting that I made yesterday here on the Newsgroups, I
> have today reviewed the SQL Error Log for a database maintenance plan that I
> have that backups my databases to an external hard drive.
> The backup to this external hard drive is failing for all of the databases!
> The error in the log report says "The system cannot find the path specified".
> The drive letter of the hard drive is Q and, therefore, the path to the
> backup device (external hard drive) is "Q:".
> I tested this a few weeks ago and it worked. But, when we put the plan into
> production to run overnight, it failed.
> Should I UNC the pathing to the external hard drive? Is there any other
> reason as to why it would not be able to find the hard drive?
> Thanks!
> childofthe1980s
Is this external drive a USB disc? If so can you see it when logged in as
the the service account?
Does a step with that calls xp_fixeddrives return the drive information?
John
In continuing with a posting that I made yesterday here on the Newsgroups, I
have today reviewed the SQL Error Log for a database maintenance plan that I
have that backups my databases to an external hard drive.
The backup to this external hard drive is failing for all of the databases!
The error in the log report says "The system cannot find the path specified".
The drive letter of the hard drive is Q and, therefore, the path to the
backup device (external hard drive) is "Q:".
I tested this a few weeks ago and it worked. But, when we put the plan into
production to run overnight, it failed.
Should I UNC the pathing to the external hard drive? Is there any other
reason as to why it would not be able to find the hard drive?
Thanks!
childofthe1980s
Hi
"childofthe1980s" wrote:
> Hello:
> In continuing with a posting that I made yesterday here on the Newsgroups, I
> have today reviewed the SQL Error Log for a database maintenance plan that I
> have that backups my databases to an external hard drive.
> The backup to this external hard drive is failing for all of the databases!
> The error in the log report says "The system cannot find the path specified".
> The drive letter of the hard drive is Q and, therefore, the path to the
> backup device (external hard drive) is "Q:".
> I tested this a few weeks ago and it worked. But, when we put the plan into
> production to run overnight, it failed.
> Should I UNC the pathing to the external hard drive? Is there any other
> reason as to why it would not be able to find the hard drive?
> Thanks!
> childofthe1980s
Is this external drive a USB disc? If so can you see it when logged in as
the the service account?
Does a step with that calls xp_fixeddrives return the drive information?
John
Thursday, February 16, 2012
Backup log isn't overwritten in SQL Server 2005
I have planned a job to backup the transaction log of a my db and then to
shrink it.
I have specified this script:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1.
The backup is executed days after days successfully, but it isn't overwritten.
The dimension of file ldf is maintained low.
I don't see any errors in the history of the job.
How can I solve this issue?
So, if I want to overwrite the backup of the log that expires after one day I
must specify this sintax:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1, INIT.
Is it correct?
Thanks
"Tibor Karaszi" wrote:
> You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
> you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
> backups in the backup set, it only generates an error if you try to INIT before that number of days.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>
|||Hello,
Looks good, this will overwrite the log file next day. Please correct the
spelling mistake in RETAINDAYS.
BACKUP LOG Sampledb TO DISK = 'C:\LogSampleDB.TRN' WITH RETAINDAYS = 1, INIT
Thanks
Hari
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...[vbcol=seagreen]
> So, if I want to overwrite the backup of the log that expires after one
> day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
> RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
> "Tibor Karaszi" wrote:
shrink it.
I have specified this script:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1.
The backup is executed days after days successfully, but it isn't overwritten.
The dimension of file ldf is maintained low.
I don't see any errors in the history of the job.
How can I solve this issue?
So, if I want to overwrite the backup of the log that expires after one day I
must specify this sintax:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1, INIT.
Is it correct?
Thanks
"Tibor Karaszi" wrote:
> You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
> you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
> backups in the backup set, it only generates an error if you try to INIT before that number of days.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>
|||Hello,
Looks good, this will overwrite the log file next day. Please correct the
spelling mistake in RETAINDAYS.
BACKUP LOG Sampledb TO DISK = 'C:\LogSampleDB.TRN' WITH RETAINDAYS = 1, INIT
Thanks
Hari
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...[vbcol=seagreen]
> So, if I want to overwrite the backup of the log that expires after one
> day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
> RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
> "Tibor Karaszi" wrote:
Backup log isn't overwritten in SQL Server 2005
I have planned a job to backup the transaction log of a my db and then to
shrink it.
I have specified this script:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1.
The backup is executed days after days successfully, but it isn't overwritten.
The dimension of file ldf is maintained low.
I don't see any errors in the history of the job.
How can I solve this issue?> The backup is executed days after days successfully, but it isn't overwritten.
You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
backups in the backup set, it only generates an error if you try to INIT before that number of days.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>I have planned a job to backup the transaction log of a my db and then to
> shrink it.
> I have specified this script:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> = 1.
> The backup is executed days after days successfully, but it isn't overwritten.
> The dimension of file ldf is maintained low.
> I don't see any errors in the history of the job.
> How can I solve this issue?|||So, if I want to overwrite the backup of the log that expires after one day I
must specify this sintax:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1, INIT.
Is it correct?
Thanks
"Tibor Karaszi" wrote:
> > The backup is executed days after days successfully, but it isn't overwritten.
> You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
> you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
> backups in the backup set, it only generates an error if you try to INIT before that number of days.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
> >I have planned a job to backup the transaction log of a my db and then to
> > shrink it.
> > I have specified this script:
> > BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> > = 1.
> > The backup is executed days after days successfully, but it isn't overwritten.
> > The dimension of file ldf is maintained low.
> > I don't see any errors in the history of the job.
> >
> > How can I solve this issue?
>|||Hello,
Looks good, this will overwrite the log file next day. Please correct the
spelling mistake in RETAINDAYS.
BACKUP LOG Sampledb TO DISK = 'C:\LogSampleDB.TRN' WITH RETAINDAYS = 1, INIT
Thanks
Hari
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one
> day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
> RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
> "Tibor Karaszi" wrote:
>> > The backup is executed days after days successfully, but it isn't
>> > overwritten.
>> You didn't tell it to overwrite. NOINIT is the default which means
>> append. If you want to overwrite
>> you specify INIT (or FORMAT). RETAINDAYS does not have anything with
>> automatically keeping that many
>> backups in the backup set, it only generates an error if you try to INIT
>> before that number of days.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>> >I have planned a job to backup the transaction log of a my db and then
>> >to
>> > shrink it.
>> > I have specified this script:
>> > BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
>> > RETAINAYS
>> > = 1.
>> > The backup is executed days after days successfully, but it isn't
>> > overwritten.
>> > The dimension of file ldf is maintained low.
>> > I don't see any errors in the history of the job.
>> >
>> > How can I solve this issue?
>>|||That command will error if you try to overwrite and the number of days hasn't happened yet, and no
backup will be produced! If you want to have several generations of a backup, you need to work with
several backup devices... See below script to demonstrate the issue:
alter database pubs set recovery full
backup database pubs to disk = 'nul'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
WAITFOR DELAY '00:00:10'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
GO
RESTORE HEADERONLY FROM disk = 'C:\Xpubslog.bak'
Note that above backup device has only one backup on it, not two!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
shrink it.
I have specified this script:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1.
The backup is executed days after days successfully, but it isn't overwritten.
The dimension of file ldf is maintained low.
I don't see any errors in the history of the job.
How can I solve this issue?> The backup is executed days after days successfully, but it isn't overwritten.
You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
backups in the backup set, it only generates an error if you try to INIT before that number of days.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>I have planned a job to backup the transaction log of a my db and then to
> shrink it.
> I have specified this script:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> = 1.
> The backup is executed days after days successfully, but it isn't overwritten.
> The dimension of file ldf is maintained low.
> I don't see any errors in the history of the job.
> How can I solve this issue?|||So, if I want to overwrite the backup of the log that expires after one day I
must specify this sintax:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1, INIT.
Is it correct?
Thanks
"Tibor Karaszi" wrote:
> > The backup is executed days after days successfully, but it isn't overwritten.
> You didn't tell it to overwrite. NOINIT is the default which means append. If you want to overwrite
> you specify INIT (or FORMAT). RETAINDAYS does not have anything with automatically keeping that many
> backups in the backup set, it only generates an error if you try to INIT before that number of days.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
> >I have planned a job to backup the transaction log of a my db and then to
> > shrink it.
> > I have specified this script:
> > BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> > = 1.
> > The backup is executed days after days successfully, but it isn't overwritten.
> > The dimension of file ldf is maintained low.
> > I don't see any errors in the history of the job.
> >
> > How can I solve this issue?
>|||Hello,
Looks good, this will overwrite the log file next day. Please correct the
spelling mistake in RETAINDAYS.
BACKUP LOG Sampledb TO DISK = 'C:\LogSampleDB.TRN' WITH RETAINDAYS = 1, INIT
Thanks
Hari
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one
> day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
> RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
> "Tibor Karaszi" wrote:
>> > The backup is executed days after days successfully, but it isn't
>> > overwritten.
>> You didn't tell it to overwrite. NOINIT is the default which means
>> append. If you want to overwrite
>> you specify INIT (or FORMAT). RETAINDAYS does not have anything with
>> automatically keeping that many
>> backups in the backup set, it only generates an error if you try to INIT
>> before that number of days.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
>> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>> >I have planned a job to backup the transaction log of a my db and then
>> >to
>> > shrink it.
>> > I have specified this script:
>> > BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
>> > RETAINAYS
>> > = 1.
>> > The backup is executed days after days successfully, but it isn't
>> > overwritten.
>> > The dimension of file ldf is maintained low.
>> > I don't see any errors in the history of the job.
>> >
>> > How can I solve this issue?
>>|||That command will error if you try to overwrite and the number of days hasn't happened yet, and no
backup will be produced! If you want to have several generations of a backup, you need to work with
several backup devices... See below script to demonstrate the issue:
alter database pubs set recovery full
backup database pubs to disk = 'nul'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
WAITFOR DELAY '00:00:10'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
GO
RESTORE HEADERONLY FROM disk = 'C:\Xpubslog.bak'
Note that above backup device has only one backup on it, not two!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
Backup log isn't overwritten in SQL Server 2005
I have planned a job to backup the transaction log of a my db and then to
shrink it.
I have specified this script:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1.
The backup is executed days after days successfully, but it isn't overwritte
n.
The dimension of file ldf is maintained low.
I don't see any errors in the history of the job.
How can I solve this issue?> The backup is executed days after days successfully, but it isn't overwrit
ten.
You didn't tell it to overwrite. NOINIT is the default which means append. I
f you want to overwrite
you specify INIT (or FORMAT). RETAINDAYS does not have anything with automat
ically keeping that many
backups in the backup set, it only generates an error if you try to INIT bef
ore that number of days.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>I have planned a job to backup the transaction log of a my db and then to
> shrink it.
> I have specified this script:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAY
S
> = 1.
> The backup is executed days after days successfully, but it isn't overwrit
ten.
> The dimension of file ldf is maintained low.
> I don't see any errors in the history of the job.
> How can I solve this issue?|||So, if I want to overwrite the backup of the log that expires after one day
I
must specify this sintax:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1, INIT.
Is it correct?
Thanks
"Tibor Karaszi" wrote:
> You didn't tell it to overwrite. NOINIT is the default which means append.
If you want to overwrite
> you specify INIT (or FORMAT). RETAINDAYS does not have anything with autom
atically keeping that many
> backups in the backup set, it only generates an error if you try to INIT b
efore that number of days.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>|||Hello,
Looks good, this will overwrite the log file next day. Please correct the
spelling mistake in RETAINDAYS.
BACKUP LOG Sampledb TO DISK = 'C:\LogSampleDB.TRN' WITH RETAINDAYS = 1, INIT
Thanks
Hari
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...[vbcol=seagreen]
> So, if I want to overwrite the backup of the log that expires after one
> day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
> RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
> "Tibor Karaszi" wrote:
>|||That command will error if you try to overwrite and the number of days hasn'
t happened yet, and no
backup will be produced! If you want to have several generations of a backup
, you need to work with
several backup devices... See below script to demonstrate the issue:
alter database pubs set recovery full
backup database pubs to disk = 'nul'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
WAITFOR DELAY '00:00:10'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
GO
RESTORE HEADERONLY FROM disk = 'C:\Xpubslog.bak'
Note that above backup device has only one backup on it, not two!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one da
y I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAY
S
> = 1, INIT.
> Is it correct?
> Thanks
>
shrink it.
I have specified this script:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1.
The backup is executed days after days successfully, but it isn't overwritte
n.
The dimension of file ldf is maintained low.
I don't see any errors in the history of the job.
How can I solve this issue?> The backup is executed days after days successfully, but it isn't overwrit
ten.
You didn't tell it to overwrite. NOINIT is the default which means append. I
f you want to overwrite
you specify INIT (or FORMAT). RETAINDAYS does not have anything with automat
ically keeping that many
backups in the backup set, it only generates an error if you try to INIT bef
ore that number of days.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>I have planned a job to backup the transaction log of a my db and then to
> shrink it.
> I have specified this script:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAY
S
> = 1.
> The backup is executed days after days successfully, but it isn't overwrit
ten.
> The dimension of file ldf is maintained low.
> I don't see any errors in the history of the job.
> How can I solve this issue?|||So, if I want to overwrite the backup of the log that expires after one day
I
must specify this sintax:
BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAYS
= 1, INIT.
Is it correct?
Thanks
"Tibor Karaszi" wrote:
> You didn't tell it to overwrite. NOINIT is the default which means append.
If you want to overwrite
> you specify INIT (or FORMAT). RETAINDAYS does not have anything with autom
atically keeping that many
> backups in the backup set, it only generates an error if you try to INIT b
efore that number of days.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
> news:994509B4-16C1-4C3D-A6E9-82A48266789F@.microsoft.com...
>|||Hello,
Looks good, this will overwrite the log file next day. Please correct the
spelling mistake in RETAINDAYS.
BACKUP LOG Sampledb TO DISK = 'C:\LogSampleDB.TRN' WITH RETAINDAYS = 1, INIT
Thanks
Hari
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...[vbcol=seagreen]
> So, if I want to overwrite the backup of the log that expires after one
> day I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH
> RETAINAYS
> = 1, INIT.
> Is it correct?
> Thanks
>
> "Tibor Karaszi" wrote:
>|||That command will error if you try to overwrite and the number of days hasn'
t happened yet, and no
backup will be produced! If you want to have several generations of a backup
, you need to work with
several backup devices... See below script to demonstrate the issue:
alter database pubs set recovery full
backup database pubs to disk = 'nul'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
WAITFOR DELAY '00:00:10'
backup log pubs to disk = 'C:\Xpubslog.bak' with retaindays = 1, INIT
GO
RESTORE HEADERONLY FROM disk = 'C:\Xpubslog.bak'
Note that above backup device has only one backup on it, not two!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:DD95C0D4-4A2C-4E0A-90B1-70DA07A4D894@.microsoft.com...
> So, if I want to overwrite the backup of the log that expires after one da
y I
> must specify this sintax:
> BACKUP LOG SampleDB TO DISK = 'D:\BACKUPLOG\LogSampleDB.TRN' WITH RETAINAY
S
> = 1, INIT.
> Is it correct?
> Thanks
>
Subscribe to:
Posts (Atom)