Backups with Maintenance Plans

Hi
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJHi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:
> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>|||... and, of course, carefully consider whether you also want to do more frequent transaction log
backups. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:CE066399-68A6-469E-BBCC-2792BF430D85@.microsoft.com...
> Yeah, for only 2 GB do daily full backups including daily backups of your
> system databases.
> Hope this helps,
> Ben Nevarez
>
>
> "TJ" wrote:
>> ok, thanks
>> Yeah, the backups are about 2 Gigs, so I could just do them as daily
>> full backups.

Backups with Maintenance Plans

Hi
I am new to database administration, and I am considering my options
for backups. We are using SQL Server 2005 Standard.
I have done some reading and have determined the right backup scheme
for us is a weekly complete Simple backup with daily Differential
backups.
I would like to set this up with a Maintenance Plan. How do I do this?
- do I set up two Maintenance Plans, one with the weekly backup and
one with the differentials?
- or do I do this with one Plan?
- or is this not possible with Maintenance Plans, in which case I
should just schedule an agent with SQL scripts?
thanks,
TJ
Hi,
You can create a Maintenance Plan for the weekly full backups and another
one for the daily differential backups.
Also, if your databases are smaller than, let us say, 100 or 200 GB perhaps
you can try daily full backups, depending on your backup window.
Hope this helps,
Ben Nevarez
"TJ" wrote:

> Hi
> I am new to database administration, and I am considering my options
> for backups. We are using SQL Server 2005 Standard.
> I have done some reading and have determined the right backup scheme
> for us is a weekly complete Simple backup with daily Differential
> backups.
> I would like to set this up with a Maintenance Plan. How do I do this?
> - do I set up two Maintenance Plans, one with the weekly backup and
> one with the differentials?
> - or do I do this with one Plan?
> - or is this not possible with Maintenance Plans, in which case I
> should just schedule an agent with SQL scripts?
> thanks,
> TJ
>
|||ok, thanks
Yeah, the backups are about 2 Gigs, so I could just do them as daily
full backups.
|||Yeah, for only 2 GB do daily full backups including daily backups of your
system databases.
Hope this helps,
Ben Nevarez
"TJ" wrote:

> ok, thanks
> Yeah, the backups are about 2 Gigs, so I could just do them as daily
> full backups.
>

Backup/restore msdb to new server

SQL 2K5 SP1
Would this be the appropriate way to move maintenance plans from one server
to another? I have a tool that will sync the data in the tables between
servers for me, would this be sufficient? Are there any pitfalls between
backup/restore between 32-bit vs 64-bit versions?
Or is there server specific info there that would be broken by doing this?
TIATim
Personally I did not test it on SQL Server 2005 , it should not be a
problem in my opinion. Read in the BOL about restoring MSDB database
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uV%23YYzDFHHA.1188@.TK2MSFTNGP06.phx.gbl...
> SQL 2K5 SP1
> Would this be the appropriate way to move maintenance plans from one
> server to another? I have a tool that will sync the data in the tables
> between servers for me, would this be sufficient? Are there any pitfalls
> between backup/restore between 32-bit vs 64-bit versions?
> Or is there server specific info there that would be broken by doing this?
> TIA
>

Backup using the proxy account in SQL2005

Hi,
We want to use the proxy account to take backups created from maintenance
plans in SQL2005. What permissions does the proxy account need to do this?
It has permissions to run SSIS packages and it failed saying the package
could not be loaded. I then added the proxy account to the db_dtsadmin role
and now I get the package execution failed. Does it also need the master
role db_backupoperator to run?
Thanks
ChrisThe login in question need to have a user in the database you want to backup and in that database be
db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges.
I don't think that there's a privilege you can grant to a login that allow you to backup any
database, less than CONTROL (same as sysadmin). At least, that is how I read below:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We want to use the proxy account to take backups created from maintenance plans in SQL2005. What
> permissions does the proxy account need to do this? It has permissions to run SSIS packages and it
> failed saying the package could not be loaded. I then added the proxy account to the db_dtsadmin
> role and now I get the package execution failed. Does it also need the master role
> db_backupoperator to run?
> Thanks
> Chris
>|||Tibor,
I first granted Backup Database and then Backup Log to the proxy account and
added it to the application database in the db_backupoperator role. It
appears to perform the backup but still says package failed.
Any ideas?
Thanks
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
> The login in question need to have a user in the database you want to
> backup and in that database be db_backupoperator or have granted the
> BACKUP DATABASE and/or BACKUP LOG privileges.
> I don't think that there's a privilege you can grant to a login that allow
> you to backup any database, less than CONTROL (same as sysadmin). At
> least, that is how I read below:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We want to use the proxy account to take backups created from maintenance
>> plans in SQL2005. What permissions does the proxy account need to do
>> this? It has permissions to run SSIS packages and it failed saying the
>> package could not be loaded. I then added the proxy account to the
>> db_dtsadmin role and now I get the package execution failed. Does it also
>> need the master role db_backupoperator to run?
>> Thanks
>> Chris
>|||So the backup is produced but you get package failed. I suggest you post this to an SSIS group. Next
step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since I'm no
SSIS person, I don't have much to offer, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
> Tibor,
> I first granted Backup Database and then Backup Log to the proxy account and added it to the
> application database in the db_backupoperator role. It appears to perform the backup but still
> says package failed.
> Any ideas?
> Thanks
> Chris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The login in question need to have a user in the database you want to backup and in that database
>> be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG privileges.
>> I don't think that there's a privilege you can grant to a login that allow you to backup any
>> database, less than CONTROL (same as sysadmin). At least, that is how I read below:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We want to use the proxy account to take backups created from maintenance plans in SQL2005. What
>> permissions does the proxy account need to do this? It has permissions to run SSIS packages and
>> it failed saying the package could not be loaded. I then added the proxy account to the
>> db_dtsadmin role and now I get the package execution failed. Does it also need the master role
>> db_backupoperator to run?
>> Thanks
>> Chris
>>
>|||Thanks again Tibor.
I had looked in SSIS at the package and it had mentioned the Reader Role as
db_dtsadmin, db_dtsoperator and the creator of the package. I can see as
long as I give the proxy account one of the 2 db_dts roles in msdb it can
read/execute the package. I suppose I could change this to some other msdb
role and make sure the proxy account is a member of that role. I cannot see
much about the error. It throws an Event 12291 in the Event log saying the
package has failed. This is running SQL2005 SP1.
Thanks
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
> So the backup is produced but you get package failed. I suggest you post
> this to an SSIS group. Next step would be to investigate why the SSIS
> package fails, looking at SSIS log etc, but since I'm no SSIS person, I
> don't have much to offer, I'm afraid...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Tibor,
>> I first granted Backup Database and then Backup Log to the proxy account
>> and added it to the application database in the db_backupoperator role.
>> It appears to perform the backup but still says package failed.
>> Any ideas?
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The login in question need to have a user in the database you want to
>> backup and in that database be db_backupoperator or have granted the
>> BACKUP DATABASE and/or BACKUP LOG privileges.
>> I don't think that there's a privilege you can grant to a login that
>> allow you to backup any database, less than CONTROL (same as sysadmin).
>> At least, that is how I read below:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We want to use the proxy account to take backups created from
>> maintenance plans in SQL2005. What permissions does the proxy account
>> need to do this? It has permissions to run SSIS packages and it failed
>> saying the package could not be loaded. I then added the proxy account
>> to the db_dtsadmin role and now I get the package execution failed.
>> Does it also need the master role db_backupoperator to run?
>> Thanks
>> Chris
>>
>>
>|||The new SP2 Dec CTP does give a lot more info when I try to run this. It
complains about sp_maintplan_logentry. It mentions the Execute permission on
this msdb stored proc was denied.
Have I found a bug in DEC SP2?
Thanks
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Thanks again Tibor.
> I had looked in SSIS at the package and it had mentioned the Reader Role
> as db_dtsadmin, db_dtsoperator and the creator of the package. I can see
> as long as I give the proxy account one of the 2 db_dts roles in msdb it
> can read/execute the package. I suppose I could change this to some other
> msdb role and make sure the proxy account is a member of that role. I
> cannot see much about the error. It throws an Event 12291 in the Event log
> saying the package has failed. This is running SQL2005 SP1.
> Thanks
> Chris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>> So the backup is produced but you get package failed. I suggest you post
>> this to an SSIS group. Next step would be to investigate why the SSIS
>> package fails, looking at SSIS log etc, but since I'm no SSIS person, I
>> don't have much to offer, I'm afraid...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Tibor,
>> I first granted Backup Database and then Backup Log to the proxy account
>> and added it to the application database in the db_backupoperator role.
>> It appears to perform the backup but still says package failed.
>> Any ideas?
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The login in question need to have a user in the database you want to
>> backup and in that database be db_backupoperator or have granted the
>> BACKUP DATABASE and/or BACKUP LOG privileges.
>> I don't think that there's a privilege you can grant to a login that
>> allow you to backup any database, less than CONTROL (same as sysadmin).
>> At least, that is how I read below:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We want to use the proxy account to take backups created from
>> maintenance plans in SQL2005. What permissions does the proxy account
>> need to do this? It has permissions to run SSIS packages and it failed
>> saying the package could not be loaded. I then added the proxy account
>> to the db_dtsadmin role and now I get the package execution failed.
>> Does it also need the master role db_backupoperator to run?
>> Thanks
>> Chris
>>
>>
>|||> Have I found a bug in DEC SP2?
Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify
the steps needed), you probably want to post this to
http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OiFy8tIJHHA.3952@.TK2MSFTNGP02.phx.gbl...
> The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about
> sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied.
> Have I found a bug in DEC SP2?
> Thanks
> Chris
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> Thanks again Tibor.
>> I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin,
>> db_dtsoperator and the creator of the package. I can see as long as I give the proxy account one
>> of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change this to
>> some other msdb role and make sure the proxy account is a member of that role. I cannot see much
>> about the error. It throws an Event 12291 in the Event log saying the package has failed. This is
>> running SQL2005 SP1.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>> So the backup is produced but you get package failed. I suggest you post this to an SSIS group.
>> Next step would be to investigate why the SSIS package fails, looking at SSIS log etc, but since
>> I'm no SSIS person, I don't have much to offer, I'm afraid...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Tibor,
>> I first granted Backup Database and then Backup Log to the proxy account and added it to the
>> application database in the db_backupoperator role. It appears to perform the backup but still
>> says package failed.
>> Any ideas?
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The login in question need to have a user in the database you want to backup and in that
>> database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG
>> privileges.
>> I don't think that there's a privilege you can grant to a login that allow you to backup any
>> database, less than CONTROL (same as sysadmin). At least, that is how I read below:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We want to use the proxy account to take backups created from maintenance plans in SQL2005.
>> What permissions does the proxy account need to do this? It has permissions to run SSIS
>> packages and it failed saying the package could not be loaded. I then added the proxy account
>> to the db_dtsadmin role and now I get the package execution failed. Does it also need the
>> master role db_backupoperator to run?
>> Thanks
>> Chris
>>
>>
>>
>|||That's just what I was thinking of doing. I also had another error with
trying to access SSIS when SQL2005 is not the default instance.
Thanks
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23N718tNJHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Have I found a bug in DEC SP2?
> Possibly. I know MS are working hard on Maint Plans in SP2, so if you can
> create a repro (specify the steps needed), you probably want to post this
> to http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:OiFy8tIJHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The new SP2 Dec CTP does give a lot more info when I try to run this. It
>> complains about sp_maintplan_logentry. It mentions the Execute permission
>> on this msdb stored proc was denied.
>> Have I found a bug in DEC SP2?
>> Thanks
>> Chris
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> Thanks again Tibor.
>> I had looked in SSIS at the package and it had mentioned the Reader Role
>> as db_dtsadmin, db_dtsoperator and the creator of the package. I can see
>> as long as I give the proxy account one of the 2 db_dts roles in msdb it
>> can read/execute the package. I suppose I could change this to some
>> other msdb role and make sure the proxy account is a member of that
>> role. I cannot see much about the error. It throws an Event 12291 in the
>> Event log saying the package has failed. This is running SQL2005 SP1.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>> So the backup is produced but you get package failed. I suggest you
>> post this to an SSIS group. Next step would be to investigate why the
>> SSIS package fails, looking at SSIS log etc, but since I'm no SSIS
>> person, I don't have much to offer, I'm afraid...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Tibor,
>> I first granted Backup Database and then Backup Log to the proxy
>> account and added it to the application database in the
>> db_backupoperator role. It appears to perform the backup but still
>> says package failed.
>> Any ideas?
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The login in question need to have a user in the database you want to
>> backup and in that database be db_backupoperator or have granted the
>> BACKUP DATABASE and/or BACKUP LOG privileges.
>> I don't think that there's a privilege you can grant to a login that
>> allow you to backup any database, less than CONTROL (same as
>> sysadmin). At least, that is how I read below:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> We want to use the proxy account to take backups created from
>>> maintenance plans in SQL2005. What permissions does the proxy
>>> account need to do this? It has permissions to run SSIS packages and
>>> it failed saying the package could not be loaded. I then added the
>>> proxy account to the db_dtsadmin role and now I get the package
>>> execution failed. Does it also need the master role
>>> db_backupoperator to run?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>
>>
>>
>>
>|||> I also had another error with trying to access SSIS when SQL2005 is not the default instance.
Make sure you specify the correct instance name in the MsDtsSrvr.ini.xml file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uttfWBRJHHA.1248@.TK2MSFTNGP02.phx.gbl...
> That's just what I was thinking of doing. I also had another error with trying to access SSIS when
> SQL2005 is not the default instance.
> Thanks
> Chris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23N718tNJHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Have I found a bug in DEC SP2?
>> Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro (specify
>> the steps needed), you probably want to post this to
>> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:OiFy8tIJHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about
>> sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied.
>> Have I found a bug in DEC SP2?
>> Thanks
>> Chris
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> Thanks again Tibor.
>> I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin,
>> db_dtsoperator and the creator of the package. I can see as long as I give the proxy account
>> one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change
>> this to some other msdb role and make sure the proxy account is a member of that role. I cannot
>> see much about the error. It throws an Event 12291 in the Event log saying the package has
>> failed. This is running SQL2005 SP1.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>> So the backup is produced but you get package failed. I suggest you post this to an SSIS
>> group. Next step would be to investigate why the SSIS package fails, looking at SSIS log etc,
>> but since I'm no SSIS person, I don't have much to offer, I'm afraid...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Tibor,
>> I first granted Backup Database and then Backup Log to the proxy account and added it to the
>> application database in the db_backupoperator role. It appears to perform the backup but
>> still says package failed.
>> Any ideas?
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>>> The login in question need to have a user in the database you want to backup and in that
>>> database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG
>>> privileges.
>>>
>>> I don't think that there's a privilege you can grant to a login that allow you to backup any
>>> database, less than CONTROL (same as sysadmin). At least, that is how I read below:
>>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>>
>>>
>>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> We want to use the proxy account to take backups created from maintenance plans in SQL2005.
>>> What permissions does the proxy account need to do this? It has permissions to run SSIS
>>> packages and it failed saying the package could not be loaded. I then added the proxy
>>> account to the db_dtsadmin role and now I get the package execution failed. Does it also
>>> need the master role db_backupoperator to run?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>
>>
>>
>>
>|||Tibor,
It does not have any instance name in this xml file. Where should it go?
My environment is SQL2000 SP4 is the default. I have 3 named instances,
SQL2005 RTM, SQL2005SP1 and now SQL2005SP2.
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUkskTSJHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> I also had another error with trying to access SSIS when SQL2005 is not
>> the default instance.
> Make sure you specify the correct instance name in the MsDtsSrvr.ini.xml
> file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:uttfWBRJHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> That's just what I was thinking of doing. I also had another error with
>> trying to access SSIS when SQL2005 is not the default instance.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23N718tNJHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Have I found a bug in DEC SP2?
>> Possibly. I know MS are working hard on Maint Plans in SP2, so if you
>> can create a repro (specify the steps needed), you probably want to post
>> this to http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:OiFy8tIJHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The new SP2 Dec CTP does give a lot more info when I try to run this.
>> It complains about sp_maintplan_logentry. It mentions the Execute
>> permission on this msdb stored proc was denied.
>> Have I found a bug in DEC SP2?
>> Thanks
>> Chris
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> Thanks again Tibor.
>> I had looked in SSIS at the package and it had mentioned the Reader
>> Role as db_dtsadmin, db_dtsoperator and the creator of the package. I
>> can see as long as I give the proxy account one of the 2 db_dts roles
>> in msdb it can read/execute the package. I suppose I could change this
>> to some other msdb role and make sure the proxy account is a member of
>> that role. I cannot see much about the error. It throws an Event 12291
>> in the Event log saying the package has failed. This is running
>> SQL2005 SP1.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>> So the backup is produced but you get package failed. I suggest you
>> post this to an SSIS group. Next step would be to investigate why the
>> SSIS package fails, looking at SSIS log etc, but since I'm no SSIS
>> person, I don't have much to offer, I'm afraid...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>>> Tibor,
>>>
>>> I first granted Backup Database and then Backup Log to the proxy
>>> account and added it to the application database in the
>>> db_backupoperator role. It appears to perform the backup but still
>>> says package failed.
>>>
>>> Any ideas?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>>> wrote in message news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>>> The login in question need to have a user in the database you want
>>> to backup and in that database be db_backupoperator or have granted
>>> the BACKUP DATABASE and/or BACKUP LOG privileges.
>>>
>>> I don't think that there's a privilege you can grant to a login
>>> that allow you to backup any database, less than CONTROL (same as
>>> sysadmin). At least, that is how I read below:
>>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>>
>>>
>>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> We want to use the proxy account to take backups created from
>>> maintenance plans in SQL2005. What permissions does the proxy
>>> account need to do this? It has permissions to run SSIS packages
>>> and it failed saying the package could not be loaded. I then added
>>> the proxy account to the db_dtsadmin role and now I get the
>>> package execution failed. Does it also need the master role
>>> db_backupoperator to run?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>|||You most probably correctly have:
<ServerName>.</ServerName>
Say you want SSIS to work against an instance called X, then you should have:
<ServerName>.\X</ServerName>
I guess you could have MachineName\X as well, haven't tried that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%2388VRhSJHHA.448@.TK2MSFTNGP04.phx.gbl...
> Tibor,
> It does not have any instance name in this xml file. Where should it go?
> My environment is SQL2000 SP4 is the default. I have 3 named instances, SQL2005 RTM, SQL2005SP1
> and now SQL2005SP2.
> Chris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uUkskTSJHHA.4760@.TK2MSFTNGP03.phx.gbl...
>> I also had another error with trying to access SSIS when SQL2005 is not the default instance.
>> Make sure you specify the correct instance name in the MsDtsSrvr.ini.xml file.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uttfWBRJHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> That's just what I was thinking of doing. I also had another error with trying to access SSIS
>> when SQL2005 is not the default instance.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23N718tNJHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Have I found a bug in DEC SP2?
>> Possibly. I know MS are working hard on Maint Plans in SP2, so if you can create a repro
>> (specify the steps needed), you probably want to post this to
>> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:OiFy8tIJHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The new SP2 Dec CTP does give a lot more info when I try to run this. It complains about
>> sp_maintplan_logentry. It mentions the Execute permission on this msdb stored proc was denied.
>> Have I found a bug in DEC SP2?
>> Thanks
>> Chris
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> Thanks again Tibor.
>> I had looked in SSIS at the package and it had mentioned the Reader Role as db_dtsadmin,
>> db_dtsoperator and the creator of the package. I can see as long as I give the proxy account
>> one of the 2 db_dts roles in msdb it can read/execute the package. I suppose I could change
>> this to some other msdb role and make sure the proxy account is a member of that role. I
>> cannot see much about the error. It throws an Event 12291 in the Event log saying the package
>> has failed. This is running SQL2005 SP1.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>>> So the backup is produced but you get package failed. I suggest you post this to an SSIS
>>> group. Next step would be to investigate why the SSIS package fails, looking at SSIS log
>>> etc, but since I'm no SSIS person, I don't have much to offer, I'm afraid...
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>>
>>>
>>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>>> Tibor,
>>>
>>> I first granted Backup Database and then Backup Log to the proxy account and added it to
>>> the application database in the db_backupoperator role. It appears to perform the backup
>>> but still says package failed.
>>>
>>> Any ideas?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>>> news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>>> The login in question need to have a user in the database you want to backup and in that
>>> database be db_backupoperator or have granted the BACKUP DATABASE and/or BACKUP LOG
>>> privileges.
>>>
>>> I don't think that there's a privilege you can grant to a login that allow you to backup
>>> any database, less than CONTROL (same as sysadmin). At least, that is how I read below:
>>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>>
>>>
>>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>>>> Hi,
>>>>
>>>> We want to use the proxy account to take backups created from maintenance plans in
>>>> SQL2005. What permissions does the proxy account need to do this? It has permissions to
>>>> run SSIS packages and it failed saying the package could not be loaded. I then added the
>>>> proxy account to the db_dtsadmin role and now I get the package execution failed. Does it
>>>> also need the master role db_backupoperator to run?
>>>>
>>>> Thanks
>>>>
>>>> Chris
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>|||Tibor and others.
The Dec SP2 CTP version gives a lot of debug info rather than just a generic
package failure. It first complained about access to a stored procedure in
msdb and then a table in msdb. Making the proxy account a db_owner in msdb
helps (no way the right approach). Then it complained about the old backup
delete sp in master. Again making the proxy account a db_owner in master
does not solve the problem (even worse solution than doing this in msdb). It
is all documented in Connect though no response from MS yet. See the SP2 DEC
CTP Backup Error entry dated Dec 21st in Connect.
Chris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23N718tNJHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Have I found a bug in DEC SP2?
> Possibly. I know MS are working hard on Maint Plans in SP2, so if you can
> create a repro (specify the steps needed), you probably want to post this
> to http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:OiFy8tIJHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The new SP2 Dec CTP does give a lot more info when I try to run this. It
>> complains about sp_maintplan_logentry. It mentions the Execute permission
>> on this msdb stored proc was denied.
>> Have I found a bug in DEC SP2?
>> Thanks
>> Chris
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> Thanks again Tibor.
>> I had looked in SSIS at the package and it had mentioned the Reader Role
>> as db_dtsadmin, db_dtsoperator and the creator of the package. I can see
>> as long as I give the proxy account one of the 2 db_dts roles in msdb it
>> can read/execute the package. I suppose I could change this to some
>> other msdb role and make sure the proxy account is a member of that
>> role. I cannot see much about the error. It throws an Event 12291 in the
>> Event log saying the package has failed. This is running SQL2005 SP1.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>> So the backup is produced but you get package failed. I suggest you
>> post this to an SSIS group. Next step would be to investigate why the
>> SSIS package fails, looking at SSIS log etc, but since I'm no SSIS
>> person, I don't have much to offer, I'm afraid...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Tibor,
>> I first granted Backup Database and then Backup Log to the proxy
>> account and added it to the application database in the
>> db_backupoperator role. It appears to perform the backup but still
>> says package failed.
>> Any ideas?
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The login in question need to have a user in the database you want to
>> backup and in that database be db_backupoperator or have granted the
>> BACKUP DATABASE and/or BACKUP LOG privileges.
>> I don't think that there's a privilege you can grant to a login that
>> allow you to backup any database, less than CONTROL (same as
>> sysadmin). At least, that is how I read below:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> We want to use the proxy account to take backups created from
>>> maintenance plans in SQL2005. What permissions does the proxy
>>> account need to do this? It has permissions to run SSIS packages and
>>> it failed saying the package could not be loaded. I then added the
>>> proxy account to the db_dtsadmin role and now I get the package
>>> execution failed. Does it also need the master role
>>> db_backupoperator to run?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>
>>
>>
>>
>|||Finally I have some news.
MS have established that you need to be a sysadmin, if you want to perform
the Maintenance Cleanup Task. You do need to give the proxy account certain
permissions in both msdb and the application database but for now leave out
the Maintenance Cleanup Task.
My next task is to get the Cleanup Task to run after the backup but not as
part of the backup as it needs to much authority.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23QxybPOMHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Tibor and others.
> The Dec SP2 CTP version gives a lot of debug info rather than just a
> generic package failure. It first complained about access to a stored
> procedure in msdb and then a table in msdb. Making the proxy account a
> db_owner in msdb helps (no way the right approach). Then it complained
> about the old backup delete sp in master. Again making the proxy account a
> db_owner in master does not solve the problem (even worse solution than
> doing this in msdb). It is all documented in Connect though no response
> from MS yet. See the SP2 DEC CTP Backup Error entry dated Dec 21st in
> Connect.
> Chris
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23N718tNJHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Have I found a bug in DEC SP2?
>> Possibly. I know MS are working hard on Maint Plans in SP2, so if you can
>> create a repro (specify the steps needed), you probably want to post this
>> to http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:OiFy8tIJHHA.3952@.TK2MSFTNGP02.phx.gbl...
>> The new SP2 Dec CTP does give a lot more info when I try to run this. It
>> complains about sp_maintplan_logentry. It mentions the Execute
>> permission on this msdb stored proc was denied.
>> Have I found a bug in DEC SP2?
>> Thanks
>> Chris
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uVPZH$EJHHA.1816@.TK2MSFTNGP06.phx.gbl...
>> Thanks again Tibor.
>> I had looked in SSIS at the package and it had mentioned the Reader
>> Role as db_dtsadmin, db_dtsoperator and the creator of the package. I
>> can see as long as I give the proxy account one of the 2 db_dts roles
>> in msdb it can read/execute the package. I suppose I could change this
>> to some other msdb role and make sure the proxy account is a member of
>> that role. I cannot see much about the error. It throws an Event 12291
>> in the Event log saying the package has failed. This is running SQL2005
>> SP1.
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:eQXzvVBJHHA.960@.TK2MSFTNGP04.phx.gbl...
>> So the backup is produced but you get package failed. I suggest you
>> post this to an SSIS group. Next step would be to investigate why the
>> SSIS package fails, looking at SSIS log etc, but since I'm no SSIS
>> person, I don't have much to offer, I'm afraid...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ODu23y7IHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Tibor,
>> I first granted Backup Database and then Backup Log to the proxy
>> account and added it to the application database in the
>> db_backupoperator role. It appears to perform the backup but still
>> says package failed.
>> Any ideas?
>> Thanks
>> Chris
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:u6XuY86IHHA.3952@.TK2MSFTNGP02.phx.gbl...
>>> The login in question need to have a user in the database you want
>>> to backup and in that database be db_backupoperator or have granted
>>> the BACKUP DATABASE and/or BACKUP LOG privileges.
>>>
>>> I don't think that there's a privilege you can grant to a login that
>>> allow you to backup any database, less than CONTROL (same as
>>> sysadmin). At least, that is how I read below:
>>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/499e5ed6-945c-4791-ab45-68dec0b9c289.htm
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>>
>>>
>>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>>> news:%23uMeVy6IHHA.5104@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> We want to use the proxy account to take backups created from
>>> maintenance plans in SQL2005. What permissions does the proxy
>>> account need to do this? It has permissions to run SSIS packages
>>> and it failed saying the package could not be loaded. I then added
>>> the proxy account to the db_dtsadmin role and now I get the package
>>> execution failed. Does it also need the master role
>>> db_backupoperator to run?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>
>>
>>
>>
>

Backup To Network Share

I would like to backup my databases to a network share (NAS) instead of local disk using Maintenance Plans created by Enterprise Manager. I have successfully used a UNC path to target the destination network share but have not been to figure out how to submit a logon to the network share before the backup is executed.

The SQL Server instance is running in the context of the local system account.

Can I insert a step in the SQL SQL job that is created by the Maintenance Plans that changes the Windows account that the backup runs under? If yes what command syntax would I use in the inserted step or is there another way to accomplish that I'm attempting to do?

thanks
HPTo do this you need the SQL Service to run as a network user account that has pemissions over the share.

That being said it is generally reccomended that you create your backup file locally and then copy it over the network using a DOS batch file that uses copy or some such method.