I am using SQL Server 2005. I have a Backup Database task which I use
to back up to a single backup device. I want to set a retention time
of 3 days and have old backups overwritten. This seems to be the WITH
INIT, NOSKIP problem. However using the wizard there doesn't seem to
be the possibility of doing this. I find it hard to believe though
that anyone would write a wizard which didn't offer this feature.
Is there any way to backup with NOSKIP or failing that alter the SQL
genereated by the wizard.
For info, here's the first few SQL statements from the wizard!
BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS =
3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS =
3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =
3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =
N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
etc
There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
backups. Either you overwrite everything, or you append. Period.
Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
INIT/NOINIT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186054522.882732.218720@.d55g2000hsg.googlegr oups.com...
>I am using SQL Server 2005. I have a Backup Database task which I use
> to back up to a single backup device. I want to set a retention time
> of 3 days and have old backups overwritten. This seems to be the WITH
> INIT, NOSKIP problem. However using the wizard there doesn't seem to
> be the possibility of doing this. I find it hard to believe though
> that anyone would write a wizard which didn't offer this feature.
> Is there any way to backup with NOSKIP or failing that alter the SQL
> genereated by the wizard.
> For info, here's the first few SQL statements from the wizard!
> BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS =
> 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS =
> 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =
> 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =
> N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> = 10
> GO
> etc
>
|||I would happily overwrite everything but there doesn't seem to be that
option either in the Wizard. You only have the option that it either
expires or it doesn't as whatever you do it always puts in SKIP into
the SQL. The only way you seem to be able to backup without having old
backups is to backup to individual files rather than using a backup
device as I am.
Rob
On 2 Aug, 12:58, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> backups. Either you overwrite everything, or you append. Period.
> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> INIT/NOINIT.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186054522.882732.218720@.d55g2000hsg.googlegr oups.com...
>
>
>
>
> - Show quoted text -
|||So what you want to do is to backup to the same backup device name and always overwrite?
In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
exists:" and I select "Overwrite".
Here are the backup commands executed, as catched by Profiler:
BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =
N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME =
N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
you want, ir are you saying that there's some problem with the TSQL above?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186057933.490529.68270@.l70g2000hse.googlegro ups.com...
>I would happily overwrite everything but there doesn't seem to be that
> option either in the Wizard. You only have the option that it either
> expires or it doesn't as whatever you do it always puts in SKIP into
> the SQL. The only way you seem to be able to backup without having old
> backups is to backup to individual files rather than using a backup
> device as I am.
> Rob
> On 2 Aug, 12:58, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>
|||I could not get the wizard to generate any SQL with SKIP in it.
However, the answer to my problems was to use a one-off backup file
rather than have a pre-allocated backup device. I could then move to
the file to somewhere else and start afresh with a new file the next
day. Thanks for the help anyway.
On 2 Aug, 14:19, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So what you want to do is to backup to the same backup device name and always overwrite?
> In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
> exists:" and I select "Overwrite".
> Here are the backup commands executed, as catched by Profiler:
> BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =
> N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME =
> N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
> you want, ir are you saying that there's some problem with the TSQL above?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186057933.490529.68270@.l70g2000hse.googlegro ups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
Showing posts with label maitenance. Show all posts
Showing posts with label maitenance. Show all posts
Friday, March 9, 2012
Maitenance plans and noskip
I am using SQL Server 2005. I have a Backup Database task which I use
to back up to a single backup device. I want to set a retention time
of 3 days and have old backups overwritten. This seems to be the WITH
INIT, NOSKIP problem. However using the wizard there doesn't seem to
be the possibility of doing this. I find it hard to believe though
that anyone would write a wizard which didn't offer this feature.
Is there any way to backup with NOSKIP or failing that alter the SQL
genereated by the wizard.
For info, here's the first few SQL statements from the wizard!
BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
etcThere's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
backups. Either you overwrite everything, or you append. Period.
Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
INIT/NOINIT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>I am using SQL Server 2005. I have a Backup Database task which I use
> to back up to a single backup device. I want to set a retention time
> of 3 days and have old backups overwritten. This seems to be the WITH
> INIT, NOSKIP problem. However using the wizard there doesn't seem to
> be the possibility of doing this. I find it hard to believe though
> that anyone would write a wizard which didn't offer this feature.
> Is there any way to backup with NOSKIP or failing that alter the SQL
> genereated by the wizard.
> For info, here's the first few SQL statements from the wizard!
> BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> = 10
> GO
> etc
>|||I would happily overwrite everything but there doesn't seem to be that
option either in the Wizard. You only have the option that it either
expires or it doesn't as whatever you do it always puts in SKIP into
the SQL. The only way you seem to be able to backup without having old
backups is to backup to individual files rather than using a backup
device as I am.
Rob
On 2 Aug, 12:58, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> backups. Either you overwrite everything, or you append. Period.
> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> INIT/NOINIT.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>
> >I am using SQL Server 2005. I have a Backup Database task which I use
> > to back up to a single backup device. I want to set a retention time
> > of 3 days and have old backups overwritten. This seems to be the WITH
> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> > be the possibility of doing this. I find it hard to believe though
> > that anyone would write a wizard which didn't offer this feature.
> > Is there any way to backup with NOSKIP or failing that alter the SQL
> > genereated by the wizard.
> > For info, here's the first few SQL statements from the wizard!
> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> > = 10
> > GO
> > etc- Hide quoted text -
> - Show quoted text -|||So what you want to do is to backup to the same backup device name and always overwrite?
In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
exists:" and I select "Overwrite".
Here are the backup commands executed, as catched by Profiler:
BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME =N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
you want, ir are you saying that there's some problem with the TSQL above?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>I would happily overwrite everything but there doesn't seem to be that
> option either in the Wizard. You only have the option that it either
> expires or it doesn't as whatever you do it always puts in SKIP into
> the SQL. The only way you seem to be able to backup without having old
> backups is to backup to individual files rather than using a backup
> device as I am.
> Rob
> On 2 Aug, 12:58, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
>> backups. Either you overwrite everything, or you append. Period.
>> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
>> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
>> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
>> everything
>> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
>> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
>> INIT/NOINIT.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <use...@.tynemarch.co.uk> wrote in message
>> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>>
>> >I am using SQL Server 2005. I have a Backup Database task which I use
>> > to back up to a single backup device. I want to set a retention time
>> > of 3 days and have old backups overwritten. This seems to be the WITH
>> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
>> > be the possibility of doing this. I find it hard to believe though
>> > that anyone would write a wizard which didn't offer this feature.
>> > Is there any way to backup with NOSKIP or failing that alter the SQL
>> > genereated by the wizard.
>> > For info, here's the first few SQL statements from the wizard!
>> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
>> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =>> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
>> > = 10
>> > GO
>> > etc- Hide quoted text -
>> - Show quoted text -
>|||I could not get the wizard to generate any SQL with SKIP in it.
However, the answer to my problems was to use a one-off backup file
rather than have a pre-allocated backup device. I could then move to
the file to somewhere else and start afresh with a new file the next
day. Thanks for the help anyway.
On 2 Aug, 14:19, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So what you want to do is to backup to the same backup device name and always overwrite?
> In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
> exists:" and I select "Overwrite".
> Here are the backup commands executed, as catched by Profiler:
> BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME => N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME => N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
> you want, ir are you saying that there's some problem with the TSQL above?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>
> >I would happily overwrite everything but there doesn't seem to be that
> > option either in the Wizard. You only have the option that it either
> > expires or it doesn't as whatever you do it always puts in SKIP into
> > the SQL. The only way you seem to be able to backup without having old
> > backups is to backup to individual files rather than using a backup
> > device as I am.
> > Rob
> > On 2 Aug, 12:58, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> >> backups. Either you overwrite everything, or you append. Period.
> >> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> >> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> >> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
> >> everything
> >> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> >> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> >> INIT/NOINIT.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <use...@.tynemarch.co.uk> wrote in message
> >>news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
> >> >I am using SQL Server 2005. I have a Backup Database task which I use
> >> > to back up to a single backup device. I want to set a retention time
> >> > of 3 days and have old backups overwritten. This seems to be the WITH
> >> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> >> > be the possibility of doing this. I find it hard to believe though
> >> > that anyone would write a wizard which didn't offer this feature.
> >> > Is there any way to backup with NOSKIP or failing that alter the SQL
> >> > genereated by the wizard.
> >> > For info, here's the first few SQL statements from the wizard!
> >> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> >> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => >> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> >> > = 10
> >> > GO
> >> > etc- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -
to back up to a single backup device. I want to set a retention time
of 3 days and have old backups overwritten. This seems to be the WITH
INIT, NOSKIP problem. However using the wizard there doesn't seem to
be the possibility of doing this. I find it hard to believe though
that anyone would write a wizard which didn't offer this feature.
Is there any way to backup with NOSKIP or failing that alter the SQL
genereated by the wizard.
For info, here's the first few SQL statements from the wizard!
BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
etcThere's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
backups. Either you overwrite everything, or you append. Period.
Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
INIT/NOINIT.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>I am using SQL Server 2005. I have a Backup Database task which I use
> to back up to a single backup device. I want to set a retention time
> of 3 days and have old backups overwritten. This seems to be the WITH
> INIT, NOSKIP problem. However using the wizard there doesn't seem to
> be the possibility of doing this. I find it hard to believe though
> that anyone would write a wizard which didn't offer this feature.
> Is there any way to backup with NOSKIP or failing that alter the SQL
> genereated by the wizard.
> For info, here's the first few SQL statements from the wizard!
> BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> = 10
> GO
> etc
>|||I would happily overwrite everything but there doesn't seem to be that
option either in the Wizard. You only have the option that it either
expires or it doesn't as whatever you do it always puts in SKIP into
the SQL. The only way you seem to be able to backup without having old
backups is to backup to individual files rather than using a backup
device as I am.
Rob
On 2 Aug, 12:58, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> backups. Either you overwrite everything, or you append. Period.
> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites everything
> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> INIT/NOINIT.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>
> >I am using SQL Server 2005. I have a Backup Database task which I use
> > to back up to a single backup device. I want to set a retention time
> > of 3 days and have old backups overwritten. This seems to be the WITH
> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> > be the possibility of doing this. I find it hard to believe though
> > that anyone would write a wizard which didn't offer this feature.
> > Is there any way to backup with NOSKIP or failing that alter the SQL
> > genereated by the wizard.
> > For info, here's the first few SQL statements from the wizard!
> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> > REWIND, NOUNLOAD, STATS = 10
> > GO
> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> > = 10
> > GO
> > etc- Hide quoted text -
> - Show quoted text -|||So what you want to do is to backup to the same backup device name and always overwrite?
In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
exists:" and I select "Overwrite".
Here are the backup commands executed, as catched by Profiler:
BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME =N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
you want, ir are you saying that there's some problem with the TSQL above?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>I would happily overwrite everything but there doesn't seem to be that
> option either in the Wizard. You only have the option that it either
> expires or it doesn't as whatever you do it always puts in SKIP into
> the SQL. The only way you seem to be able to backup without having old
> backups is to backup to individual files rather than using a backup
> device as I am.
> Rob
> On 2 Aug, 12:58, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
>> backups. Either you overwrite everything, or you append. Period.
>> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
>> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
>> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
>> everything
>> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
>> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
>> INIT/NOINIT.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <use...@.tynemarch.co.uk> wrote in message
>> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>>
>> >I am using SQL Server 2005. I have a Backup Database task which I use
>> > to back up to a single backup device. I want to set a retention time
>> > of 3 days and have old backups overwritten. This seems to be the WITH
>> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
>> > be the possibility of doing this. I find it hard to believe though
>> > that anyone would write a wizard which didn't offer this feature.
>> > Is there any way to backup with NOSKIP or failing that alter the SQL
>> > genereated by the wizard.
>> > For info, here's the first few SQL statements from the wizard!
>> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =>> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
>> > REWIND, NOUNLOAD, STATS = 10
>> > GO
>> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
>> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =>> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
>> > = 10
>> > GO
>> > etc- Hide quoted text -
>> - Show quoted text -
>|||I could not get the wizard to generate any SQL with SKIP in it.
However, the answer to my problems was to use a one-off backup file
rather than have a pre-allocated backup device. I could then move to
the file to somewhere else and start afresh with a new file the next
day. Thanks for the help anyway.
On 2 Aug, 14:19, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So what you want to do is to backup to the same backup device name and always overwrite?
> In my wizard (I'm on sp2, there has been improvements here...), I see an option "If backup files
> exists:" and I select "Overwrite".
> Here are the backup commands executed, as catched by Profiler:
> BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME => N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME => N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> As you see, it does INIT for the first backup and NOINIT for the following backups. Is above what
> you want, ir are you saying that there's some problem with the TSQL above?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>
> >I would happily overwrite everything but there doesn't seem to be that
> > option either in the Wizard. You only have the option that it either
> > expires or it doesn't as whatever you do it always puts in SKIP into
> > the SQL. The only way you seem to be able to backup without having old
> > backups is to backup to individual files rather than using a backup
> > device as I am.
> > Rob
> > On 2 Aug, 12:58, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> There's no way in SQL Server to backup to a single backup device and overwrite "only the oldest"
> >> backups. Either you overwrite everything, or you append. Period.
> >> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP can be confusing, and
> >> that it probably why there's a section in Books Online titled "Interaction of SKIP, NOSKIP, INIT,
> >> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server either overwrites
> >> everything
> >> or not at all. This is how the engine work, so it doesn't relate to the maintenance wizard.
> >> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options adding anything to
> >> INIT/NOINIT.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <use...@.tynemarch.co.uk> wrote in message
> >>news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
> >> >I am using SQL Server 2005. I have a Backup Database task which I use
> >> > to back up to a single backup device. I want to set a retention time
> >> > of 3 days and have old backups overwritten. This seems to be the WITH
> >> > INIT, NOSKIP problem. However using the wizard there doesn't seem to
> >> > be the possibility of doing this. I find it hard to believe though
> >> > that anyone would write a wizard which didn't offer this feature.
> >> > Is there any way to backup with NOSKIP or failing that alter the SQL
> >> > genereated by the wizard.
> >> > For info, here's the first few SQL statements from the wizard!
> >> > BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS => >> > 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> >> > REWIND, NOUNLOAD, STATS = 10
> >> > GO
> >> > BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> >> > RETAINDAYS = 3, NOFORMAT, NOINIT, NAME => >> > N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> >> > = 10
> >> > GO
> >> > etc- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -
Maitenance plans and noskip
I am using SQL Server 2005. I have a Backup Database task which I use
to back up to a single backup device. I want to set a retention time
of 3 days and have old backups overwritten. This seems to be the WITH
INIT, NOSKIP problem. However using the wizard there doesn't seem to
be the possibility of doing this. I find it hard to believe though
that anyone would write a wizard which didn't offer this feature.
Is there any way to backup with NOSKIP or failing that alter the SQL
genereated by the wizard.
For info, here's the first few SQL statements from the wizard!
BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS
=
3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS
=
3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =
3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =
N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
etcThere's no way in SQL Server to backup to a single backup device and overwri
te "only the oldest"
backups. Either you overwrite everything, or you append. Period.
Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP c
an be confusing, and
that it probably why there's a section in Books Online titled "Interaction o
f SKIP, NOSKIP, INIT,
and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server eithe
r overwrites everything
or not at all. This is how the engine work, so it doesn't relate to the main
tenance wizard.
Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options a
dding anything to
INIT/NOINIT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>I am using SQL Server 2005. I have a Backup Database task which I use
> to back up to a single backup device. I want to set a retention time
> of 3 days and have old backups overwritten. This seems to be the WITH
> INIT, NOSKIP problem. However using the wizard there doesn't seem to
> be the possibility of doing this. I find it hard to believe though
> that anyone would write a wizard which didn't offer this feature.
> Is there any way to backup with NOSKIP or failing that alter the SQL
> genereated by the wizard.
> For info, here's the first few SQL statements from the wizard!
> BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDA
YS =
> 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAY
S =
> 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS
=
> 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =
> N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> = 10
> GO
> etc
>|||I would happily overwrite everything but there doesn't seem to be that
option either in the Wizard. You only have the option that it either
expires or it doesn't as whatever you do it always puts in SKIP into
the SQL. The only way you seem to be able to backup without having old
backups is to backup to individual files rather than using a backup
device as I am.
Rob
On 2 Aug, 12:58, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There's no way in SQL Server to backup to a single backup device and overw
rite "only the oldest"
> backups. Either you overwrite everything, or you append. Period.
> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP
can be confusing, and
> that it probably why there's a section in Books Online titled "Interaction
of SKIP, NOSKIP, INIT,
> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server eit
her overwrites everything
> or not at all. This is how the engine work, so it doesn't relate to the ma
intenance wizard.
> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options
adding anything to
> INIT/NOINIT.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -|||So what you want to do is to backup to the same backup device name and alway
s overwrite?
In my wizard (I'm on sp2, there has been improvements here...), I see an opt
ion "If backup files
exists:" and I select "Overwrite".
Here are the backup commands executed, as catched by Profiler:
BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =
N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME
=
N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
As you see, it does INIT for the first backup and NOINIT for the following b
ackups. Is above what
you want, ir are you saying that there's some problem with the TSQL above?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>I would happily overwrite everything but there doesn't seem to be that
> option either in the Wizard. You only have the option that it either
> expires or it doesn't as whatever you do it always puts in SKIP into
> the SQL. The only way you seem to be able to backup without having old
> backups is to backup to individual files rather than using a backup
> device as I am.
> Rob
> On 2 Aug, 12:58, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>|||I could not get the wizard to generate any SQL with SKIP in it.
However, the answer to my problems was to use a one-off backup file
rather than have a pre-allocated backup device. I could then move to
the file to somewhere else and start afresh with a new file the next
day. Thanks for the help anyway.
On 2 Aug, 14:19, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So what you want to do is to backup to the same backup device name and alw
ays overwrite?
> In my wizard (I'm on sp2, there has been improvements here...), I see an o
ption "If backup files
> exists:" and I select "Overwrite".
> Here are the backup commands executed, as catched by Profiler:
> BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME
=
> N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAM
E =
> N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> As you see, it does INIT for the first backup and NOINIT for the following
backups. Is above what
> you want, ir are you saying that there's some problem with the TSQL above?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
to back up to a single backup device. I want to set a retention time
of 3 days and have old backups overwritten. This seems to be the WITH
INIT, NOSKIP problem. However using the wizard there doesn't seem to
be the possibility of doing this. I find it hard to believe though
that anyone would write a wizard which didn't offer this feature.
Is there any way to backup with NOSKIP or failing that alter the SQL
genereated by the wizard.
For info, here's the first few SQL statements from the wizard!
BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDAYS
=
3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAYS
=
3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS =
3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =
N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
= 10
GO
etcThere's no way in SQL Server to backup to a single backup device and overwri
te "only the oldest"
backups. Either you overwrite everything, or you append. Period.
Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP c
an be confusing, and
that it probably why there's a section in Books Online titled "Interaction o
f SKIP, NOSKIP, INIT,
and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server eithe
r overwrites everything
or not at all. This is how the engine work, so it doesn't relate to the main
tenance wizard.
Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options a
dding anything to
INIT/NOINIT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>I am using SQL Server 2005. I have a Backup Database task which I use
> to back up to a single backup device. I want to set a retention time
> of 3 days and have old backups overwritten. This seems to be the WITH
> INIT, NOSKIP problem. However using the wizard there doesn't seem to
> be the possibility of doing this. I find it hard to believe though
> that anyone would write a wizard which didn't offer this feature.
> Is there any way to backup with NOSKIP or failing that alter the SQL
> genereated by the wizard.
> For info, here's the first few SQL statements from the wizard!
> BACKUP DATABASE [master] TO [All299LocalDatabases] WITH RETAINDA
YS =
> 3, NOFORMAT, INIT, NAME = N'master_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [model] TO [All299LocalDatabases] WITH RETAINDAY
S =
> 3, NOFORMAT, NOINIT, NAME = N'model_backup_20070802123131', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [msdb] TO [All299LocalDatabases] WITH RETAINDAYS
=
> 3, NOFORMAT, NOINIT, NAME = N'msdb_backup_20070802123132', SKIP,
> REWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [ReportServer] TO [All299LocalDatabases] WITH
> RETAINDAYS = 3, NOFORMAT, NOINIT, NAME =
> N'ReportServer_backup_20070802123132', SKIP, REWIND, NOUNLOAD, STATS
> = 10
> GO
> etc
>|||I would happily overwrite everything but there doesn't seem to be that
option either in the Wizard. You only have the option that it either
expires or it doesn't as whatever you do it always puts in SKIP into
the SQL. The only way you seem to be able to backup without having old
backups is to backup to individual files rather than using a backup
device as I am.
Rob
On 2 Aug, 12:58, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There's no way in SQL Server to backup to a single backup device and overw
rite "only the oldest"
> backups. Either you overwrite everything, or you append. Period.
> Now, I understand that the interaction between INIT/NOINIT and SKIP/NOSKIP
can be confusing, and
> that it probably why there's a section in Books Online titled "Interaction
of SKIP, NOSKIP, INIT,
> and NOINIT" (in the BACKUP topic). Bu, as mentioned before, SQL Server eit
her overwrites everything
> or not at all. This is how the engine work, so it doesn't relate to the ma
intenance wizard.
> Personally,. I don't use SKIP/NOSKIP at all, as I don't find those options
adding anything to
> INIT/NOINIT.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186054522.882732.218720@.d55g2000hsg.googlegroups.com...
>
>
>
>
>
>
> - Show quoted text -|||So what you want to do is to backup to the same backup device name and alway
s overwrite?
In my wizard (I'm on sp2, there has been improvements here...), I see an opt
ion "If backup files
exists:" and I select "Overwrite".
Here are the backup commands executed, as catched by Profiler:
BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME =
N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAME
=
N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
As you see, it does INIT for the first backup and NOINIT for the following b
ackups. Is above what
you want, ir are you saying that there's some problem with the TSQL above?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<usenet@.tynemarch.co.uk> wrote in message
news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>I would happily overwrite everything but there doesn't seem to be that
> option either in the Wizard. You only have the option that it either
> expires or it doesn't as whatever you do it always puts in SKIP into
> the SQL. The only way you seem to be able to backup without having old
> backups is to backup to individual files rather than using a backup
> device as I am.
> Rob
> On 2 Aug, 12:58, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>|||I could not get the wizard to generate any SQL with SKIP in it.
However, the answer to my problems was to use a one-off backup file
rather than have a pre-allocated backup device. I could then move to
the file to somewhere else and start afresh with a new file the next
day. Thanks for the help anyway.
On 2 Aug, 14:19, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So what you want to do is to backup to the same backup device name and alw
ays overwrite?
> In my wizard (I'm on sp2, there has been improvements here...), I see an o
ption "If backup files
> exists:" and I select "Overwrite".
> Here are the backup commands executed, as catched by Profiler:
> BACKUP DATABASE [master] TO [MyDevice] WITH NOFORMAT, INIT, NAME
=
> N'master_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> BACKUP DATABASE [model] TO [MyDevice] WITH NOFORMAT, NOINIT, NAM
E =
> N'model_backup_20070802151715', SKIP, REWIND, NOUNLOAD, STATS = 10
> As you see, it does INIT for the first backup and NOINIT for the following
backups. Is above what
> you want, ir are you saying that there's some problem with the TSQL above?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> <use...@.tynemarch.co.uk> wrote in message
> news:1186057933.490529.68270@.l70g2000hse.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Maitenance Job
In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
BAK files are delted that are older than this, however the TRN backups are
not deleted. Why is this and how can I resolve it?Hi ,
Typically, the size of the transaction log file stabilizes when it can hold
the maximum number of transactions that can occur between transaction log
truncations that either checkpoints or transaction log backups trigger.
However, in some situations the transaction log may become very large and
run out of space or become full.
For more information, please refer to:
317375 A transaction log grows unexpectedly or becomes full on a computer
that is running SQL Server
http://support.microsoft.com/defaul...kb;EN-US;317375
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<georgedschneider@.news.postalias>[vbcol=seagreen]
week.[vbcol=seagreen]
are[vbcol=seagreen]|||Did you include databases in simple recovery model in the maint plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week
.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||what do you mean by simple recovery model
"Tibor Karaszi" wrote:
> Did you include databases in simple recovery model in the maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
>
>|||The tranactions logs are only like 84 kb when they are backuped up.
"George Schneider" wrote:
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week
.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||This is very well documented in Books Online (BOL), and I suggest you take t
he time to learn those
concepts:
BOL 2000, search for "Selecting a Recovery Model"
BOL 2005, search for "Choosing the Recovery Model for a Database"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:1EE7026E-7AB5-41E7-B5E6-F474B3B6A9E0@.microsoft.com...[vbcol=seagreen]
> what do you mean by simple recovery model
> "Tibor Karaszi" wrote:
>|||Hi George ,
How is everything going? Let me know if you still have concerns.
Have a good day.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<georgedschneider@.news.postalias>[vbcol=seagreen]
week.[vbcol=seagreen]
are[vbcol=seagreen]
BAK files are delted that are older than this, however the TRN backups are
not deleted. Why is this and how can I resolve it?Hi ,
Typically, the size of the transaction log file stabilizes when it can hold
the maximum number of transactions that can occur between transaction log
truncations that either checkpoints or transaction log backups trigger.
However, in some situations the transaction log may become very large and
run out of space or become full.
For more information, please refer to:
317375 A transaction log grows unexpectedly or becomes full on a computer
that is running SQL Server
http://support.microsoft.com/defaul...kb;EN-US;317375
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<georgedschneider@.news.postalias>[vbcol=seagreen]
week.[vbcol=seagreen]
are[vbcol=seagreen]|||Did you include databases in simple recovery model in the maint plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week
.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||what do you mean by simple recovery model
"Tibor Karaszi" wrote:
> Did you include databases in simple recovery model in the maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
>
>|||The tranactions logs are only like 84 kb when they are backuped up.
"George Schneider" wrote:
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week
.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||This is very well documented in Books Online (BOL), and I suggest you take t
he time to learn those
concepts:
BOL 2000, search for "Selecting a Recovery Model"
BOL 2005, search for "Choosing the Recovery Model for a Database"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:1EE7026E-7AB5-41E7-B5E6-F474B3B6A9E0@.microsoft.com...[vbcol=seagreen]
> what do you mean by simple recovery model
> "Tibor Karaszi" wrote:
>|||Hi George ,
How is everything going? Let me know if you still have concerns.
Have a good day.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<georgedschneider@.news.postalias>[vbcol=seagreen]
week.[vbcol=seagreen]
are[vbcol=seagreen]
Maitenance Job
In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
BAK files are delted that are older than this, however the TRN backups are
not deleted. Why is this and how can I resolve it?Hi ,
Typically, the size of the transaction log file stabilizes when it can hold
the maximum number of transactions that can occur between transaction log
truncations that either checkpoints or transaction log backups trigger.
However, in some situations the transaction log may become very large and
run out of space or become full.
For more information, please refer to:
317375 A transaction log grows unexpectedly or becomes full on a computer
that is running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZn26C5VxQ5EMvgQt6cXAn7hAS36A==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>Subject: Maitenance Job
>>Date: Mon, 24 Apr 2006 13:14:01 -0700
>>Lines: 3
>>Message-ID: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429048
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>>BAK files are delted that are older than this, however the TRN backups
are
>>not deleted. Why is this and how can I resolve it?|||Did you include databases in simple recovery model in the maint plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||what do you mean by simple recovery model
"Tibor Karaszi" wrote:
> Did you include databases in simple recovery model in the maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> > BAK files are delted that are older than this, however the TRN backups are
> > not deleted. Why is this and how can I resolve it?
>
>|||The tranactions logs are only like 84 kb when they are backuped up.
"George Schneider" wrote:
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||This is very well documented in Books Online (BOL), and I suggest you take the time to learn those
concepts:
BOL 2000, search for "Selecting a Recovery Model"
BOL 2005, search for "Choosing the Recovery Model for a Database"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:1EE7026E-7AB5-41E7-B5E6-F474B3B6A9E0@.microsoft.com...
> what do you mean by simple recovery model
> "Tibor Karaszi" wrote:
>> Did you include databases in simple recovery model in the maint plan?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "George Schneider" <georgedschneider@.news.postalias> wrote in message
>> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
>> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
>> > BAK files are delted that are older than this, however the TRN backups are
>> > not deleted. Why is this and how can I resolve it?
>>|||Hi George ,
How is everything going? Let me know if you still have concerns.
Have a good day.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZoZcp6c/Q/UTZhQfOLwJIjZnhIqA==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>References: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com>
>>Subject: RE: Maitenance Job
>>Date: Tue, 25 Apr 2006 05:43:02 -0700
>>Lines: 7
>>Message-ID: <C88CCAF2-A7E1-409B-96BA-11C7F6B060F0@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429121
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>The tranactions logs are only like 84 kb when they are backuped up.
>>"George Schneider" wrote:
>> In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>> BAK files are delted that are older than this, however the TRN backups
are
>> not deleted. Why is this and how can I resolve it?
BAK files are delted that are older than this, however the TRN backups are
not deleted. Why is this and how can I resolve it?Hi ,
Typically, the size of the transaction log file stabilizes when it can hold
the maximum number of transactions that can occur between transaction log
truncations that either checkpoints or transaction log backups trigger.
However, in some situations the transaction log may become very large and
run out of space or become full.
For more information, please refer to:
317375 A transaction log grows unexpectedly or becomes full on a computer
that is running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZn26C5VxQ5EMvgQt6cXAn7hAS36A==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>Subject: Maitenance Job
>>Date: Mon, 24 Apr 2006 13:14:01 -0700
>>Lines: 3
>>Message-ID: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429048
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>>BAK files are delted that are older than this, however the TRN backups
are
>>not deleted. Why is this and how can I resolve it?|||Did you include databases in simple recovery model in the maint plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||what do you mean by simple recovery model
"Tibor Karaszi" wrote:
> Did you include databases in simple recovery model in the maint plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Schneider" <georgedschneider@.news.postalias> wrote in message
> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> > BAK files are delted that are older than this, however the TRN backups are
> > not deleted. Why is this and how can I resolve it?
>
>|||The tranactions logs are only like 84 kb when they are backuped up.
"George Schneider" wrote:
> In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
> BAK files are delted that are older than this, however the TRN backups are
> not deleted. Why is this and how can I resolve it?|||This is very well documented in Books Online (BOL), and I suggest you take the time to learn those
concepts:
BOL 2000, search for "Selecting a Recovery Model"
BOL 2005, search for "Choosing the Recovery Model for a Database"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:1EE7026E-7AB5-41E7-B5E6-F474B3B6A9E0@.microsoft.com...
> what do you mean by simple recovery model
> "Tibor Karaszi" wrote:
>> Did you include databases in simple recovery model in the maint plan?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "George Schneider" <georgedschneider@.news.postalias> wrote in message
>> news:E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com...
>> > In my Maintenance job I specify to keep the BAK and TRN backups for 1 week.
>> > BAK files are delted that are older than this, however the TRN backups are
>> > not deleted. Why is this and how can I resolve it?
>>|||Hi George ,
How is everything going? Let me know if you still have concerns.
Have a good day.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Maitenance Job
>>thread-index: AcZoZcp6c/Q/UTZhQfOLwJIjZnhIqA==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>References: <E2EDCF6E-06F1-4FB2-9918-D80D57D971AF@.microsoft.com>
>>Subject: RE: Maitenance Job
>>Date: Tue, 25 Apr 2006 05:43:02 -0700
>>Lines: 7
>>Message-ID: <C88CCAF2-A7E1-409B-96BA-11C7F6B060F0@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:429121
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>The tranactions logs are only like 84 kb when they are backuped up.
>>"George Schneider" wrote:
>> In my Maintenance job I specify to keep the BAK and TRN backups for 1
week.
>> BAK files are delted that are older than this, however the TRN backups
are
>> not deleted. Why is this and how can I resolve it?
Subscribe to:
Posts (Atom)