Bad code clogs replication

I am using Transaction replication on sql2005 SP2.
When deploying a new stored procedure that is supposed to be tested, I
got an error stateing that the number of columns in the select did not
match the number in the insert. I checked the SP and it was fine.
1) Was this really relating to the code, or was there an error in
replication?
2) How do you clear 'bad' transactions from replication?
Thanks,
Peter Cwik
Use the skiperror parameter on the distribution agent to skip these
errors for transactional replication.
Can you provide me with the code for the proc in question. I have not
encountered such an error yet in SQL Server.
I normally deploy changes to procs using sp_addscriptexec.
On Jan 8, 2:15 pm, PeterCwik <pjc...@.gmail.com> wrote:
> I am using Transaction replication on sql2005 SP2.
> When deploying a new stored procedure that is supposed to be tested, I
> got an error stateing that the number of columns in the select did not
> match the number in the insert. I checked the SP and it was fine.
> 1) Was this really relating to the code, or was there an error in
> replication?
> 2) How do you clear 'bad' transactions from replication?
> Thanks,
> Peter Cwik

Backward Coompatibility

We have two different machines in different locations running SQL2000. One was upgraded to SQL2005. The problem is we can't upgrade the other at the moment cause it is running SBS2003 (requires alot more planning), But we would like to get information from the computer running SQL2005. Is there a patch that can be obtained so that we can open the database created using SQL2005 in SQL2000? We desperately need the database and upgrading to SQL2005 with SBS2003 is not straight forward.
Database files or backups created in SQL Server 2005 cannot be opened using SQL Server 2000. YOu sure can connect to the SQL Server 2005 server and get data but you are not able to use the physical files for restore or attaching.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||If you have the Sql2000 backup in tape \ disk before upgrading. Use the backup and restore the db in Sql2000, Use Import\Export to move all the data's from Sql2005 to Sql2000 for that db. Since you don't have any option for using Sql2005 physical or backup files in sql2000.

backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
--
Paul G
Software engineer.Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
--
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > Hi,
> > I was just wondering if you build a database in sql2005 and then save the
> > file(backup dbase) would it work to perform a restore from this file with
> > sql2000?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||oops, someone asked the same question just a few posts earlier!
--
Paul G
Software engineer.
"Paul" wrote:
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
> > No.
> >
> > Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> > will restore to 2000 and 2005, but not 2008 (forthcoming).
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > > Hi,
> > > I was just wondering if you build a database in sql2005 and then save the
> > > file(backup dbase) would it work to perform a restore from this file with
> > > sql2000?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >

backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
Paul G
Software engineer.
Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
Paul G
Software engineer.
"Geoff N. Hiten" wrote:

> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
>
|||oops, someone asked the same question just a few posts earlier!
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:

Backups of Logs - Growing way, way out of control & Need Assistanc

Hello All
I should start by saying, I am not a SQL guru at all!
We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
NetBackup 5.1 MP5 using an online SQL Agent.
I will start with the good news, in that all FULL Backups work fine! I have
discovered that the Transaction logs are NOT included in this FULL Backup.
In NetBackup there is an option to "Backup and Truncate the logs" - so I
attempted to do this, and it claims it worked fine (the Application log also
shows event id 18265 and the description gives an indication "log was backed
up Database ect and ending in "No user action is required".
Great! but here is the problem. The .ldf files are growing WAY out of
control. For example, they can grow to over 40GB in a week !
I know there is a query or a SHRINK command that can be used to help, but I
am being told that NetBackup should be "truncating" the log down. I guess
this means shrinking.
Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
2005 Administrator has to manually shrink the logs or run a query to do this?
Or could something be setup wrong in SQL2005.
Any help is warmly appreciated.
Thank you
SimonConsider a log file a bucket. As modifications are performed, the bucket is filled. It is only
emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
by SQL Server if it becomes full and modifications are performed (autogrow).
So, one reason for large log files is that you never did backup log. You now did it, and that log
backup was probably pretty big and you should now have a lot of empty space in the log file. This
can be a valid situation for shrinking the log file. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> Hello All
> I should start by saying, I am not a SQL guru at all!
> We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> NetBackup 5.1 MP5 using an online SQL Agent.
> I will start with the good news, in that all FULL Backups work fine! I have
> discovered that the Transaction logs are NOT included in this FULL Backup.
> In NetBackup there is an option to "Backup and Truncate the logs" - so I
> attempted to do this, and it claims it worked fine (the Application log also
> shows event id 18265 and the description gives an indication "log was backed
> up Database ect and ending in "No user action is required".
> Great! but here is the problem. The .ldf files are growing WAY out of
> control. For example, they can grow to over 40GB in a week !
> I know there is a query or a SHRINK command that can be used to help, but I
> am being told that NetBackup should be "truncating" the log down. I guess
> this means shrinking.
> Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> 2005 Administrator has to manually shrink the logs or run a query to do this?
> Or could something be setup wrong in SQL2005.
> Any help is warmly appreciated.
> Thank you
> Simon|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||Tibor thanks
Is there a way of telling what free space is in the log file. I sort of
understand the "bucket" route now :-)
I am guessing that if the log file size was 2GB in size, it does NOT mean
that SQL would use that - so in other words, the bucket may only contain 1GB
of data. Whats the best way of finding out?
Will check the link out as well. I apprecaite that a 3rd party tool is doing
the backup, but it sounds like I may be on the right track - the only concern
I had is the log file does not shrink. but if the query I showed below is
run, then the file is reduced down in size.
Simon
"Tibor Karaszi" wrote:
> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in size
> by SQL Server if it becomes full and modifications are performed (autogrow).
> So, one reason for large log files is that you never did backup log. You now did it, and that log
> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> can be a valid situation for shrinking the log file. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> > Hello All
> > I should start by saying, I am not a SQL guru at all!
> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> > NetBackup 5.1 MP5 using an online SQL Agent.
> >
> > I will start with the good news, in that all FULL Backups work fine! I have
> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >
> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> > attempted to do this, and it claims it worked fine (the Application log also
> > shows event id 18265 and the description gives an indication "log was backed
> > up Database ect and ending in "No user action is required".
> >
> > Great! but here is the problem. The .ldf files are growing WAY out of
> > control. For example, they can grow to over 40GB in a week !
> >
> > I know there is a query or a SHRINK command that can be used to help, but I
> > am being told that NetBackup should be "truncating" the log down. I guess
> > this means shrinking.
> >
> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >
> > Or could something be setup wrong in SQL2005.
> >
> > Any help is warmly appreciated.
> > Thank you
> > Simon
>|||> Is there a way of telling what free space is in the log file.
Sure.:
DBCC SQLPERF(LOGSPACE)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
Correct thinking. Use above command.
So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
shrinking the file size. Here's an elaboration on the "bucket" analogy:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> Tibor thanks
> Is there a way of telling what free space is in the log file. I sort of
> understand the "bucket" route now :-)
> I am guessing that if the log file size was 2GB in size, it does NOT mean
> that SQL would use that - so in other words, the bucket may only contain 1GB
> of data. Whats the best way of finding out?
> Will check the link out as well. I apprecaite that a 3rd party tool is doing
> the backup, but it sounds like I may be on the right track - the only concern
> I had is the log file does not shrink. but if the query I showed below is
> run, then the file is reduced down in size.
> Simon
> "Tibor Karaszi" wrote:
>> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
>> size
>> by SQL Server if it becomes full and modifications are performed (autogrow).
>> So, one reason for large log files is that you never did backup log. You now did it, and that log
>> backup was probably pretty big and you should now have a lot of empty space in the log file. This
>> can be a valid situation for shrinking the log file. See
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
>> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> > Hello All
>> > I should start by saying, I am not a SQL guru at all!
>> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >
>> > I will start with the good news, in that all FULL Backups work fine! I have
>> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >
>> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> > attempted to do this, and it claims it worked fine (the Application log also
>> > shows event id 18265 and the description gives an indication "log was backed
>> > up Database ect and ending in "No user action is required".
>> >
>> > Great! but here is the problem. The .ldf files are growing WAY out of
>> > control. For example, they can grow to over 40GB in a week !
>> >
>> > I know there is a query or a SHRINK command that can be used to help, but I
>> > am being told that NetBackup should be "truncating" the log down. I guess
>> > this means shrinking.
>> >
>> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >
>> > Or could something be setup wrong in SQL2005.
>> >
>> > Any help is warmly appreciated.
>> > Thank you
>> > Simon
>>|||Thank you! I think I understand a bit better now!
Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
another recommendation?
Thanks
"Tibor Karaszi" wrote:
> > Is there a way of telling what free space is in the log file.
> Sure.:
> DBCC SQLPERF(LOGSPACE)
>
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> Correct thinking. Use above command.
> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
> shrinking the file size. Here's an elaboration on the "bucket" analogy:
> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
> > Tibor thanks
> > Is there a way of telling what free space is in the log file. I sort of
> > understand the "bucket" route now :-)
> >
> > I am guessing that if the log file size was 2GB in size, it does NOT mean
> > that SQL would use that - so in other words, the bucket may only contain 1GB
> > of data. Whats the best way of finding out?
> >
> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
> > the backup, but it sounds like I may be on the right track - the only concern
> > I had is the log file does not shrink. but if the query I showed below is
> > run, then the file is reduced down in size.
> >
> > Simon
> >
> > "Tibor Karaszi" wrote:
> >
> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow in
> >> size
> >> by SQL Server if it becomes full and modifications are performed (autogrow).
> >>
> >> So, one reason for large log files is that you never did backup log. You now did it, and that log
> >> backup was probably pretty big and you should now have a lot of empty space in the log file. This
> >> can be a valid situation for shrinking the log file. See
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> >>
> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like BACKUP
> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that app.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
> >> > Hello All
> >> > I should start by saying, I am not a SQL guru at all!
> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
> >> > NetBackup 5.1 MP5 using an online SQL Agent.
> >> >
> >> > I will start with the good news, in that all FULL Backups work fine! I have
> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
> >> >
> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
> >> > attempted to do this, and it claims it worked fine (the Application log also
> >> > shows event id 18265 and the description gives an indication "log was backed
> >> > up Database ect and ending in "No user action is required".
> >> >
> >> > Great! but here is the problem. The .ldf files are growing WAY out of
> >> > control. For example, they can grow to over 40GB in a week !
> >> >
> >> > I know there is a query or a SHRINK command that can be used to help, but I
> >> > am being told that NetBackup should be "truncating" the log down. I guess
> >> > this means shrinking.
> >> >
> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
> >> >
> >> > Or could something be setup wrong in SQL2005.
> >> >
> >> > Any help is warmly appreciated.
> >> > Thank you
> >> > Simon
> >>
> >>
>|||There are so many books out there. First think about what area you want (admin, architecture, some
specific component, programming etc), then browse at various book sites. I've always liked the
"Inside SQL Server" series from MS Press.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:26E40685-B062-4B78-BAB5-FCCCCCB5D5E8@.microsoft.com...
> Thank you! I think I understand a bit better now!
> Any recommendations on a 2005 SQL book? Microsoft one perhaps or do you have
> another recommendation?
> Thanks
> "Tibor Karaszi" wrote:
>> > Is there a way of telling what free space is in the log file.
>> Sure.:
>> DBCC SQLPERF(LOGSPACE)
>>
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> Correct thinking. Use above command.
>> So, when MS is using the term "truncating", I prefer to say "emptying". This is not the same and
>> shrinking the file size. Here's an elaboration on the "bucket" analogy:
>> http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> news:300F61B2-2E71-4252-AFEA-DAE45567F14F@.microsoft.com...
>> > Tibor thanks
>> > Is there a way of telling what free space is in the log file. I sort of
>> > understand the "bucket" route now :-)
>> >
>> > I am guessing that if the log file size was 2GB in size, it does NOT mean
>> > that SQL would use that - so in other words, the bucket may only contain 1GB
>> > of data. Whats the best way of finding out?
>> >
>> > Will check the link out as well. I apprecaite that a 3rd party tool is doing
>> > the backup, but it sounds like I may be on the right track - the only concern
>> > I had is the log file does not shrink. but if the query I showed below is
>> > run, then the file is reduced down in size.
>> >
>> > Simon
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Consider a log file a bucket. As modifications are performed, the bucket is filled. It is only
>> >> emptied when you BACKUP LOG, not for BACKUP DATABASE. Unless you have the database in simple
>> >> recovery mode, when you will get an error message if you do BACKUP LOG. This bucket can grow
>> >> in
>> >> size
>> >> by SQL Server if it becomes full and modifications are performed (autogrow).
>> >>
>> >> So, one reason for large log files is that you never did backup log. You now did it, and that
>> >> log
>> >> backup was probably pretty big and you should now have a lot of empty space in the log file.
>> >> This
>> >> can be a valid situation for shrinking the log file. See
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> >>
>> >> But, since you use some 3:rd party tool to do the backup, we don't know what command (like
>> >> BACKUP
>> >> LOG) was submitted. I'd run a Profiler trace to see what backup command is submitted by that
>> >> app.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Simon" <Simon@.discussions.microsoft.com> wrote in message
>> >> news:CEEAEBF3-D30B-476B-B248-D0A93510139C@.microsoft.com...
>> >> > Hello All
>> >> > I should start by saying, I am not a SQL guru at all!
>> >> > We have many, many SQL2005 SP1 on Win2k3 SP1 Servers, being backed up by
>> >> > NetBackup 5.1 MP5 using an online SQL Agent.
>> >> >
>> >> > I will start with the good news, in that all FULL Backups work fine! I have
>> >> > discovered that the Transaction logs are NOT included in this FULL Backup.
>> >> >
>> >> > In NetBackup there is an option to "Backup and Truncate the logs" - so I
>> >> > attempted to do this, and it claims it worked fine (the Application log also
>> >> > shows event id 18265 and the description gives an indication "log was backed
>> >> > up Database ect and ending in "No user action is required".
>> >> >
>> >> > Great! but here is the problem. The .ldf files are growing WAY out of
>> >> > control. For example, they can grow to over 40GB in a week !
>> >> >
>> >> > I know there is a query or a SHRINK command that can be used to help, but I
>> >> > am being told that NetBackup should be "truncating" the log down. I guess
>> >> > this means shrinking.
>> >> >
>> >> > Could anyone please tell me if I am going nuts !!! Is it a case that the SQL
>> >> > 2005 Administrator has to manually shrink the logs or run a query to do this?
>> >> >
>> >> > Or could something be setup wrong in SQL2005.
>> >> >
>> >> > Any help is warmly appreciated.
>> >> > Thank you
>> >> > Simon
>> >>
>> >>

Backups and log file growth issues

I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:

BACKUP DATABASE [DSS] TO DISK =

N'G:\MSSQL\Backup\DSSbackup.BAK'

WITH INIT , NOUNLOAD , NAME = N'DSS backup',

SKIP , STATS = 10, NOFORMAT

Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?

Thanks!

Additional information: I have used the scripts below to backup my database and logfile including using the INIT to truncate the log file. My database file is 9.2 GB and my log file is 9.9 GB. After running the scripts, a transaction log backup file is created (512 KB), however my ldf log file remains 9.9 GB. Why are the transactions not being written to the database file and log file entries being released?

/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO

/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO

Thanks!

|||

I have few observation

(a) in this script what you are doing is you are overwriting the earlier backup with the current one... which is not at all recommended. if the backup file got corrupted means... you will endup without any backup.. this is a serious issue. You should not use INIT in this case i believe.

(b) to transaction log size under control , you should increase the frequency of the TL backup. If currently you are configured TL backup daily then make it hourly with out INIT option. If you overwrite the backup of TL then there is no point in taking the TL Backup

(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit

(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script

EG.

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

Read more about TL architecture and Backup method /Recovery model in BOL

Read these article also :-

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu

|||

Thank you for your response.

(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.

(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.

(d) As this database is new, I will try your script commands to reduce the log file this one time.

This would fall into the class of a one-off increase. I just created this database and imported several SQL2000 tables. I then re-wrote the SQL2000 stored procedures which truncate and reload the tables, so I feel this caused the log file increase. I will try the DBCC shrink this time, however, I do not plan to use it regularily.

Thanks again for your response!

|||

I performed a full database backup and a transaction log backup. I ran the following query:

DBCC SHRINKFILE (DSS_log)

..and received the following message:

Cannot shrink log file 2 (DSS_log) because all logical log files are in use.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?

Backups and log file growth issues

I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:

BACKUP DATABASE [DSS] TO DISK =

N'G:\MSSQL\Backup\DSSbackup.BAK'

WITH INIT , NOUNLOAD , NAME = N'DSS backup',

SKIP , STATS = 10, NOFORMAT

Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?

Thanks!

Additional information: I have used the scripts below to backup my database and logfile including using the INIT to truncate the log file. My database file is 9.2 GB and my log file is 9.9 GB. After running the scripts, a transaction log backup file is created (512 KB), however my ldf log file remains 9.9 GB. Why are the transactions not being written to the database file and log file entries being released?

/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO

/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO

Thanks!

|||

I have few observation

(a) in this script what you are doing is you are overwriting the earlier backup with the current one... which is not at all recommended. if the backup file got corrupted means... you will endup without any backup.. this is a serious issue. You should not use INIT in this case i believe.

(b) to transaction log size under control , you should increase the frequency of the TL backup. If currently you are configured TL backup daily then make it hourly with out INIT option. If you overwrite the backup of TL then there is no point in taking the TL Backup

(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit

(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script

EG.

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

Read more about TL architecture and Backup method /Recovery model in BOL

Read these article also :-

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu

|||

Thank you for your response.

(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.

(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.

(d) As this database is new, I will try your script commands to reduce the log file this one time.

This would fall into the class of a one-off increase. I just created this database and imported several SQL2000 tables. I then re-wrote the SQL2000 stored procedures which truncate and reload the tables, so I feel this caused the log file increase. I will try the DBCC shrink this time, however, I do not plan to use it regularily.

Thanks again for your response!

|||

I performed a full database backup and a transaction log backup. I ran the following query:

DBCC SHRINKFILE (DSS_log)

..and received the following message:

Cannot shrink log file 2 (DSS_log) because all logical log files are in use.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?

Backups and Backup Devices in SQL2005

Hi,
Credentials are needed to access resources outside of SQL. Is a backup
and/or backup device regarded as a resource outside of SQL? Do I need to
create a credential to use a backup and/or backup device?
Thanks
Chris
Essentially yes. If you are going to schedule the backups you will need to
use a proxy account. The proxy will require a credential to access the file
system.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O7uEn1IGGHA.608@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Credentials are needed to access resources outside of SQL. Is a backup
> and/or backup device regarded as a resource outside of SQL? Do I need to
> create a credential to use a backup and/or backup device?
> Thanks
> Chris
>

Backups and Backup Devices in SQL2005

Hi,
Credentials are needed to access resources outside of SQL. Is a backup
and/or backup device regarded as a resource outside of SQL? Do I need to
create a credential to use a backup and/or backup device?
Thanks
ChrisEssentially yes. If you are going to schedule the backups you will need to
use a proxy account. The proxy will require a credential to access the file
system.
--
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O7uEn1IGGHA.608@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Credentials are needed to access resources outside of SQL. Is a backup
> and/or backup device regarded as a resource outside of SQL? Do I need to
> create a credential to use a backup and/or backup device?
> Thanks
> Chris
>

Backups and Backup Devices in SQL2005

Hi,
Credentials are needed to access resources outside of SQL. Is a backup
and/or backup device regarded as a resource outside of SQL? Do I need to
create a credential to use a backup and/or backup device?
Thanks
ChrisEssentially yes. If you are going to schedule the backups you will need to
use a proxy account. The proxy will require a credential to access the file
system.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O7uEn1IGGHA.608@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Credentials are needed to access resources outside of SQL. Is a backup
> and/or backup device regarded as a resource outside of SQL? Do I need to
> create a credential to use a backup and/or backup device?
> Thanks
> Chris
>

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
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...
>
|||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...
>
|||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...
>
|||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...
> 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...
>
|||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...
> 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...
>
|||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...
> 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...
>
|||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...
>

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
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...
>
|||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...
>
|||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...
>
|||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...
> 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...
>
|||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...
> 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...
>
|||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...
> 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...
>
|||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...
>

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
>>>
>>>
>>
>>
>>
>>
>