Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?
How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.
|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:
> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
--
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How do you run these jobs, and what types are they of. Maint Wiz? SQL
>> Server Agent jobs, TSQL or CmdExec? Etc...
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server
Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transactio
n
> logs. 2 out of the 3 databases are being backed up and the transaction log
s
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main
Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Tibor Karaszi wrote:
>
>
> They were created using the Maintenance Wizard and scheduled to run nightly.[/vbco
l]

backups

I am unable to modify/delete a backup job for master database.
Error 14274: Cannot add, update or delete a job that originated from MSX
server.
??
Help!
Did you recently migrate the msdb database from another server? Are you the
DBA responsible for managing these?
You get that error for one of the two reasons above usually. The second one
happens when someone is using a single server to administer the jobs on
several servers.
If you look at the sysjobs table in the msdb database, you will see a column
called originating_server. That will be a different name then the instance
this msdb and master database reside on. If you update that column, you will
no longer get the error.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"docsql" wrote:

> I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> ??
> Help!
>
>
|||Hi,
You may also want to check if you encounter the following known issue:
281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming
http://support.microsoft.com/?id=281642
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: backups
| thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
| X-WBNR-Posting-Host: 65.69.68.118
| From: "=?Utf-8?B?TWVhbk9sZERCQQ==?="
<MeanOldDBA@.discussions.microsoft.com>
| References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
| Subject: RE: backups
| Date: Thu, 6 Oct 2005 19:18:02 -0700
| Lines: 33
| Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Did you recently migrate the msdb database from another server? Are you
the
| DBA responsible for managing these?
|
| You get that error for one of the two reasons above usually. The second
one
| happens when someone is using a single server to administer the jobs on
| several servers.
|
| If you look at the sysjobs table in the msdb database, you will see a
column
| called originating_server. That will be a different name then the
instance
| this msdb and master database reside on. If you update that column, you
will
| no longer get the error.
|
| --
| MeanOldDBA
| derrickleggett@.hotmail.com
| http://weblogs.sqlteam.com/derrickl
|
| When life gives you a lemon, fire the DBA.
|
|
| "docsql" wrote:
|
| > I am unable to modify/delete a backup job for master database.
| >
| > Error 14274: Cannot add, update or delete a job that originated from
MSX
| > server.
| >
| > ??
| >
| > Help!
| >
| >
| >
|
|||... and this http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:pjczSIwyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> You may also want to check if you encounter the following known issue:
> 281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
> Renaming
> http://support.microsoft.com/?id=281642
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: backups
> | thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
> | X-WBNR-Posting-Host: 65.69.68.118
> | From: "=?Utf-8?B?TWVhbk9sZERCQQ==?="
> <MeanOldDBA@.discussions.microsoft.com>
> | References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
> | Subject: RE: backups
> | Date: Thu, 6 Oct 2005 19:18:02 -0700
> | Lines: 33
> | Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Did you recently migrate the msdb database from another server? Are you
> the
> | DBA responsible for managing these?
> |
> | You get that error for one of the two reasons above usually. The second
> one
> | happens when someone is using a single server to administer the jobs on
> | several servers.
> |
> | If you look at the sysjobs table in the msdb database, you will see a
> column
> | called originating_server. That will be a different name then the
> instance
> | this msdb and master database reside on. If you update that column, you
> will
> | no longer get the error.
> |
> | --
> | MeanOldDBA
> | derrickleggett@.hotmail.com
> | http://weblogs.sqlteam.com/derrickl
> |
> | When life gives you a lemon, fire the DBA.
> |
> |
> | "docsql" wrote:
> |
> | > I am unable to modify/delete a backup job for master database.
> | >
> | > Error 14274: Cannot add, update or delete a job that originated from
> MSX
> | > server.
> | >
> | > ??
> | >
> | > Help!
> | >
> | >
> | >
> |
>
|||open sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished
|||open msdb..sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished
|||"docsql" <docsql@.noemail.nospam> wrote in message
news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> ??
> Help!
>
|||http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"docsql" <docsql@.noemail.nospam> wrote in message news:eVx37BpzFHA.908@.tk2msftngp13.phx.gbl...
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

Backups

I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?
Are you asking a question or responding to the question already asked in this thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?

Backups

I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?Are you asking a question or responding to the question already asked in thi
s thread?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx
.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?

backups

I am unable to modify/delete a backup job for master database.
Error 14274: Cannot add, update or delete a job that originated from MSX
server.
'?
Help!Did you recently migrate the msdb database from another server? Are you the
DBA responsible for managing these?
You get that error for one of the two reasons above usually. The second one
happens when someone is using a single server to administer the jobs on
several servers.
If you look at the sysjobs table in the msdb database, you will see a column
called originating_server. That will be a different name then the instance
this msdb and master database reside on. If you update that column, you wil
l
no longer get the error.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"docsql" wrote:

> I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> '?
> Help!
>
>|||Hi,
You may also want to check if you encounter the following known issue:
281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming
http://support.microsoft.com/?id=281642
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: backups
| thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
| X-WBNR-Posting-Host: 65.69.68.118
| From: "examnotes"
<MeanOldDBA@.discussions.microsoft.com>
| References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
| Subject: RE: backups
| Date: Thu, 6 Oct 2005 19:18:02 -0700
| Lines: 33
| Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Did you recently migrate the msdb database from another server? Are you
the
| DBA responsible for managing these?
|
| You get that error for one of the two reasons above usually. The second
one
| happens when someone is using a single server to administer the jobs on
| several servers.
|
| If you look at the sysjobs table in the msdb database, you will see a
column
| called originating_server. That will be a different name then the
instance
| this msdb and master database reside on. If you update that column, you
will
| no longer get the error.
|
| --
| MeanOldDBA
| derrickleggett@.hotmail.com
| http://weblogs.sqlteam.com/derrickl
|
| When life gives you a lemon, fire the DBA.
|
|
| "docsql" wrote:
|
| > I am unable to modify/delete a backup job for master database.
| >
| > Error 14274: Cannot add, update or delete a job that originated from
MSX
| > server.
| >
| > '?
| >
| > Help!
| >
| >
| >
||||... and this http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:pjczSIwyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> You may also want to check if you encounter the following known issue:
> 281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
> Renaming
> http://support.microsoft.com/?id=281642
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> --
> | Thread-Topic: backups
> | thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==
> | X-WBNR-Posting-Host: 65.69.68.118
> | From: "examnotes"
> <MeanOldDBA@.discussions.microsoft.com>
> | References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
> | Subject: RE: backups
> | Date: Thu, 6 Oct 2005 19:18:02 -0700
> | Lines: 33
> | Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Did you recently migrate the msdb database from another server? Are you
> the
> | DBA responsible for managing these?
> |
> | You get that error for one of the two reasons above usually. The second
> one
> | happens when someone is using a single server to administer the jobs on
> | several servers.
> |
> | If you look at the sysjobs table in the msdb database, you will see a
> column
> | called originating_server. That will be a different name then the
> instance
> | this msdb and master database reside on. If you update that column, you
> will
> | no longer get the error.
> |
> | --
> | MeanOldDBA
> | derrickleggett@.hotmail.com
> | http://weblogs.sqlteam.com/derrickl
> |
> | When life gives you a lemon, fire the DBA.
> |
> |
> | "docsql" wrote:
> |
> | > I am unable to modify/delete a backup job for master database.
> | >
> | > Error 14274: Cannot add, update or delete a job that originated from
> MSX
> | > server.
> | >
> | > '?
> | >
> | > Help!
> | >
> | >
> | >
> |
>|||open sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished|||open msdb..sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished|||"docsql" <docsql@.noemail.nospam> wrote in message
news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> '?
> Help!
>|||http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"docsql" <docsql@.noemail.nospam> wrote in message news:eVx37BpzFHA.908@.tk2msftngp13.phx.gbl.
.
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

backups

I am unable to modify/delete a backup job for master database.
Error 14274: Cannot add, update or delete a job that originated from MSX
server.
'?
Help!Did you recently migrate the msdb database from another server? Are you the
DBA responsible for managing these?
You get that error for one of the two reasons above usually. The second one
happens when someone is using a single server to administer the jobs on
several servers.
If you look at the sysjobs table in the msdb database, you will see a column
called originating_server. That will be a different name then the instance
this msdb and master database reside on. If you update that column, you will
no longer get the error.
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"docsql" wrote:
> I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> '?
> Help!
>
>|||Hi,
You may also want to check if you encounter the following known issue:
281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
Renaming
http://support.microsoft.com/?id=281642
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: backups
| thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==| X-WBNR-Posting-Host: 65.69.68.118
| From: "=?Utf-8?B?TWVhbk9sZERCQQ==?="
<MeanOldDBA@.discussions.microsoft.com>
| References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
| Subject: RE: backups
| Date: Thu, 6 Oct 2005 19:18:02 -0700
| Lines: 33
| Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Did you recently migrate the msdb database from another server? Are you
the
| DBA responsible for managing these?
|
| You get that error for one of the two reasons above usually. The second
one
| happens when someone is using a single server to administer the jobs on
| several servers.
|
| If you look at the sysjobs table in the msdb database, you will see a
column
| called originating_server. That will be a different name then the
instance
| this msdb and master database reside on. If you update that column, you
will
| no longer get the error.
|
| --
| MeanOldDBA
| derrickleggett@.hotmail.com
| http://weblogs.sqlteam.com/derrickl
|
| When life gives you a lemon, fire the DBA.
|
|
| "docsql" wrote:
|
| > I am unable to modify/delete a backup job for master database.
| >
| > Error 14274: Cannot add, update or delete a job that originated from
MSX
| > server.
| >
| > '?
| >
| > Help!
| >
| >
| >
||||... and this http://www.karaszi.com/SQLServer/info_change_server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:pjczSIwyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> Hi,
> You may also want to check if you encounter the following known issue:
> 281642 PRB: Error 14274 Occurs When You Update a SQL Agent Job After
> Renaming
> http://support.microsoft.com/?id=281642
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: backups
> | thread-index: AcXK5VguhZxeuRlgTjiTogE3a78P6A==> | X-WBNR-Posting-Host: 65.69.68.118
> | From: "=?Utf-8?B?TWVhbk9sZERCQQ==?="
> <MeanOldDBA@.discussions.microsoft.com>
> | References: <uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl>
> | Subject: RE: backups
> | Date: Thu, 6 Oct 2005 19:18:02 -0700
> | Lines: 33
> | Message-ID: <7818BB22-C587-47F5-B487-3FC87D0513FD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:73344
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Did you recently migrate the msdb database from another server? Are you
> the
> | DBA responsible for managing these?
> |
> | You get that error for one of the two reasons above usually. The second
> one
> | happens when someone is using a single server to administer the jobs on
> | several servers.
> |
> | If you look at the sysjobs table in the msdb database, you will see a
> column
> | called originating_server. That will be a different name then the
> instance
> | this msdb and master database reside on. If you update that column, you
> will
> | no longer get the error.
> |
> | --
> | MeanOldDBA
> | derrickleggett@.hotmail.com
> | http://weblogs.sqlteam.com/derrickl
> |
> | When life gives you a lemon, fire the DBA.
> |
> |
> | "docsql" wrote:
> |
> | > I am unable to modify/delete a backup job for master database.
> | >
> | > Error 14274: Cannot add, update or delete a job that originated from
> MSX
> | > server.
> | >
> | > '?
> | >
> | > Help!
> | >
> | >
> | >
> |
>|||open sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished|||open msdb..sysjobs table
find the job that gives the error, the originating server field MUST be
different, change it to the current server name
finished|||"docsql" <docsql@.noemail.nospam> wrote in message
news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>I am unable to modify/delete a backup job for master database.
> Error 14274: Cannot add, update or delete a job that originated from MSX
> server.
> '?
> Help!
>|||http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"docsql" <docsql@.noemail.nospam> wrote in message news:eVx37BpzFHA.908@.tk2msftngp13.phx.gbl...
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:uPrUdQsyFHA.2072@.TK2MSFTNGP14.phx.gbl...
>>I am unable to modify/delete a backup job for master database.
>> Error 14274: Cannot add, update or delete a job that originated from MSX
>> server.
>> '?
>> Help!
>

Backups

I guess I'm not understander your responce. Do I need a
specific job to "Update statistics used by the query
optimizer"?Are you asking a question or responding to the question already asked in this thread?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:2479d01c45f79$e19f6de0$a501280a@.phx.gbl...
> I guess I'm not understander your responce. Do I need a
> specific job to "Update statistics used by the query
> optimizer"?

Backup Transaction Logs Using T-SQL

I am trying to backup my Transaction Logs using T-SQL with the aid of a job. The problem I am running into is that I need it to backup every three hours and retain one days worth of transactional logs. This is the query I got so far.

BACKUP LOG CA001 TO DISK = 'S:\AZTARGP\CA001\ca001_TRN_bkuplog.trn' with retaindays = 1

Any suggestions welcomed.At my place, we have a separate cleanup job that
deletes the files using xp_cmdshell .|||Try the Database Maintenance Plan. It's a database object and one of the properties is "Remove Files Older Than ___". It creates an entry in your SQL SERVER AGENT under JOBS to run the plan as scheduled.

It also allows much more than just simple backups and it very easy to manage.

I set up a Sunday backup that keeps it's prior Sunday file but removes older ones, but that backup doesn't affect my ongoing daily backups and logfile changes (separate jobs).

Also: I notice you're referencing a "S:" drive. Does SQL backup recognize mapped drives? Maybe that's just a shortcoming of the Database Maintenance Plan Object. It would seem risky. I'd rather do it to a separate (physical) volume physically attached, then immediately copy that the mapped drive (that's what I do). That way a RAID corruption on the DB volume doesn't corrupt the backup too. Also; my backup volume is just RAID 1 (mirrored), since that's easily transfered to a different server in a disaster situation.|||The S:\ drive is actually an iscsi drive that is recognised by the server. What is funny is that the maintenance plan for the database backup works just not the transaction logs. I also have checked to make sure the database was not in simple recovery mode.

I am sort of trying to find a way to give my backup names a date and time. Then I want to only keep a days worth of transaction logs becuase we do a full backup of the database everynight.

I do have

declare @.bkupdate datetime
set @.bkupdate = getdate()

but I don't know how to include it into my sql statement. When I do I get an syntax error.

BACKUP LOG CA001 TO DISK = 'S:\aztargp\ca001\CA001_' + @.bkupdate + '_TRNLOG.trn' with retaindays = 1

I am just trying to use t-sql until I can find the problem with why my database maintenance plan is not working. Thanks for the help so far.|||OK. Here's one that works here.

BACKUP DATABASE [wfab] TO DISK = N'd:\SQL Backups\Daily\mydb_db_latest.bak' WITH INIT , NAME = N'mydbLatest', SKIP , FORMAT

This hard codes the filename. I have this running every 6 hours. Surely using a variable for the filename would be fine, although why not build the whole thing first.

So:

declare @.bkdbName varchar(50)
set @.bkdbName = 'mydb' + Convert(varchar(50),getdate(),110)

declare @.bkName varchar(255)
set @.bkName = 'd:'
set @.bkName = trim(@.bkName) + '\SQL Backups\Daily\'
set @.bkName = trim(@.bkName) + trim(@.bkdbName)

BACKUP DATABASE [wfab] TO DISK = trim(@.bkName) WITH INIT , NAME = trim(@.bkName), SKIP , FORMAT

The reason I liked it without the special naming is because then it shows up pre-registered when I want to do a restore. I do a scheduled copy of these backup files to various places before the next backup takes place. Actually; I copy it to a standby server (as with the half-hour log files), ftp it to an offsite server, and I have a scheduled CD burn (encrypted) to my desktop at 3:30 every day, that I take home with me. This in addition to replication - I think it's safe to say we'll never loose much.

I haven't tried the above - I'm curious if it would then cause all the prior backups to show on the pre-registered backup file list. That would be very nice for restores - something we do regularly to test environments.|||code works great. Had to change a couple of things. How can I get the @.bkdbName to also contain the time?|||code works great. Had to change a couple of things. How can I get the @.bkdbName to also contain the time?
Here's (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp) a link to MSDN for TSQL Ref. Make this a favorite.

Look at "Cast and convert" for conversion formats.

This should help:

select CONVERT(varchar(8),getdate(),114)
Result is: 15:57:34 Note: 114 returns 11 or 12 characters, but you probably don't need miliseconds.

Use SUBSTRING to parse out the ":" since that's probably not allowed as part of a filename.

I'm curious (and too lazy to try it). After creating a couple of these backups, does it list all these backups on the drop-down list when you go into the RESTORE dialog box? I know that works if the name is static.|||Have not tried it yet. Will when I get a change this afternoon.|||Thanks guys for the help. I also found out what was wrong with my DB maintenance plans. When you set to backup master,model,msdb and other databases in one plan it works fine for the db backups but not the translog. When i created a seperate plan for my user databases the transaction log backups completed successfully.

Backup Transaction Job with NO truncate

Is there a way to setup a SQL Agent backup job to backup the transaction log and NOT truncate it
I can't seem to find any information on this.How about BACKUP LOG YourDatabase WITH NO_TRUNCATE? Why would you want to
back it up and never truncate it?
--
Andrew J. Kelly
SQL Server MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:C1903053-1B63-433D-A9DD-AACED12D83F2@.microsoft.com...
> Is there a way to setup a SQL Agent backup job to backup the transaction
log and NOT truncate it.
> I can't seem to find any information on this.|||You can use the syntax Backup log dbname to device
with no_truncate.
Regards,
Paul Ibison

Backup Transaction Job with NO truncate

Is there a way to setup a SQL Agent backup job to backup the transaction log
and NOT truncate it.
I can't seem to find any information on this.How about BACKUP LOG YourDatabase WITH NO_TRUNCATE? Why would you want to
back it up and never truncate it?
Andrew J. Kelly
SQL Server MVP
"mannie" <anonymous@.discussions.microsoft.com> wrote in message
news:C1903053-1B63-433D-A9DD-AACED12D83F2@.microsoft.com...
> Is there a way to setup a SQL Agent backup job to backup the transaction
log and NOT truncate it.
> I can't seem to find any information on this.

Backup to UNC path

I'm having a problem with a backup job.

15 or so databases are being backed up overnight to a remote server using UNC paths. All bar one database is being copied fine. The one that isn't is the largest at 8gb. The error in the log is OS error 64 (the network name could not be found).

Now I've got the customer looking into any reasons why their network may be interrupted overnight, but while googling around I saw someone mentioned that backing up to UNC with databases over 2gb can cause problems? Is there any truth in this?

I am inclined to reccomend the user backs up the database to a local drive and then sets up a scheduled task to copy it across, but I'm interested to see if this is a known issue.

Thanks.My own Opinion (MOO)

You should backup to the local drive, then copy the dump.

It'll be faster and safer...

MOO|||Once upon a time, in a galaxy far, far away ... oops, wrong place

I had that situation once. You could download a free copy of gzip and use that to compress the backup on the local machine before copying it across the network. I tried winzip, but it became unreliable after the database grew to over 4 GB. I **never** had a problem unzipping the database using gzip, and before I left that company, it had grown to over 17 GB.

I backed up the db to a backup device, zipped the file into a new file, and then copied the zipped file off server, where it was then picked up by the tape backup daily. Triple redundacy!!|||As I thought :)

btw does anyone know if sql 2005 will support native compression of backups?

Sage databases have a *lot* of padding. I've got a 1gb database that compressed down to 25mb with winrar, would be nice to have backups compressed right off the bat!|||backup to disk
use network backup to backup the backup file
saves you money so you dont have to buy the sql plugin for your backup software. and you get permenent backup devices which make restoring a whole lot easier.

backup database db1 to db1full
backup database db1 to db1diff with differential
backup log db1 to db1log

i know where all of my fulls, diffs and logs are without searching through hundreds of files in a dir.

convenient yes
does it compress well? not really.

backup to network drive

I'm trying to create a job to backup to a network drive. The following SQL does not execute successfully:
USE master
EXEC sp_addumpdevice 'disk', 'Thurs6', '\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
BACKUP LOG Mock8 TO Thurs6 WITH INIT
However, if I change the destination file to one located on the SQL server, it executes fine. I've also tried this using a mapped drive letter, and that doesn't work either. Any thoughts?
Backups are done under the account that sql server runs under and not the
account you are logged on as. So if SQL Server is not running under a domain
account with access to that share it can not see it.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||It could be that the account used by SQL Server doesn't have permissions to
that share. Can you post the exact error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||Hi,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
USE master
go
EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
go
BACKUP LOG Mock8 TO Thurs6 WITH INIT
Thanks
Hari
MCDBA
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?