db error: 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--

Hi All,

I have a db backup job on SQL Server 2005 server that has a delete step that deletes a backup file that is located on the SQL Server 2000 server. Here is the step:

EXEC master..xp_cmdshell 'del \\DevServerName\Dev-bkups-db\BackupsDB\DBName\DBName_db*.bak /q'

The step doesn't create any errors but it doesn't delete the file. When I run the same command from the command prompt it deletes the file.

I can't figure out what is wrong. Any Idea?

Thanks.Try using Query Analyzer to execute:DECLARE @.foo INT
EXEC @.foo = master..xp_cmdshell 'del \\DevServerName\Dev-bkups-db\BackupsDB\DBName\DBName_db*.bak /q'
SELECT @.foo AS CmdErrorLevelBoth the output and the error level ought to give you clues. My first guess is that the Windows Login being used by xp_cmdshell doesn't have permissions.

-PatP|||I get an Access Denied error and CmdErrorLevel 1. How can I find out which account is used by xp_cmdshell? And what permissions does this accout need?|||it needs sysadmin rights. I think it is using whatever account is running the SQL agent. This account also needs write access to the destination.|||How can I find out which account is used by xp_cmdshell?
sp_xp_cmdshell_proxy_account (http://msdn2.microsoft.com/en-us/library/ms190359.aspx) is where you can set it.what permissions does this accout need?This is the $64,000 question. Microsoft recommends little or no permissions, and I generally agree with them because of the security risks.

I would recommend making the step in the backup job a command step instead of a SQL step. That way you can set the Windows Credentials there, and you don't need to open up xp_cmdshell at all.

-PatP|||How do you set the credentials?|||Check the sql agent service in services.msc. If it's running with a domain account, then that account needs to have access to delete from \\DevServerName\Dev-bkups-db\BackupsDB\DBName\. It doesn't necessarily need admin rights to DevServerName.|||It is running with a local system account.|||local system account can not access network resources.|||Which account should be SQL Agent service startup account?|||typically I have the network guys create a LAN account dedicated to this purpose with a password that does not expire. This last part is very important.|||Thanks for your help.|||How do you set the credentials?The easy way is to change the job owner. There are sneaky ways too, especially if you've installed the Windows Resource Kit or are willing to write a bit of VBA code.

-PatP|||Thrasymachus,

Forgot to ask what are the minimum rights does this account need?

Thursday, March 29, 2012

DB Backup Maint Plan - old file deletes

DB Backup Job

db error

Blog Archive

About Me