DB Backup Maint Plan - old file deletes

Hello. When using a DB Maint Plan to backup databases,
say 10 databases all in one plan... the job backs up each
database and THEN at the end of the entire job, it deletes
the previous backups (based on your backup retention).
question>> is there any way to backup the database, and
RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
backup files? The problem is that you need enough disk
space to hold the current and old backups for ALL
databases being backed up, unless there's some setting
we're missing? Any thoughts on that? (using DB Maint
Plans, I know we could code things ourselves and NOT use
Maint Plans)... THanks, BruceThis is a multi-part message in MIME format.
--=_NextPart_000_03A4_01C3B337.52A2A5B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
If you're cramped for space, consider replacing the single maintenance plan
with one plan for each DB. This way, the old backup for a database is
removed before the next plan kicks in.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
Hello. When using a DB Maint Plan to backup databases,
say 10 databases all in one plan... the job backs up each
database and THEN at the end of the entire job, it deletes
the previous backups (based on your backup retention).
question>> is there any way to backup the database, and
RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
backup files? The problem is that you need enough disk
space to hold the current and old backups for ALL
databases being backed up, unless there's some setting
we're missing? Any thoughts on that? (using DB Maint
Plans, I know we could code things ourselves and NOT use
Maint Plans)... THanks, Bruce
--=_NextPart_000_03A4_01C3B337.52A2A5B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If you're cramped for space, consider =replacing the single maintenance plan with one plan for each DB. This way, =the old backup for a database is removed before the next plan kicks =in.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce de Freitas" wrote in =message news:0fca01c3b360$91=c86e90$a401280a@.phx.gbl...Hello. When using a DB Maint Plan to backup databases, say 10 databases all =in one plan... the job backs up each database and THEN at the end of the =entire job, it deletes the previous backups (based on your backup =retention). question>> is there any way to backup the database, =and RIGHT THEN, in a Maint Plan job, delete the PREVIOUS backup =files? The problem is that you need enough disk space to hold the current =and old backups for ALL databases being backed up, unless there's some =setting we're missing? Any thoughts on that? (using DB Maint =Plans, I know we could code things ourselves and NOT use Maint Plans)... =THanks, Bruce

---

--=_NextPart_000_03A4_01C3B337.52A2A5B0--|||Yep, we talked about that as a possibility. It's nice to
have DB backups in one Maint Plan though sometimes, and
was wondering if there was a way to do that. THanks
Tom... Bruce
>--Original Message--
>If you're cramped for space, consider replacing the
single maintenance plan
>with one plan for each DB. This way, the old backup for
a database is
>removed before the next plan kicks in.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
>Hello. When using a DB Maint Plan to backup databases,
>say 10 databases all in one plan... the job backs up each
>database and THEN at the end of the entire job, it deletes
>the previous backups (based on your backup retention).
>question>> is there any way to backup the database, and
>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
>backup files? The problem is that you need enough disk
>space to hold the current and old backups for ALL
>databases being backed up, unless there's some setting
>we're missing? Any thoughts on that? (using DB Maint
>Plans, I know we could code things ourselves and NOT use
>Maint Plans)... THanks, Bruce
>|||This is a multi-part message in MIME format.
--=_NextPart_000_048B_01C3B33F.F3F4BCC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Maintenance plans give you ease of use, but that does not always translate
to "most efficient".
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:109a01c3b368$e3450d70$a401280a@.phx.gbl...
Yep, we talked about that as a possibility. It's nice to
have DB backups in one Maint Plan though sometimes, and
was wondering if there was a way to do that. THanks
Tom... Bruce
>--Original Message--
>If you're cramped for space, consider replacing the
single maintenance plan
>with one plan for each DB. This way, the old backup for
a database is
>removed before the next plan kicks in.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...
>Hello. When using a DB Maint Plan to backup databases,
>say 10 databases all in one plan... the job backs up each
>database and THEN at the end of the entire job, it deletes
>the previous backups (based on your backup retention).
>question>> is there any way to backup the database, and
>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS
>backup files? The problem is that you need enough disk
>space to hold the current and old backups for ALL
>databases being backed up, unless there's some setting
>we're missing? Any thoughts on that? (using DB Maint
>Plans, I know we could code things ourselves and NOT use
>Maint Plans)... THanks, Bruce
>
--=_NextPart_000_048B_01C3B33F.F3F4BCC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Maintenance plans give you ease of =use, but that does not always translate to "most efficient".
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bruce de Freitas" wrote in =message news:109a01c3b368$e3=450d70$a401280a@.phx.gbl...Yep, we talked about that as a possibility. It's nice to have DB =backups in one Maint Plan though sometimes, and was wondering if there was a =way to do that. THanks Tom... Bruce>--Original Message-->If you're cramped for space, consider replacing the single maintenance plan>with one plan for each DB. This =way, the old backup for a database is>removed before the next plan =kicks in.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Bruc=e de Freitas" wrote in message>news:0fca01c3b360$91c86e90$a401280a@.phx.gbl...>Hell=o. When using a DB Maint Plan to backup databases,>say 10 databases =all in one plan... the job backs up each>database and THEN at the end of =the entire job, it deletes>the previous backups (based on your backup =retention).>>question>> is there any way to =backup the database, and>RIGHT THEN, in a Maint Plan job, delete the PREVIOUS>backup files? The problem is that you need enough disk>space to hold the current and old backups for =ALL>databases being backed up, unless there's some setting>we're missing? =Any thoughts on that? (using DB Maint>Plans, I know we could =code things ourselves and NOT use>Maint Plans)... THanks, Bruce>

---

--=_NextPart_000_048B_01C3B33F.F3F4BCC0--

DB backup job for DB maintenance plan failed

I have few doubts as below state:
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS KhorKhor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khor

DB backup job for DB maintenance plan failed

I have few doubts as below state:
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS Khor
Khor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khor

DB backup job for DB maintenance plan failed

I have few doubts as below state:
1) What's the cause for DB backup job for DB maintenance plan failed?
2) What's the measure to correct this problem?
Thanks in advance.
MS KhorKhor,
What's in the:
1) SQL Server error log
2) SQL Agent error log
3) Windows Event log
Right click the maintenance plan, select History, and double click any
errors you see there. What do they say? Paste the text to this group.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Khor wrote:
> I have few doubts as below state:
> 1) What's the cause for DB backup job for DB maintenance plan failed?
> 2) What's the measure to correct this problem?
> Thanks in advance.
> MS Khorsql

DB Backup Job for a Maintenance Plan taking a long time

Hello,
I am trying to schedule complete backups of some of the databases that I
have on my database server using the Database Maintenance Plan. However,
when the DB Backup Job is run for backing up the database, it just keeps on
executing (status). There is no backup file created for any of the database
that I have chosen.
However, if I can manually backup each of the database by Right-clicking
each database in Enterprise Manager and choosing 'Backup Database'.
For some reason, the same backup process fails / takes very very long time
when I use a scheduled job created from the Database Maintenance Plan.
Any ideas?
Thanks
What's in the maintenance report? Any errors ? Where are you backing up
to ? Disk or Tape. If it is tape, did you check that the path to the
default backup directory exists ?
Markus
|||Sorry, that last comment should have been "IF yo backup to DISK (not
tape) check if the path exists"
M
|||I have the same problem (SQL7), except that the backup file is created (in
about a minute and a half) but the SQL Agent Job never completes. I have to
manually stop and restart SQL agent for the job status to return to "not
running". I have checked the backup and it is a valid file (restores in
about three minutes).
Job was created using Maintenance plan, backing up to device (filepath) on
the server.
The actual command in the job step is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
-BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
"BAK"'
"Vince Keller" wrote:

> Hello,
> I am trying to schedule complete backups of some of the databases that I
> have on my database server using the Database Maintenance Plan. However,
> when the DB Backup Job is run for backing up the database, it just keeps on
> executing (status). There is no backup file created for any of the database
> that I have chosen.
> However, if I can manually backup each of the database by Right-clicking
> each database in Enterprise Manager and choosing 'Backup Database'.
> For some reason, the same backup process fails / takes very very long time
> when I use a scheduled job created from the Database Maintenance Plan.
> Any ideas?
> Thanks
>
>
|||Follow up:
The problem persisted, so I removed the backup from the maintenance plan and
created it as a stand alone job. There are several ways to do this, the
simplest being to go to enterprise manage, expand your server, expand
Management, and and right-click on Backup. Select backup database, and make
sure that the schedule checkbox is selected (at the bottom of the dialogue).
Now the job runs fine. The remaining jobs of the orignial maintenance plan
show the same errant behavior, though... never quite completing.
"WD Rudman" wrote:
[vbcol=seagreen]
> I have the same problem (SQL7), except that the backup file is created (in
> about a minute and a half) but the SQL Agent Job never completes. I have to
> manually stop and restart SQL agent for the job status to return to "not
> running". I have checked the backup and it is a valid file (restores in
> about three minutes).
> Job was created using Maintenance plan, backing up to device (filepath) on
> the server.
> The actual command in the job step is:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
> -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
> "BAK"'
> "Vince Keller" wrote:

DB Backup Job for a Maintenance Plan taking a long time

Hello,
I am trying to schedule complete backups of some of the databases that I
have on my database server using the Database Maintenance Plan. However,
when the DB Backup Job is run for backing up the database, it just keeps on
executing (status). There is no backup file created for any of the database
that I have chosen.
However, if I can manually backup each of the database by Right-clicking
each database in Enterprise Manager and choosing 'Backup Database'.
For some reason, the same backup process fails / takes very very long time
when I use a scheduled job created from the Database Maintenance Plan.
Any ideas'
ThanksWhat's in the maintenance report? Any errors ? Where are you backing up
to ? Disk or Tape. If it is tape, did you check that the path to the
default backup directory exists ?
Markus|||Sorry, that last comment should have been "IF yo backup to DISK (not
tape) check if the path exists"
M|||I have the same problem (SQL7), except that the backup file is created (in
about a minute and a half) but the SQL Agent Job never completes. I have to
manually stop and restart SQL agent for the job status to return to "not
running". I have checked the backup and it is a valid file (restores in
about three minutes).
Job was created using Maintenance plan, backing up to device (filepath) on
the server.
The actual command in the job step is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
-BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
"BAK"'
"Vince Keller" wrote:
> Hello,
> I am trying to schedule complete backups of some of the databases that I
> have on my database server using the Database Maintenance Plan. However,
> when the DB Backup Job is run for backing up the database, it just keeps on
> executing (status). There is no backup file created for any of the database
> that I have chosen.
> However, if I can manually backup each of the database by Right-clicking
> each database in Enterprise Manager and choosing 'Backup Database'.
> For some reason, the same backup process fails / takes very very long time
> when I use a scheduled job created from the Database Maintenance Plan.
> Any ideas'
> Thanks
>
>|||Follow up:
The problem persisted, so I removed the backup from the maintenance plan and
created it as a stand alone job. There are several ways to do this, the
simplest being to go to enterprise manage, expand your server, expand
Management, and and right-click on Backup. Select backup database, and make
sure that the schedule checkbox is selected (at the bottom of the dialogue).
Now the job runs fine. The remaining jobs of the orignial maintenance plan
show the same errant behavior, though... never quite completing.
"WD Rudman" wrote:
> I have the same problem (SQL7), except that the backup file is created (in
> about a minute and a half) but the SQL Agent Job never completes. I have to
> manually stop and restart SQL agent for the job status to return to "not
> running". I have checked the backup and it is a valid file (restores in
> about three minutes).
> Job was created using Maintenance plan, backing up to device (filepath) on
> the server.
> The actual command in the job step is:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
> -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
> "BAK"'
> "Vince Keller" wrote:
> > Hello,
> >
> > I am trying to schedule complete backups of some of the databases that I
> > have on my database server using the Database Maintenance Plan. However,
> > when the DB Backup Job is run for backing up the database, it just keeps on
> > executing (status). There is no backup file created for any of the database
> > that I have chosen.
> >
> > However, if I can manually backup each of the database by Right-clicking
> > each database in Enterprise Manager and choosing 'Backup Database'.
> >
> > For some reason, the same backup process fails / takes very very long time
> > when I use a scheduled job created from the Database Maintenance Plan.
> >
> > Any ideas'
> >
> > Thanks
> >
> >
> >

DB Backup Job for a Maintenance Plan taking a long time

Hello,
I am trying to schedule complete backups of some of the databases that I
have on my database server using the Database Maintenance Plan. However,
when the DB Backup Job is run for backing up the database, it just keeps on
executing (status). There is no backup file created for any of the database
that I have chosen.
However, if I can manually backup each of the database by Right-clicking
each database in Enterprise Manager and choosing 'Backup Database'.
For some reason, the same backup process fails / takes very very long time
when I use a scheduled job created from the Database Maintenance Plan.
Any ideas'
ThanksWhat's in the maintenance report? Any errors ? Where are you backing up
to ? Disk or Tape. If it is tape, did you check that the path to the
default backup directory exists ?
Markus|||Sorry, that last comment should have been "IF yo backup to DISK (not
tape) check if the path exists"
M|||I have the same problem (SQL7), except that the backup file is created (in
about a minute and a half) but the SQL Agent Job never completes. I have to
manually stop and restart SQL agent for the job status to return to "not
running". I have checked the backup and it is a valid file (restores in
about three minutes).
Job was created using Maintenance plan, backing up to device (filepath) on
the server.
The actual command in the job step is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
-BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
"BAK"'
"Vince Keller" wrote:

> Hello,
> I am trying to schedule complete backups of some of the databases that I
> have on my database server using the Database Maintenance Plan. However,
> when the DB Backup Job is run for backing up the database, it just keeps o
n
> executing (status). There is no backup file created for any of the databas
e
> that I have chosen.
> However, if I can manually backup each of the database by Right-clicking
> each database in Enterprise Manager and choosing 'Backup Database'.
> For some reason, the same backup process fails / takes very very long time
> when I use a scheduled job created from the Database Maintenance Plan.
> Any ideas'
> Thanks
>
>|||Follow up:
The problem persisted, so I removed the backup from the maintenance plan and
created it as a stand alone job. There are several ways to do this, the
simplest being to go to enterprise manage, expand your server, expand
Management, and and right-click on Backup. Select backup database, and make
sure that the schedule checkbox is selected (at the bottom of the dialogue).
Now the job runs fine. The remaining jobs of the orignial maintenance plan
show the same errant behavior, though... never quite completing.
"WD Rudman" wrote:
[vbcol=seagreen]
> I have the same problem (SQL7), except that the backup file is created (in
> about a minute and a half) but the SQL Agent Job never completes. I have
to
> manually stop and restart SQL agent for the job status to return to "not
> running". I have checked the backup and it is a valid file (restores in
> about three minutes).
> Job was created using Maintenance plan, backing up to device (filepath) on
> the server.
> The actual command in the job step is:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 153F9792-4A76-44CD-944E-C581659E556D -To "Warren Rudman" -WriteHistory
> -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -CrBkSubDir -BkExt
> "BAK"'
> "Vince Keller" wrote:
>

DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

DB Backup Failure

Hello All,

I have created a Maintenance plan using Wizard, In this plan I am doing full backup of 12 DBs to share drive, I created only one step as full backup. And Scheduled this at 7 pm everyday, the
following is the error I am getting in the Logfile, can some one please through some light on this.

" failed with the following error: "Cannot open backup device
\\shq-ss2\sql backup\SQL_PRODDB\WorkFlow_backup_200708291911.bak. Operating
system error 5(Access is denied.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly.

SQL Server Instance and SQL Agent are running under domain admin account, Share Drive folder has full control by domain admin.

I am the local admin on the SQL Server box, and I have read, write and delete, create permissions on the share drive for backups folder.

What must be the issue?

Thanks

Siebel

Can you try to run the backup of a db using the backup database syntax instead of job and tell us the results........if you have modify privilege in the backup folder it should work fine when you run the backup script as sql server will take your authentication (windows)..........in your case i guess its related to privileges for the shared folder for the startup account as the error is pretty expicit but if your sure try executing the script.........
|||

I am getting same error when I use the DB Backup syntax, I have full control on the shared folder.

What else could be the issue?

Thanks

Siebel

|||

How did you log on to the server, do you use SQL Server or Windows authentication ?

Jens K. Suessmeyer

http://www.sqlserver2005.de