The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.
On P5 I created the Backup Device:
USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\\P3\temp\test.bak'
Then I executed the following on P5 Query Analyzer:
Backup Database myDatabase TO myDevice
But I get this error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Any idea? Thanks and regards.Originally posted by juniper
The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.
On P5 I created the Backup Device:
USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\\P3\temp\test.bak'
Then I executed the following on P5 Query Analyzer:
Backup Database myDatabase TO myDevice
But I get this error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Any idea? Thanks and regards.
yeah, go into Enterprise Manager, right click on the db you are trying to backup and all tasks - bring online|||Better yet...don't dump across a network...
It'll be much slower and be at risk for blips in the network...
Dump local then copy...|||Hi guys, thanks for your advice. Brett, I am doing it the way you suggested. However, it would mean that the computer running the database server would have to have a folder permanently shared for my program to copy the file across the network.
Any workaround so that I do not need to share folders? The user might inadvertently unshare the folder and would create problems.
Thanks and regards.|||Ensure the account used to execute this task does have required privileges to access the share specified.
If you're scheduling this task then ensure SQLAgent account used does have admin privileges or a domain account to access so.
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
backup database database remotely
i have a stored procedure that backups database on server A which i execute this SP from server B in a scheduled job. when i run this job it gives me an error:
Executed as user: domain\user_name. Could not relay results of procedure 'usp_BackupDBOnServerA' from remote server 'ServerB'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.
i searched MS KB, everywhere but could not find the solution or even explanation why this happenning.
both sql servers are 2000 and they are linked.It seems like an account permissions issue. I would look over the SP and make sure that the account which SQLServer service, SQLAgent service, and the scheduled job has access to the SP as well as the network share which the backup is being written too.
Does the whole backup fail or does it just not return what happened (the result or output)?|||actually there's no issue with account's permissions. and to asnwer your question the job itself fails.
but i just figured out how to solve the issue: i increased the Remote query timeout in the Server properties.
thanks for the help anyway.|||Great! Thanks for posting the answer, it helps with other people who might run into the same problem.
Executed as user: domain\user_name. Could not relay results of procedure 'usp_BackupDBOnServerA' from remote server 'ServerB'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.
i searched MS KB, everywhere but could not find the solution or even explanation why this happenning.
both sql servers are 2000 and they are linked.It seems like an account permissions issue. I would look over the SP and make sure that the account which SQLServer service, SQLAgent service, and the scheduled job has access to the SP as well as the network share which the backup is being written too.
Does the whole backup fail or does it just not return what happened (the result or output)?|||actually there's no issue with account's permissions. and to asnwer your question the job itself fails.
but i just figured out how to solve the issue: i increased the Remote query timeout in the Server properties.
thanks for the help anyway.|||Great! Thanks for posting the answer, it helps with other people who might run into the same problem.
Thursday, March 8, 2012
Backup continues to try to execute...
Greetings
I've inherited a SQL server recently at work, and in trying to clean up
the maintenance plan and back ups I've discovered that every night
there is a backup job for the transactions for two particular databases
that attempts to run, but is misconfigured and fails. Weird thing is
there isn't a backup job in Enterprise Manager. My maintenance plan,
the only maintenance plan, executes fine and backs up those particular
databases and their transaction logs fine. I'm just trying to find
this phantom backup job that is misconfigured and filling up my event
logs. Any help you can provide is appreciated.
moterSQL Server Profiler is your friend
<motert@.gmail.com> wrote in message
news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> Greetings
> I've inherited a SQL server recently at work, and in trying to clean up
> the maintenance plan and back ups I've discovered that every night
> there is a backup job for the transactions for two particular databases
> that attempts to run, but is misconfigured and fails. Weird thing is
> there isn't a backup job in Enterprise Manager. My maintenance plan,
> the only maintenance plan, executes fine and backs up those particular
> databases and their transaction logs fine. I'm just trying to find
> this phantom backup job that is misconfigured and filling up my event
> logs. Any help you can provide is appreciated.
> moter
>|||I looked through the Profiler, but could not find any events that
watched backups. I know the job is executing, I just can't find it in
Enterprise Manager to delete it. It doesn't display in the Backups
section and is not a part of my maintenance plan. It's a phantom
backup job from some where.
Any other ideas?
Uri Dimant wrote:
> SQL Server Profiler is your friend
>
> <motert@.gmail.com> wrote in message
> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> > Greetings
> >
> > I've inherited a SQL server recently at work, and in trying to clean up
> > the maintenance plan and back ups I've discovered that every night
> > there is a backup job for the transactions for two particular databases
> > that attempts to run, but is misconfigured and fails. Weird thing is
> > there isn't a backup job in Enterprise Manager. My maintenance plan,
> > the only maintenance plan, executes fine and backs up those particular
> > databases and their transaction logs fine. I'm just trying to find
> > this phantom backup job that is misconfigured and filling up my event
> > logs. Any help you can provide is appreciated.
> >
> > moter
> >|||The backup command can be executed by anyone. Agent isn't the only scheduler on the market. Even
Windows comes with a built-in scheduler. Use Profiler to capture the BACKUP command and see what the
application name is. Might help you to narrow things down a bit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<motert@.gmail.com> wrote in message news:1152456107.316846.79550@.75g2000cwc.googlegroups.com...
>I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
> Uri Dimant wrote:
>> SQL Server Profiler is your friend
>>
>> <motert@.gmail.com> wrote in message
>> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
>> > Greetings
>> >
>> > I've inherited a SQL server recently at work, and in trying to clean up
>> > the maintenance plan and back ups I've discovered that every night
>> > there is a backup job for the transactions for two particular databases
>> > that attempts to run, but is misconfigured and fails. Weird thing is
>> > there isn't a backup job in Enterprise Manager. My maintenance plan,
>> > the only maintenance plan, executes fine and backs up those particular
>> > databases and their transaction logs fine. I'm just trying to find
>> > this phantom backup job that is misconfigured and filling up my event
>> > logs. Any help you can provide is appreciated.
>> >
>> > moter
>> >
>|||motert@.gmail.com wrote:
> I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
The output of sp_who2 includes a column for the application name. If
this is running from an Agent job, it will indicate that in this field.
You could also KILL the process that is running the backup, and then see
which Agent job fails.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks to all who have responded so far. I went all the way through
the Profiler and couldn't find where to select BACKUP as an event to
trace. I'll admidt I've never used it before so any basics will help.
I was able to find this though:
"SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
1)."
Does that help narrow it down any?
Thanks again!
moter
Tracy McKibben wrote:
> motert@.gmail.com wrote:
> > I looked through the Profiler, but could not find any events that
> > watched backups. I know the job is executing, I just can't find it in
> > Enterprise Manager to delete it. It doesn't display in the Backups
> > section and is not a part of my maintenance plan. It's a phantom
> > backup job from some where.
> >
> > Any other ideas?
> >
> The output of sp_who2 includes a column for the application name. If
> this is running from an Agent job, it will indicate that in this field.
> You could also KILL the process that is running the backup, and then see
> which Agent job fails.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I found it. Typing it out led me there. Thanks everyone.
mot...@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
> Tracy McKibben wrote:
> > motert@.gmail.com wrote:
> > > I looked through the Profiler, but could not find any events that
> > > watched backups. I know the job is executing, I just can't find it in
> > > Enterprise Manager to delete it. It doesn't display in the Backups
> > > section and is not a part of my maintenance plan. It's a phantom
> > > backup job from some where.
> > >
> > > Any other ideas?
> > >
> >
> > The output of sp_who2 includes a column for the application name. If
> > this is running from an Agent job, it will indicate that in this field.
> >
> > You could also KILL the process that is running the backup, and then see
> > which Agent job fails.
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||motert@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
That gives you the name of the job right there in the error message.
"database backup1" is the name of the Agent job...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
I've inherited a SQL server recently at work, and in trying to clean up
the maintenance plan and back ups I've discovered that every night
there is a backup job for the transactions for two particular databases
that attempts to run, but is misconfigured and fails. Weird thing is
there isn't a backup job in Enterprise Manager. My maintenance plan,
the only maintenance plan, executes fine and backs up those particular
databases and their transaction logs fine. I'm just trying to find
this phantom backup job that is misconfigured and filling up my event
logs. Any help you can provide is appreciated.
moterSQL Server Profiler is your friend
<motert@.gmail.com> wrote in message
news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> Greetings
> I've inherited a SQL server recently at work, and in trying to clean up
> the maintenance plan and back ups I've discovered that every night
> there is a backup job for the transactions for two particular databases
> that attempts to run, but is misconfigured and fails. Weird thing is
> there isn't a backup job in Enterprise Manager. My maintenance plan,
> the only maintenance plan, executes fine and backs up those particular
> databases and their transaction logs fine. I'm just trying to find
> this phantom backup job that is misconfigured and filling up my event
> logs. Any help you can provide is appreciated.
> moter
>|||I looked through the Profiler, but could not find any events that
watched backups. I know the job is executing, I just can't find it in
Enterprise Manager to delete it. It doesn't display in the Backups
section and is not a part of my maintenance plan. It's a phantom
backup job from some where.
Any other ideas?
Uri Dimant wrote:
> SQL Server Profiler is your friend
>
> <motert@.gmail.com> wrote in message
> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> > Greetings
> >
> > I've inherited a SQL server recently at work, and in trying to clean up
> > the maintenance plan and back ups I've discovered that every night
> > there is a backup job for the transactions for two particular databases
> > that attempts to run, but is misconfigured and fails. Weird thing is
> > there isn't a backup job in Enterprise Manager. My maintenance plan,
> > the only maintenance plan, executes fine and backs up those particular
> > databases and their transaction logs fine. I'm just trying to find
> > this phantom backup job that is misconfigured and filling up my event
> > logs. Any help you can provide is appreciated.
> >
> > moter
> >|||The backup command can be executed by anyone. Agent isn't the only scheduler on the market. Even
Windows comes with a built-in scheduler. Use Profiler to capture the BACKUP command and see what the
application name is. Might help you to narrow things down a bit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<motert@.gmail.com> wrote in message news:1152456107.316846.79550@.75g2000cwc.googlegroups.com...
>I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
> Uri Dimant wrote:
>> SQL Server Profiler is your friend
>>
>> <motert@.gmail.com> wrote in message
>> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
>> > Greetings
>> >
>> > I've inherited a SQL server recently at work, and in trying to clean up
>> > the maintenance plan and back ups I've discovered that every night
>> > there is a backup job for the transactions for two particular databases
>> > that attempts to run, but is misconfigured and fails. Weird thing is
>> > there isn't a backup job in Enterprise Manager. My maintenance plan,
>> > the only maintenance plan, executes fine and backs up those particular
>> > databases and their transaction logs fine. I'm just trying to find
>> > this phantom backup job that is misconfigured and filling up my event
>> > logs. Any help you can provide is appreciated.
>> >
>> > moter
>> >
>|||motert@.gmail.com wrote:
> I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
The output of sp_who2 includes a column for the application name. If
this is running from an Agent job, it will indicate that in this field.
You could also KILL the process that is running the backup, and then see
which Agent job fails.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks to all who have responded so far. I went all the way through
the Profiler and couldn't find where to select BACKUP as an event to
trace. I'll admidt I've never used it before so any basics will help.
I was able to find this though:
"SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
1)."
Does that help narrow it down any?
Thanks again!
moter
Tracy McKibben wrote:
> motert@.gmail.com wrote:
> > I looked through the Profiler, but could not find any events that
> > watched backups. I know the job is executing, I just can't find it in
> > Enterprise Manager to delete it. It doesn't display in the Backups
> > section and is not a part of my maintenance plan. It's a phantom
> > backup job from some where.
> >
> > Any other ideas?
> >
> The output of sp_who2 includes a column for the application name. If
> this is running from an Agent job, it will indicate that in this field.
> You could also KILL the process that is running the backup, and then see
> which Agent job fails.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I found it. Typing it out led me there. Thanks everyone.
mot...@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
> Tracy McKibben wrote:
> > motert@.gmail.com wrote:
> > > I looked through the Profiler, but could not find any events that
> > > watched backups. I know the job is executing, I just can't find it in
> > > Enterprise Manager to delete it. It doesn't display in the Backups
> > > section and is not a part of my maintenance plan. It's a phantom
> > > backup job from some where.
> > >
> > > Any other ideas?
> > >
> >
> > The output of sp_who2 includes a column for the application name. If
> > this is running from an Agent job, it will indicate that in this field.
> >
> > You could also KILL the process that is running the backup, and then see
> > which Agent job fails.
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||motert@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
That gives you the name of the job right there in the error message.
"database backup1" is the name of the Agent job...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Backup continues to try to execute...
Greetings
I've inherited a SQL server recently at work, and in trying to clean up
the maintenance plan and back ups I've discovered that every night
there is a backup job for the transactions for two particular databases
that attempts to run, but is misconfigured and fails. Weird thing is
there isn't a backup job in Enterprise Manager. My maintenance plan,
the only maintenance plan, executes fine and backs up those particular
databases and their transaction logs fine. I'm just trying to find
this phantom backup job that is misconfigured and filling up my event
logs. Any help you can provide is appreciated.
moterSQL Server Profiler is your friend
<motert@.gmail.com> wrote in message
news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> Greetings
> I've inherited a SQL server recently at work, and in trying to clean up
> the maintenance plan and back ups I've discovered that every night
> there is a backup job for the transactions for two particular databases
> that attempts to run, but is misconfigured and fails. Weird thing is
> there isn't a backup job in Enterprise Manager. My maintenance plan,
> the only maintenance plan, executes fine and backs up those particular
> databases and their transaction logs fine. I'm just trying to find
> this phantom backup job that is misconfigured and filling up my event
> logs. Any help you can provide is appreciated.
> moter
>|||I looked through the Profiler, but could not find any events that
watched backups. I know the job is executing, I just can't find it in
Enterprise Manager to delete it. It doesn't display in the Backups
section and is not a part of my maintenance plan. It's a phantom
backup job from some where.
Any other ideas?
Uri Dimant wrote:[vbcol=seagreen]
> SQL Server Profiler is your friend
>
> <motert@.gmail.com> wrote in message
> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...|||The backup command can be executed by anyone. Agent isn't the only scheduler
on the market. Even
Windows comes with a built-in scheduler. Use Profiler to capture the BACKUP
command and see what the
application name is. Might help you to narrow things down a bit.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<motert@.gmail.com> wrote in message news:1152456107.316846.79550@.75g2000cwc.googlegroups.com
..
>I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
> Uri Dimant wrote:
>|||motert@.gmail.com wrote:
> I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
The output of sp_who2 includes a column for the application name. If
this is running from an Agent job, it will indicate that in this field.
You could also KILL the process that is running the backup, and then see
which Agent job fails.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks to all who have responded so far. I went all the way through
the Profiler and couldn't find where to select BACKUP as an event to
trace. I'll admidt I've never used it before so any basics will help.
I was able to find this though:
"SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
1)."
Does that help narrow it down any?
Thanks again!
moter
Tracy McKibben wrote:
> motert@.gmail.com wrote:
> The output of sp_who2 includes a column for the application name. If
> this is running from an Agent job, it will indicate that in this field.
> You could also KILL the process that is running the backup, and then see
> which Agent job fails.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||motert@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
That gives you the name of the job right there in the error message.
"database backup1" is the name of the Agent job...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I found it. Typing it out led me there. Thanks everyone.
mot...@.gmail.com wrote:[vbcol=seagreen]
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
> Tracy McKibben wrote:
I've inherited a SQL server recently at work, and in trying to clean up
the maintenance plan and back ups I've discovered that every night
there is a backup job for the transactions for two particular databases
that attempts to run, but is misconfigured and fails. Weird thing is
there isn't a backup job in Enterprise Manager. My maintenance plan,
the only maintenance plan, executes fine and backs up those particular
databases and their transaction logs fine. I'm just trying to find
this phantom backup job that is misconfigured and filling up my event
logs. Any help you can provide is appreciated.
moterSQL Server Profiler is your friend
<motert@.gmail.com> wrote in message
news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...
> Greetings
> I've inherited a SQL server recently at work, and in trying to clean up
> the maintenance plan and back ups I've discovered that every night
> there is a backup job for the transactions for two particular databases
> that attempts to run, but is misconfigured and fails. Weird thing is
> there isn't a backup job in Enterprise Manager. My maintenance plan,
> the only maintenance plan, executes fine and backs up those particular
> databases and their transaction logs fine. I'm just trying to find
> this phantom backup job that is misconfigured and filling up my event
> logs. Any help you can provide is appreciated.
> moter
>|||I looked through the Profiler, but could not find any events that
watched backups. I know the job is executing, I just can't find it in
Enterprise Manager to delete it. It doesn't display in the Backups
section and is not a part of my maintenance plan. It's a phantom
backup job from some where.
Any other ideas?
Uri Dimant wrote:[vbcol=seagreen]
> SQL Server Profiler is your friend
>
> <motert@.gmail.com> wrote in message
> news:1152453296.717787.45300@.m73g2000cwd.googlegroups.com...|||The backup command can be executed by anyone. Agent isn't the only scheduler
on the market. Even
Windows comes with a built-in scheduler. Use Profiler to capture the BACKUP
command and see what the
application name is. Might help you to narrow things down a bit.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<motert@.gmail.com> wrote in message news:1152456107.316846.79550@.75g2000cwc.googlegroups.com
..
>I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
> Uri Dimant wrote:
>|||motert@.gmail.com wrote:
> I looked through the Profiler, but could not find any events that
> watched backups. I know the job is executing, I just can't find it in
> Enterprise Manager to delete it. It doesn't display in the Backups
> section and is not a part of my maintenance plan. It's a phantom
> backup job from some where.
> Any other ideas?
>
The output of sp_who2 includes a column for the application name. If
this is running from an Agent job, it will indicate that in this field.
You could also KILL the process that is running the backup, and then see
which Agent job fails.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks to all who have responded so far. I went all the way through
the Profiler and couldn't find where to select BACKUP as an event to
trace. I'll admidt I've never used it before so any basics will help.
I was able to find this though:
"SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
1)."
Does that help narrow it down any?
Thanks again!
moter
Tracy McKibben wrote:
> motert@.gmail.com wrote:
> The output of sp_who2 includes a column for the application name. If
> this is running from an Agent job, it will indicate that in this field.
> You could also KILL the process that is running the backup, and then see
> which Agent job fails.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||motert@.gmail.com wrote:
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
That gives you the name of the job right there in the error message.
"database backup1" is the name of the Agent job...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I found it. Typing it out led me there. Thanks everyone.
mot...@.gmail.com wrote:[vbcol=seagreen]
> Thanks to all who have responded so far. I went all the way through
> the Profiler and couldn't find where to select BACKUP as an event to
> trace. I'll admidt I've never used it before so any basics will help.
> I was able to find this though:
> "SQL Server Scheduled Job 'database backup1' - Status: Failed - Invoked
> on: 2006-07-09 22:00:01 - Message: The job failed. The Job was invoked
> by Schedule 13 (Schedule 1). The last step to run was step 1 (Step
> 1)."
> Does that help narrow it down any?
> Thanks again!
> moter
>
> Tracy McKibben wrote:
Friday, February 24, 2012
Backup and Job view and execute permissions
Can you please help me to identify which roles or permissions i need to grant
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.
Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to grant
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
>required permission to perform the above operation.
|||Thanks Sue.
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
>
>
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.
Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to grant
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
>required permission to perform the above operation.
|||Thanks Sue.
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
>
>
Backup and Job view and execute permissions
Can you please help me to identify which roles or permissions i need to grant
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to grant
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
>required permission to perform the above operation.|||Thanks Sue. :)
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
> >Can you please help me to identify which roles or permissions i need to grant
> >to a user to do the following task without assigning sysadmin role?
> >
> >1. View and execute SQL Jobs
> >2. View backup device and its contents
> >
> >I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
> >required permission to perform the above operation.
>
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to grant
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
>required permission to perform the above operation.|||Thanks Sue. :)
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
> >Can you please help me to identify which roles or permissions i need to grant
> >to a user to do the following task without assigning sysadmin role?
> >
> >1. View and execute SQL Jobs
> >2. View backup device and its contents
> >
> >I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
> >required permission to perform the above operation.
>
Backup and Job view and execute permissions
Can you please help me to identify which roles or permissions i need to gran
t
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to gra
nt
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet th
e
>required permission to perform the above operation.|||Thanks Sue.
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
>
>
t
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to gra
nt
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet th
e
>required permission to perform the above operation.|||Thanks Sue.
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
>
>
Sunday, February 19, 2012
Backup /Restore options
I'm trying to decide what path to take in implementing Backup & Restore
processes in my VB6/MSDE application.
Either (a) create and execute Maintenance Plans or (b) use the BACKUP /
RESTORE / DBSHRINK commands in the program.
Can anybody explain to me the pros/cons of using one of the other?
TIA
Jim K
Hi Jim,
This depends upon the type of client in my opinion. A scheduled backup to a
file (for example) is great for small sites (followed by an operating system
backup of that file) but ONLY if they're going to check that the file
date/time keeps changing. If the SQL Agent stops (for example), they'll
blissfully keep backing up the same file and not actually have a backup
(apart from an old one). Maintenance plans have the same issues.
If that's a problem, I'd rather have an option in the program because you
are more likely to have those sorts of places carry out a "procedure" than
to look for something more subtle.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Jim K" <krusej@.megsinet.net> wrote in message
news:OcvVvds5EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I'm trying to decide what path to take in implementing Backup & Restore
> processes in my VB6/MSDE application.
> Either (a) create and execute Maintenance Plans or (b) use the BACKUP /
> RESTORE / DBSHRINK commands in the program.
> Can anybody explain to me the pros/cons of using one of the other?
> TIA
> Jim K
>
processes in my VB6/MSDE application.
Either (a) create and execute Maintenance Plans or (b) use the BACKUP /
RESTORE / DBSHRINK commands in the program.
Can anybody explain to me the pros/cons of using one of the other?
TIA
Jim K
Hi Jim,
This depends upon the type of client in my opinion. A scheduled backup to a
file (for example) is great for small sites (followed by an operating system
backup of that file) but ONLY if they're going to check that the file
date/time keeps changing. If the SQL Agent stops (for example), they'll
blissfully keep backing up the same file and not actually have a backup
(apart from an old one). Maintenance plans have the same issues.
If that's a problem, I'd rather have an option in the program because you
are more likely to have those sorts of places carry out a "procedure" than
to look for something more subtle.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Jim K" <krusej@.megsinet.net> wrote in message
news:OcvVvds5EHA.4072@.TK2MSFTNGP10.phx.gbl...
> I'm trying to decide what path to take in implementing Backup & Restore
> processes in my VB6/MSDE application.
> Either (a) create and execute Maintenance Plans or (b) use the BACKUP /
> RESTORE / DBSHRINK commands in the program.
> Can anybody explain to me the pros/cons of using one of the other?
> TIA
> Jim K
>
Subscribe to:
Posts (Atom)