Problems using SQL: backup

Showing posts with label backup. Show all posts

Friday, March 30, 2012

Problems with TLog Backup

To All:
TLog backups have been going smoothly for many months. Now, the following
messages are generated (always together) with increasing frequency.
3041 :
BACKUP failed to complete the command backup log "Apollo" to
VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize = 65536, buffercount = 1
18210 :
BackupVirtualDeviceSet::Initialize: CoCreateInstance failure on
backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No such
interface supported).
The fellow in charge of backups wants me to reboot the server daily. This
isn't the answer to this problem. Any suggestions are appreciated.
Regards,
Michael CliffordIs the backup device local ?
If not can you backup locally ?
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
news:ufZJblQiDHA.2164@.TK2MSFTNGP09.phx.gbl...
> To All:
> TLog backups have been going smoothly for many months. Now, the following
> messages are generated (always together) with increasing frequency.
> 3041 :
> BACKUP failed to complete the command backup log "Apollo" to
> VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize => 65536, buffercount = 1
> 18210 :
> BackupVirtualDeviceSet::Initialize: CoCreateInstance failure on
> backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No
such
> interface supported).
>
> The fellow in charge of backups wants me to reboot the server daily. This
> isn't the answer to this problem. Any suggestions are appreciated.
>
> Regards,
> Michael Clifford
>
>
>|||Allan,
Backups are done with an enterprise level Veritas SQL Agent on the local box
and a tape backup server remotely.
Michael
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:uEA1oASiDHA.1508@.TK2MSFTNGP10.phx.gbl...
> Is the backup device local ?
> If not can you backup locally ?
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
> news:ufZJblQiDHA.2164@.TK2MSFTNGP09.phx.gbl...
> > To All:
> >
> > TLog backups have been going smoothly for many months. Now, the
following
> > messages are generated (always together) with increasing frequency.
> >
> > 3041 :
> >
> > BACKUP failed to complete the command backup log "Apollo" to
> > VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize => > 65536, buffercount = 1
> >
> > 18210 :
> >
> > BackupVirtualDeviceSet::Initialize: CoCreateInstance failure on
> > backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No
> such
> > interface supported).
> >
> >
> >
> > The fellow in charge of backups wants me to reboot the server daily.
This
> > isn't the answer to this problem. Any suggestions are appreciated.
> >
> >
> >
> > Regards,
> >
> > Michael Clifford
> >
> >
> >
> >
> >
> >
>|||OK so these are those "Suck the guts" backups from an "Agent" ?
Can you issue a simple BACKUP DATABASE <<name>> TO DISK = '<<local disk
file>>'
Have you seen this
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q323/6/02.asp&NoWebContent=1
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
news:uzrz%23bZiDHA.1624@.tk2msftngp13.phx.gbl...
> Allan,
> Backups are done with an enterprise level Veritas SQL Agent on the local
box
> and a tape backup server remotely.
> Michael
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:uEA1oASiDHA.1508@.TK2MSFTNGP10.phx.gbl...
> > Is the backup device local ?
> > If not can you backup locally ?
> >
> > --
> > --
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> > "Michael Clifford" <mjclifford_nospam@.mindspring.com> wrote in message
> > news:ufZJblQiDHA.2164@.TK2MSFTNGP09.phx.gbl...
> > > To All:
> > >
> > > TLog backups have been going smoothly for many months. Now, the
> following
> > > messages are generated (always together) with increasing frequency.
> > >
> > > 3041 :
> > >
> > > BACKUP failed to complete the command backup log "Apollo" to
> > > VIRTUAL_DEVICE='VNBU0-2416-2316' with blocksize = 512, maxtransfersize
=> > > 65536, buffercount = 1
> > >
> > > 18210 :
> > >
> > > BackupVirtualDeviceSet::Initialize: CoCreateInstance failure
on
> > > backup device 'VNBU0-2416-2316'. Operating system error -2147467262(No
> > such
> > > interface supported).
> > >
> > >
> > >
> > > The fellow in charge of backups wants me to reboot the server daily.
> This
> > > isn't the answer to this problem. Any suggestions are appreciated.
> > >
> > >
> > >
> > > Regards,
> > >
> > > Michael Clifford
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>sql

Friday, March 23, 2012

Problems with restoring master from a backup - SQL Server 2000

Hello,
I am trying to restore the master DB and I am not getting anywhere.
I stor the server, then restart it in single user mode (sqlservr.exe -c -m
from command prompt)
When I open Enterprise Manager I find that my user DB is in single user
mode, master is not, so when I try to restore I get a message that master
must be in single user mode.
I must be missing something.
Anu help would be appreciated
RagnarIs this a default or a named instance? If it is a named instance, you need to use the I switch as
well (I think it is "I", but you better check with BIL, might be "i").
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:%23Oz%23yXzTFHA.3952@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I am trying to restore the master DB and I am not getting anywhere.
> I stor the server, then restart it in single user mode (sqlservr.exe -c -m from command prompt)
> When I open Enterprise Manager I find that my user DB is in single user mode, master is not, so
> when I try to restore I get a message that master must be in single user mode.
> I must be missing something.
> Anu help would be appreciated
> Ragnar
>|||How to start the default instance of SQL Server in single-user mode (Command
Prompt
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_4okl.asp
AMB
"Ragnar Midtskogen" wrote:
> Hello,
> I am trying to restore the master DB and I am not getting anywhere.
> I stor the server, then restart it in single user mode (sqlservr.exe -c -m
> from command prompt)
> When I open Enterprise Manager I find that my user DB is in single user
> mode, master is not, so when I try to restore I get a message that master
> must be in single user mode.
> I must be missing something.
> Anu help would be appreciated
> Ragnar
>
>|||How to start a named instance of SQL Server in single-user mode (Command
Prompt)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_5609.asp
AMB
"Alejandro Mesa" wrote:
> How to start the default instance of SQL Server in single-user mode (Command
> Prompt)
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_4okl.asp
>
> AMB
> "Ragnar Midtskogen" wrote:
> > Hello,
> >
> > I am trying to restore the master DB and I am not getting anywhere.
> > I stor the server, then restart it in single user mode (sqlservr.exe -c -m
> > from command prompt)
> > When I open Enterprise Manager I find that my user DB is in single user
> > mode, master is not, so when I try to restore I get a message that master
> > must be in single user mode.
> >
> > I must be missing something.
> >
> > Anu help would be appreciated
> >
> > Ragnar
> >
> >
> >|||Thank you Tibor,
I did not install it, but as far as I can tell it is a default instance.
For example, the name of the processes are MSSQLServer and SQLServerAgent.
Ragnar|||Thank you Alejandro,
As I wrote in my post:
> I stop the server, then restart it in single user mode (sqlservr.exe -c -m
> from command prompt)
This is what is described in the link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_4okl.asp
Ragnar|||FYI. for a named instance the syntax is:
a.. From a command prompt, enter:
sqlservr.exe -c - m -s {instancename}
Ragnar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQVhbdzTFHA.2556@.TK2MSFTNGP12.phx.gbl...
> Is this a default or a named instance? If it is a named instance, you need
> to use the I switch as well (I think it is "I", but you better check with
> BIL, might be "i").
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:%23Oz%23yXzTFHA.3952@.TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I am trying to restore the master DB and I am not getting anywhere.
>> I stor the server, then restart it in single user mode
>> (sqlservr.exe -c -m from command prompt)
>> When I open Enterprise Manager I find that my user DB is in single user
>> mode, master is not, so when I try to restore I get a message that master
>> must be in single user mode.
>> I must be missing something.
>> Anu help would be appreciated
>> Ragnar
>|||Try using Query Analyzer instead of EM to do the restore. post the RESTORE DATABASE command you
execute and the exact error message from SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:OsCB76$TFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Thank you Tibor,
> I did not install it, but as far as I can tell it is a default instance.
> For example, the name of the processes are MSSQLServer and SQLServerAgent.
> Ragnar
>|||Thanks Tibor,
The error message I get no matter which way I do the restore is that the
master DB must be in single user mode.
The problem here is that even though I follow the recommended procedure for
starting the server in single user mode just puts my user DB in single user
mode.
I did a test and confirmed that my user DB was in single user moed and it
was.
However, I found a post on a NG where someone had the same problem and he
solved it by setting a property in SQL Server to start in single user mode
and it worked for him.
Problem is I can't find the property, and this was post from a couple of
years ago so there is no way to get in touch with the guy.
I will do some more searching when I get a chance.
Ragnar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eV1vXHAUFHA.612@.TK2MSFTNGP12.phx.gbl...
> Try using Query Analyzer instead of EM to do the restore. post the RESTORE
> DATABASE command you execute and the exact error message from SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:OsCB76$TFHA.3436@.TK2MSFTNGP09.phx.gbl...
>> Thank you Tibor,
>> I did not install it, but as far as I can tell it is a default instance.
>> For example, the name of the processes are MSSQLServer and
>> SQLServerAgent.
>> Ragnar
>|||To start the SQL Server in single user mode for master or any other system
database recovery
From the command prompt
sqlservr.exe -m
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:uX0RF6aUFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Thanks Tibor,
> The error message I get no matter which way I do the restore is that the
> master DB must be in single user mode.
> The problem here is that even though I follow the recommended procedure
> for starting the server in single user mode just puts my user DB in single
> user mode.
> I did a test and confirmed that my user DB was in single user moed and it
> was.
> However, I found a post on a NG where someone had the same problem and he
> solved it by setting a property in SQL Server to start in single user mode
> and it worked for him.
> Problem is I can't find the property, and this was post from a couple of
> years ago so there is no way to get in touch with the guy.
> I will do some more searching when I get a chance.
> Ragnar
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:eV1vXHAUFHA.612@.TK2MSFTNGP12.phx.gbl...
>> Try using Query Analyzer instead of EM to do the restore. post the
>> RESTORE DATABASE command you execute and the exact error message from SQL
>> Server.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
>> news:OsCB76$TFHA.3436@.TK2MSFTNGP09.phx.gbl...
>> Thank you Tibor,
>> I did not install it, but as far as I can tell it is a default instance.
>> For example, the name of the processes are MSSQLServer and
>> SQLServerAgent.
>> Ragnar
>>
>|||Thanks Wayne,
I guess you have not read my the original post, the problem here is that the
command does not work for me, it puts my user DB in single user mode, but
not the master DB.
Ragnar|||How do you determine that the user db is in single user mode? Also, can you describe the exact steps
you take when you start SQL Server in single user mode (like "open DOS prompt, type sqlservr, ")?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:O0nt%23VnUFHA.928@.TK2MSFTNGP15.phx.gbl...
> Thanks Wayne,
> I guess you have not read my the original post, the problem here is that the command does not work
> for me, it puts my user DB in single user mode, but not the master DB.
> Ragnar
>|||First of all, I KNOW the documentation says SINGLE USER mode is sufficient;
however, there can be problems and it only works some times. But there is
an alternative.
The problem is that in order to restore a database, any database, you need
to take it offline. Unfortunately, you can't do that for the master, model,
or msdb system databases. So, an alternative is to not recover them on
startup. There are two trace flags that you can do: 3608 skips autorecovery
for all but the master database, 3607 skips recovery for all databases. As
an aside, 3609 skips the creation, and, thus, the recovery of the tempdb.
Those flags will put you in a position to detach and move or restore the
various system databases depending on which one you choose. However, there
are other caveats. What if someone else connects to these databases while
you are trying to do your work? For recovery, no one can have access to the
database you want to restore, becuase it has to be taken offline. This is
why Single User Mode is NECESSARY, but not SUFFICIENT.
There is one other trace flag you should be aware of, 4022, this bypasses
the launch of startup procedures. These too can cause havoc if you're
attempting to restore or manipulate system database. In addition, you will
want to "allow updates" through the use of sp_configure.
I know; that's a lot of work. Hence, there is another command line
parameter to make this all easier: sqlservr -f. This puts the instance
(default in this case) in MINIMAL CONFIGURATION and SINGLE USER mode. You
can only use a tool like Query Analyzer and only one connection to the
SYSTEM will be allowed as well as no recovery, no tempdb, no startup procs,
allow system updates, etc., etc., etc.
SQLSERVR -f
Try it. Also, have a look at the following:
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Starting SQL Server with Minimal Configuration
http://msdn.microsoft.com/library/en-us/adminsql/ad_1_start_1cxa.asp?frame=true
Finally, the backup you are using must be of a master database AT THE SAME
BUILD LEVEL as the system you are trying to restore it to. If not, then you
must get it there, either by patching or by reinstalling and then ONLY
patching to the level of the backup you are attempting to use.
Hope this helps.
Sincerely,
Anthony Thomas
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:%23Oz%23yXzTFHA.3952@.TK2MSFTNGP15.phx.gbl...
Hello,
I am trying to restore the master DB and I am not getting anywhere.
I stor the server, then restart it in single user mode (sqlservr.exe -c -m
from command prompt)
When I open Enterprise Manager I find that my user DB is in single user
mode, master is not, so when I try to restore I get a message that master
must be in single user mode.
I must be missing something.
Anu help would be appreciated
Ragnar

Problems with restoring master from a backup - SQL Server 2000

Problems with restoring DB

I found out that i can alter database to change db mode to SINGLE_USER but
when i execute this statemens it SQL Query Analize ALTER DATABASE abs SET
SINGLE_USER this program hangs itself.
What else can I do without droping publication and recreating it after DB is
recovered ?
Best Regards
Wojciech ZnanieckiTry,
ALTER DATABASE abs SET SINGLE_USER with ROLLBACK IMMEDIATE
AMB
"Wojtek Z" wrote:

> Hello,
> I'm trying to create backup plan for a replicated database. I can create
> backup's - its easy. ( I'm using BULK_LOGGED recovery mode). I have
> problems with the recovery process.
> When I want to use RECOVERY DATABASE abc FROM DISK = '...' i get
> "Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use."
> When I want to take offline my DB i get :
> " Server: Msg 5061, Level 16, State 1, Line 1
> ALTER DATABASE failed because a lock could not be placed on database 'ABC'
.
> Try again later."
> SP_dbremove and sp_detatch_db also failed

> I found out that i can alter database to change db mode to SINGLE_USER bu
t
> when i execute this statemens it SQL Query Analize ALTER DATABASE abs SET
> SINGLE_USER this program hangs itself.
> What else can I do without droping publication and recreating it after DB
is
> recovered ?
>
> --
> Best Regards
> Wojciech Znaniecki
>
>

Problems with restoring DB

Hello,
I'm trying to create backup plan for a replicated database. I can create
backup's - its easy. ( I'm using BULK_LOGGED recovery mode). I have
problems with the recovery process.
When I want to use RECOVERY DATABASE abc FROM DISK = '...' i get
"Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use."
When I want to take offline my DB i get :
" Server: Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'ABC'.
Try again later."
SP_dbremove and sp_detatch_db also failed :(
I found out that i can alter database to change db mode to SINGLE_USER but
when i execute this statemens it SQL Query Analize ALTER DATABASE abs SET
SINGLE_USER this program hangs itself.
What else can I do without droping publication and recreating it after DB is
recovered ?
--
Best Regards
Wojciech ZnanieckiTry,
ALTER DATABASE abs SET SINGLE_USER with ROLLBACK IMMEDIATE
AMB
"Wojtek Z" wrote:
> Hello,
> I'm trying to create backup plan for a replicated database. I can create
> backup's - its easy. ( I'm using BULK_LOGGED recovery mode). I have
> problems with the recovery process.
> When I want to use RECOVERY DATABASE abc FROM DISK = '...' i get
> "Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use."
> When I want to take offline my DB i get :
> " Server: Msg 5061, Level 16, State 1, Line 1
> ALTER DATABASE failed because a lock could not be placed on database 'ABC'.
> Try again later."
> SP_dbremove and sp_detatch_db also failed :(
> I found out that i can alter database to change db mode to SINGLE_USER but
> when i execute this statemens it SQL Query Analize ALTER DATABASE abs SET
> SINGLE_USER this program hangs itself.
> What else can I do without droping publication and recreating it after DB is
> recovered ?
>
> --
> Best Regards
> Wojciech Znaniecki
>
>sql

Problems with restoring DB

I found out that i can alter database to change db mode to SINGLE_USER but
when i execute this statemens it SQL Query Analize ALTER DATABASE abs SET
SINGLE_USER this program hangs itself.
What else can I do without droping publication and recreating it after DB is
recovered ?
Best Regards
Wojciech Znaniecki
Try,
ALTER DATABASE abs SET SINGLE_USER with ROLLBACK IMMEDIATE
AMB
"Wojtek Z" wrote:

> Hello,
> I'm trying to create backup plan for a replicated database. I can create
> backup's - its easy. ( I'm using BULK_LOGGED recovery mode). I have
> problems with the recovery process.
> When I want to use RECOVERY DATABASE abc FROM DISK = '...' i get
> "Server: Msg 3101, Level 16, State 2, Line 1
> Exclusive access could not be obtained because the database is in use."
> When I want to take offline my DB i get :
> " Server: Msg 5061, Level 16, State 1, Line 1
> ALTER DATABASE failed because a lock could not be placed on database 'ABC'.
> Try again later."
> SP_dbremove and sp_detatch_db also failed

> I found out that i can alter database to change db mode to SINGLE_USER but
> when i execute this statemens it SQL Query Analize ALTER DATABASE abs SET
> SINGLE_USER this program hangs itself.
> What else can I do without droping publication and recreating it after DB is
> recovered ?
>
> --
> Best Regards
> Wojciech Znaniecki
>
>

Wednesday, March 21, 2012

Problems with my Scheduled Backup Jobs on Windows Server 2003 SP2

Hello all,
I'll get to the problem, which I have a tough time finding out the
root cause.
1) I have SQL Server 2000 Installed, with the following patch/SP
version:
Version: 8.00.760
2) My database is running on a server with Windows Server 2003 SP2.
The Problem:
All of the automated scheduled backups used to work all perfectly
well. We have an IC backup that we've implemented and it hasn't ever
caused us issues (since it is our standard)...BUT...when our
security team decided to update the Service Pack to SP2, we noticed
that the scheduled backup job would cease to run...YET...it shows
that the backup has been taken on the Jobs Activity Monitor (Even in
the History of that job), but once we look at it, the job only fired
and completed in 1 second, when it takes on average (1-2 mins).
But here's something even more wierd...it takes a backup whenever it
feels like...notably every 2-3 days...and everything is perfect. Then
you get 3-4 days of no backups. We've been watching this odd behaivour
for the past 1 month or so.
Is there a patch I am missing for SQL Server 2000? Or is the Windows
Server 2003 SP2, causing me to not properly take my backups since it
is blocking or has stopped a particular step.
It sounds strange. Have you looked into ERROR.LOG ? Any entries?
"Mohd Al Junaibi" <mohamed.aljunaibi@.gmail.com> wrote in message
news:d9f861f4-3667-49cc-8eae-a51b75aff9db@.s19g2000prg.googlegroups.com...
> Hello all,
> I'll get to the problem, which I have a tough time finding out the
> root cause.
> 1) I have SQL Server 2000 Installed, with the following patch/SP
> version:
> Version: 8.00.760
> 2) My database is running on a server with Windows Server 2003 SP2.
> The Problem:
> All of the automated scheduled backups used to work all perfectly
> well. We have an IC backup that we've implemented and it hasn't ever
> caused us issues (since it is our standard)...BUT...when our
> security team decided to update the Service Pack to SP2, we noticed
> that the scheduled backup job would cease to run...YET...it shows
> that the backup has been taken on the Jobs Activity Monitor (Even in
> the History of that job), but once we look at it, the job only fired
> and completed in 1 second, when it takes on average (1-2 mins).
> But here's something even more wierd...it takes a backup whenever it
> feels like...notably every 2-3 days...and everything is perfect. Then
> you get 3-4 days of no backups. We've been watching this odd behaivour
> for the past 1 month or so.
> Is there a patch I am missing for SQL Server 2000? Or is the Windows
> Server 2003 SP2, causing me to not properly take my backups since it
> is blocking or has stopped a particular step.
|||I do get entries in the ERROR.LOG file.
But they are only there once the backup is successfully done. The
backups are to happen on a daily basis...but you'll notice in the
ERROR.LOG that there are gaps of 2,3 and sometimes 4 days in between.
Again, this is strange, as in the Jobs Activity Monitor, it says that
the job was successfully run, yet in reality it didn't as it fired and
completed the job within 1 second...which is big sign that OS is
stoping something. But I'm speculating.
Here's the error logs (the database I'm talking about is:
"MyDatabase") I've edited just to make it as informative as possible:
2007-10-31 11:08:13.03 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2007-10-31 11:08:13.03 server Copyright (C) 1988-2002 Microsoft
Corporation.
2007-10-31 11:08:13.03 server All rights reserved.
2007-10-31 11:08:13.03 server Server Process ID is 1892.
2007-10-31 11:08:13.03 server Logging SQL Server messages in file
'd:\MSSQL\log\ERRORLOG'.
2007-10-31 11:08:13.06 server SQL Server is starting at priority
class 'normal'(4 CPUs
detected).
2007-10-31 11:08:13.71 server SQL Server configured for thread mode
processing.
2007-10-31 11:08:13.78 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000]
Lock Owner Blocks.
2007-10-31 11:08:14.09 server Attempting to initialize Distributed
Transaction
Coordinator.
2007-10-31 11:08:17.34 spid3 Starting up database 'master'.
2007-10-31 11:08:22.40 server Using 'SSNETLIB.DLL' version
'8.0.760'.
2007-10-31 11:08:22.40 spid5 Starting up database 'model'.
2007-10-31 11:08:22.43 spid3 Server name is 'XYZ'.
2007-10-31 11:08:22.62 spid8 Starting up database 'msdb'.
2007-10-31 11:08:22.64 spid12 Starting up database 'MyDatabase'.
2007-10-31 11:08:23.25 spid5 Clearing tempdb database.
2007-10-31 11:08:26.45 server SQL server listening on 10.40.50.210:
1433.
2007-10-31 11:08:26.45 server SQL server listening on 127.0.0.1:
1433.
2007-10-31 11:08:26.51 server SQL server listening on TCP, Shared
Memory, Named Pipes.
2007-10-31 11:08:26.51 server SQL Server is ready for client
connections
2007-10-31 11:08:27.70 spid5 Starting up database 'tempdb'.
2007-10-31 11:08:28.17 spid5 Analysis of database 'tempdb' (2) is
100% complete
(approximately 0 more seconds)
2007-10-31 11:08:28.64 spid3 Recovery complete.
2007-10-31 11:08:28.64 spid3 SQL global counter collection task is
created.
2007-10-31 11:08:32.39 spid51 Using 'xpsqlbot.dll' version
'2000.80.194' to execute
extended stored procedure 'xp_qv'.
2007-11-01 00:10:01.07 spid53 Using 'xplog70.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_cmdshell'.
2007-11-01 00:10:03.07 spid53 Using 'xpstar.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_instance_regread'.
2007-11-01 08:06:08.26 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31369, first LSN:
38008:338:1, last LSN:
38008:404:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
errors and repaired 0 errors.
2007-11-08 00:10:28.80 spid55 Starting up database 'MyDatabase'.
2007-11-08 00:10:47.85 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31513, first LSN:
38022:1404:1, last LSN:
38022:1408:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-10 00:10:00.80 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:00.91 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:02.22 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1798:1, last
LSN: 155:1800:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-10 00:10:02.49 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-10 00:10:02.57 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:04.30 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 980:417:1, last
LSN: 980:420:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-10 00:10:27.83 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.86 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.93 spid55 Starting up database 'MyDatabase'.
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-19 00:10:26.45 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:29.02 spid57 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.05 spid57 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.12 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:48.69 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31881, first LSN:
38051:74:1, last LSN:
38051:78:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-21 00:10:01.23 spid56 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:01.35 spid56 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:02.71 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1920:1, last
LSN: 155:1922:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-21 00:10:02.98 spid56 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-21 00:10:03.05 spid56 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:04.96 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 981:198:1, last
LSN: 981:201:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-21 00:10:26.87 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:29.51 spid56 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.55 spid56 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.63 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:49.37 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32033, first LSN:
38073:707:1, last LSN:
38073:711:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-24 00:10:00.47 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:00.60 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:02.00 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1969:1, last
LSN: 155:1971:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-24 00:10:02.28 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-24 00:10:02.36 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:04.17 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1843, first LSN: 981:274:1, last
LSN: 981:277:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-24 00:10:25.86 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:28.50 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.53 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.61 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:48.19 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32161, first LSN:
38101:76:1, last LSN:
38101:80:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
|||I've just realised that the main issue is that in one of backup
scripts that runs (and it is automated)...we have used:
isql command to run the back, and this would at times work, and also
not work.
by using the osql command instead, everything worked out fine.
Example:
Instead of having your statement:
isql -Usa -P -SSERVER
You have:
osql -Usa -P -SSERVER
That worked for me. The statement for the backup was in "isql" and I
converted it to "osql" and the rest remained untouched, and everything
started working again.
sql

Problems with my Scheduled Backup Jobs on Windows Server 2003 SP2

Hello all,
I'll get to the problem, which I have a tough time finding out the
root cause.
1) I have SQL Server 2000 Installed, with the following patch/SP
version:
Version: 8.00.760
2) My database is running on a server with Windows Server 2003 SP2.
The Problem:
All of the automated scheduled backups used to work all perfectly
well. We have an IC backup that we've implemented and it hasn't ever
caused us issues (since it is our standard)...BUT...when our
security team decided to update the Service Pack to SP2, we noticed
that the scheduled backup job would cease to run...YET...it shows
that the backup has been taken on the Jobs Activity Monitor (Even in
the History of that job), but once we look at it, the job only fired
and completed in 1 second, when it takes on average (1-2 mins).
But here's something even more wierd...it takes a backup whenever it
feels like...notably every 2-3 days...and everything is perfect. Then
you get 3-4 days of no backups. We've been watching this odd behaivour
for the past 1 month or so.
Is there a patch I am missing for SQL Server 2000? Or is the Windows
Server 2003 SP2, causing me to not properly take my backups since it
is blocking or has stopped a particular step.It sounds strange. Have you looked into ERROR.LOG ? Any entries?
"Mohd Al Junaibi" <mohamed.aljunaibi@.gmail.com> wrote in message
news:d9f861f4-3667-49cc-8eae-a51b75aff9db@.s19g2000prg.googlegroups.com...
> Hello all,
> I'll get to the problem, which I have a tough time finding out the
> root cause.
> 1) I have SQL Server 2000 Installed, with the following patch/SP
> version:
> Version: 8.00.760
> 2) My database is running on a server with Windows Server 2003 SP2.
> The Problem:
> All of the automated scheduled backups used to work all perfectly
> well. We have an IC backup that we've implemented and it hasn't ever
> caused us issues (since it is our standard)...BUT...when our
> security team decided to update the Service Pack to SP2, we noticed
> that the scheduled backup job would cease to run...YET...it shows
> that the backup has been taken on the Jobs Activity Monitor (Even in
> the History of that job), but once we look at it, the job only fired
> and completed in 1 second, when it takes on average (1-2 mins).
> But here's something even more wierd...it takes a backup whenever it
> feels like...notably every 2-3 days...and everything is perfect. Then
> you get 3-4 days of no backups. We've been watching this odd behaivour
> for the past 1 month or so.
> Is there a patch I am missing for SQL Server 2000? Or is the Windows
> Server 2003 SP2, causing me to not properly take my backups since it
> is blocking or has stopped a particular step.|||I do get entries in the ERROR.LOG file.
But they are only there once the backup is successfully done. The
backups are to happen on a daily basis...but you'll notice in the
ERROR.LOG that there are gaps of 2,3 and sometimes 4 days in between.
Again, this is strange, as in the Jobs Activity Monitor, it says that
the job was successfully run, yet in reality it didn't as it fired and
completed the job within 1 second...which is big sign that OS is
stoping something. But I'm speculating.
Here's the error logs (the database I'm talking about is:
"MyDatabase") I've edited just to make it as informative as possible:
2007-10-31 11:08:13.03 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2007-10-31 11:08:13.03 server Copyright (C) 1988-2002 Microsoft
Corporation.
2007-10-31 11:08:13.03 server All rights reserved.
2007-10-31 11:08:13.03 server Server Process ID is 1892.
2007-10-31 11:08:13.03 server Logging SQL Server messages in file
'd:\MSSQL\log\ERRORLOG'.
2007-10-31 11:08:13.06 server SQL Server is starting at priority
class 'normal'(4 CPUs
detected).
2007-10-31 11:08:13.71 server SQL Server configured for thread mode
processing.
2007-10-31 11:08:13.78 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000]
Lock Owner Blocks.
2007-10-31 11:08:14.09 server Attempting to initialize Distributed
Transaction
Coordinator.
2007-10-31 11:08:17.34 spid3 Starting up database 'master'.
2007-10-31 11:08:22.40 server Using 'SSNETLIB.DLL' version
'8.0.760'.
2007-10-31 11:08:22.40 spid5 Starting up database 'model'.
2007-10-31 11:08:22.43 spid3 Server name is 'XYZ'.
2007-10-31 11:08:22.62 spid8 Starting up database 'msdb'.
2007-10-31 11:08:22.64 spid12 Starting up database 'MyDatabase'.
2007-10-31 11:08:23.25 spid5 Clearing tempdb database.
2007-10-31 11:08:26.45 server SQL server listening on 10.40.50.210:
1433.
2007-10-31 11:08:26.45 server SQL server listening on 127.0.0.1:
1433.
2007-10-31 11:08:26.51 server SQL server listening on TCP, Shared
Memory, Named Pipes.
2007-10-31 11:08:26.51 server SQL Server is ready for client
connections
2007-10-31 11:08:27.70 spid5 Starting up database 'tempdb'.
2007-10-31 11:08:28.17 spid5 Analysis of database 'tempdb' (2) is
100% complete
(approximately 0 more seconds)
2007-10-31 11:08:28.64 spid3 Recovery complete.
2007-10-31 11:08:28.64 spid3 SQL global counter collection task is
created.
2007-10-31 11:08:32.39 spid51 Using 'xpsqlbot.dll' version
'2000.80.194' to execute
extended stored procedure 'xp_qv'.
2007-11-01 00:10:01.07 spid53 Using 'xplog70.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_cmdshell'.
2007-11-01 00:10:03.07 spid53 Using 'xpstar.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_instance_regread'.
2007-11-01 08:06:08.26 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31369, first LSN:
38008:338:1, last LSN:
38008:404:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
errors and repaired 0 errors.
2007-11-08 00:10:28.80 spid55 Starting up database 'MyDatabase'.
2007-11-08 00:10:47.85 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31513, first LSN:
38022:1404:1, last LSN:
38022:1408:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-10 00:10:00.80 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:00.91 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:02.22 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1798:1, last
LSN: 155:1800:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-10 00:10:02.49 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-10 00:10:02.57 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:04.30 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 980:417:1, last
LSN: 980:420:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-10 00:10:27.83 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.86 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.93 spid55 Starting up database 'MyDatabase'.
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-19 00:10:26.45 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:29.02 spid57 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.05 spid57 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.12 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:48.69 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31881, first LSN:
38051:74:1, last LSN:
38051:78:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-21 00:10:01.23 spid56 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:01.35 spid56 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:02.71 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1920:1, last
LSN: 155:1922:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-21 00:10:02.98 spid56 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-21 00:10:03.05 spid56 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:04.96 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 981:198:1, last
LSN: 981:201:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-21 00:10:26.87 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:29.51 spid56 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.55 spid56 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.63 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:49.37 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32033, first LSN:
38073:707:1, last LSN:
38073:711:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-24 00:10:00.47 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:00.60 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:02.00 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1969:1, last
LSN: 155:1971:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-24 00:10:02.28 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-24 00:10:02.36 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:04.17 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1843, first LSN: 981:274:1, last
LSN: 981:277:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-24 00:10:25.86 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:28.50 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.53 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.61 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:48.19 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32161, first LSN:
38101:76:1, last LSN:
38101:80:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).|||I've just realised that the main issue is that in one of backup
scripts that runs (and it is automated)...we have used:
isql command to run the back, and this would at times work, and also
not work.
by using the osql command instead, everything worked out fine.
Example:
Instead of having your statement:
isql -Usa -P -SSERVER
You have:
osql -Usa -P -SSERVER
That worked for me. The statement for the backup was in "isql" and I
converted it to "osql" and the rest remained untouched, and everything
started working again.

Problems with my Scheduled Backup Jobs on Windows Server 2003 SP2

Hello all,
I'll get to the problem, which I have a tough time finding out the
root cause.
1) I have SQL Server 2000 Installed, with the following patch/SP
version:
Version: 8.00.760
2) My database is running on a server with Windows Server 2003 SP2.
The Problem:
All of the automated scheduled backups used to work all perfectly
well. We have an IC backup that we've implemented and it hasn't ever
caused us issues (since it is our standard)...BUT...when our
security team decided to update the Service Pack to SP2, we noticed
that the scheduled backup job would cease to run...YET...it shows
that the backup has been taken on the Jobs Activity Monitor (Even in
the History of that job), but once we look at it, the job only fired
and completed in 1 second, when it takes on average (1-2 mins).
But here's something even more wierd...it takes a backup whenever it
feels like...notably every 2-3 days...and everything is perfect. Then
you get 3-4 days of no backups. We've been watching this odd behaivour
for the past 1 month or so.
Is there a patch I am missing for SQL Server 2000? Or is the Windows
Server 2003 SP2, causing me to not properly take my backups since it
is blocking or has stopped a particular step.It sounds strange. Have you looked into ERROR.LOG ? Any entries?
"Mohd Al Junaibi" <mohamed.aljunaibi@.gmail.com> wrote in message
news:d9f861f4-3667-49cc-8eae-a51b75aff9db@.s19g2000prg.googlegroups.com...
> Hello all,
> I'll get to the problem, which I have a tough time finding out the
> root cause.
> 1) I have SQL Server 2000 Installed, with the following patch/SP
> version:
> Version: 8.00.760
> 2) My database is running on a server with Windows Server 2003 SP2.
> The Problem:
> All of the automated scheduled backups used to work all perfectly
> well. We have an IC backup that we've implemented and it hasn't ever
> caused us issues (since it is our standard)...BUT...when our
> security team decided to update the Service Pack to SP2, we noticed
> that the scheduled backup job would cease to run...YET...it shows
> that the backup has been taken on the Jobs Activity Monitor (Even in
> the History of that job), but once we look at it, the job only fired
> and completed in 1 second, when it takes on average (1-2 mins).
> But here's something even more wierd...it takes a backup whenever it
> feels like...notably every 2-3 days...and everything is perfect. Then
> you get 3-4 days of no backups. We've been watching this odd behaivour
> for the past 1 month or so.
> Is there a patch I am missing for SQL Server 2000? Or is the Windows
> Server 2003 SP2, causing me to not properly take my backups since it
> is blocking or has stopped a particular step.|||I do get entries in the ERROR.LOG file.
But they are only there once the backup is successfully done. The
backups are to happen on a daily basis...but you'll notice in the
ERROR.LOG that there are gaps of 2,3 and sometimes 4 days in between.
Again, this is strange, as in the Jobs Activity Monitor, it says that
the job was successfully run, yet in reality it didn't as it fired and
completed the job within 1 second...which is big sign that OS is
stoping something. But I'm speculating.
Here's the error logs (the database I'm talking about is:
"MyDatabase") I've edited just to make it as informative as possible:
2007-10-31 11:08:13.03 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2007-10-31 11:08:13.03 server Copyright (C) 1988-2002 Microsoft
Corporation.
2007-10-31 11:08:13.03 server All rights reserved.
2007-10-31 11:08:13.03 server Server Process ID is 1892.
2007-10-31 11:08:13.03 server Logging SQL Server messages in file
'd:\MSSQL\log\ERRORLOG'.
2007-10-31 11:08:13.06 server SQL Server is starting at priority
class 'normal'(4 CPUs
detected).
2007-10-31 11:08:13.71 server SQL Server configured for thread mode
processing.
2007-10-31 11:08:13.78 server Using dynamic lock allocation. [2500]
Lock Blocks, [5000]
Lock Owner Blocks.
2007-10-31 11:08:14.09 server Attempting to initialize Distributed
Transaction
Coordinator.
2007-10-31 11:08:17.34 spid3 Starting up database 'master'.
2007-10-31 11:08:22.40 server Using 'SSNETLIB.DLL' version
'8.0.760'.
2007-10-31 11:08:22.40 spid5 Starting up database 'model'.
2007-10-31 11:08:22.43 spid3 Server name is 'XYZ'.
2007-10-31 11:08:22.62 spid8 Starting up database 'msdb'.
2007-10-31 11:08:22.64 spid12 Starting up database 'MyDatabase'.
2007-10-31 11:08:23.25 spid5 Clearing tempdb database.
2007-10-31 11:08:26.45 server SQL server listening on 10.40.50.210:
1433.
2007-10-31 11:08:26.45 server SQL server listening on 127.0.0.1:
1433.
2007-10-31 11:08:26.51 server SQL server listening on TCP, Shared
Memory, Named Pipes.
2007-10-31 11:08:26.51 server SQL Server is ready for client
connections
2007-10-31 11:08:27.70 spid5 Starting up database 'tempdb'.
2007-10-31 11:08:28.17 spid5 Analysis of database 'tempdb' (2) is
100% complete
(approximately 0 more seconds)
2007-10-31 11:08:28.64 spid3 Recovery complete.
2007-10-31 11:08:28.64 spid3 SQL global counter collection task is
created.
2007-10-31 11:08:32.39 spid51 Using 'xpsqlbot.dll' version
'2000.80.194' to execute
extended stored procedure 'xp_qv'.
2007-11-01 00:10:01.07 spid53 Using 'xplog70.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_cmdshell'.
2007-11-01 00:10:03.07 spid53 Using 'xpstar.dll' version
'2000.80.760' to execute
extended stored procedure 'xp_instance_regread'.
2007-11-01 08:06:08.26 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31369, first LSN:
38008:338:1, last LSN:
38008:404:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
errors and repaired 0 errors.
2007-11-08 00:10:28.80 spid55 Starting up database 'MyDatabase'.
2007-11-08 00:10:47.85 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31513, first LSN:
38022:1404:1, last LSN:
38022:1408:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-10 00:10:00.80 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:00.91 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:02.22 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1798:1, last
LSN: 155:1800:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-10 00:10:02.49 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-10 00:10:02.57 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-10 00:10:04.30 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 980:417:1, last
LSN: 980:420:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-10 00:10:27.83 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.86 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-10 00:10:27.93 spid55 Starting up database 'MyDatabase'.
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-19 00:10:26.45 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:29.02 spid57 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.05 spid57 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-19 00:10:29.12 spid57 Starting up database 'MyDatabase'.
2007-11-19 00:10:48.69 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 31881, first LSN:
38051:74:1, last LSN:
38051:78:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-21 00:10:01.23 spid56 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:01.35 spid56 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:02.71 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1920:1, last
LSN: 155:1922:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-21 00:10:02.98 spid56 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-21 00:10:03.05 spid56 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-21 00:10:04.96 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1835, first LSN: 981:198:1, last
LSN: 981:201:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-21 00:10:26.87 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:29.51 spid56 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.55 spid56 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-21 00:10:29.63 spid56 Starting up database 'MyDatabase'.
2007-11-21 00:10:49.37 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32033, first LSN:
38073:707:1, last LSN:
38073:711:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).
2007-11-24 00:10:00.47 spid55 DBCC CHECKDB (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:00.60 spid55 DBCC CHECKALLOC (master) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:02.00 backup Database backed up: Database: master,
creation date(time):
2007/10/31(11:08:17), pages dumped: 1867, first LSN: 155:1969:1, last
LSN: 155:1971:1,
number of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'master_Backup'}).
2007-11-24 00:10:02.28 spid55 DBCC CHECKDB (msdb) executed by
sqladm found 0 errors and
repaired 0 errors.
2007-11-24 00:10:02.36 spid55 DBCC CHECKALLOC (msdb) executed by
sqladm found 0 errors
and repaired 0 errors.
2007-11-24 00:10:04.17 backup Database backed up: Database: msdb,
creation date(time):
2000/08/06(01:40:56), pages dumped: 1843, first LSN: 981:274:1, last
LSN: 981:277:1, number
of dump devices: 1, device information: (FILE=1, TYPE=DISK:
{'msdb_Backup'}).
2007-11-24 00:10:25.86 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:28.50 spid55 DBCC CHECKDB (MyDatabase) executed by
sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.53 spid55 DBCC CHECKALLOC (MyDatabase) executed
by sqladm found 0
errors and repaired 0 errors.
2007-11-24 00:10:28.61 spid55 Starting up database 'MyDatabase'.
2007-11-24 00:10:48.19 backup Database backed up: Database:
MyDatabase, creation
date(time): 2006/08/16(23:08:27), pages dumped: 32161, first LSN:
38101:76:1, last LSN:
38101:80:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'MyDatabase_Backup'}).|||I've just realised that the main issue is that in one of backup
scripts that runs (and it is automated)...we have used:
isql command to run the back, and this would at times work, and also
not work.
by using the osql command instead, everything worked out fine.
Example:
Instead of having your statement:
isql -Usa -P -SSERVER
You have:
osql -Usa -P -SSERVER
That worked for me. The statement for the backup was in "isql" and I
converted it to "osql" and the rest remained untouched, and everything
started working again.

Friday, March 9, 2012

problems with diff backup

hello.
my server sql2000 has this backup schema: monday-full and other days
diff backup.
But 2weeks back diff backups become too large -cca 1.3 GB. previous
size of diff backups was about 300mb. every diff backup in week backup
schema is incerasing from 1.3GB to 1,4GB. largest table in database is
about 500MB large. so I can't identify why about 1.3 GB is backuped
every day. Can anybody help me ?
How Diff backup stores changes ? by tables or by rows ? Thank for
answer.
thansk .
Perhaps your indexes are fragmented. Try running DBCC INDEXDEFRAG or DBCC
DBREINDEX. Then see if the problem persists.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"oto pakozdy" <pakozdy@.pobox.sk> wrote in message
news:fa2dfe40.0409210421.7ea75192@.posting.google.c om...
hello.
my server sql2000 has this backup schema: monday-full and other days
diff backup.
But 2weeks back diff backups become too large -cca 1.3 GB. previous
size of diff backups was about 300mb. every diff backup in week backup
schema is incerasing from 1.3GB to 1,4GB. largest table in database is
about 500MB large. so I can't identify why about 1.3 GB is backuped
every day. Can anybody help me ?
How Diff backup stores changes ? by tables or by rows ? Thank for
answer.
thansk .

problems with diff backup

hello.
my server sql2000 has this backup schema: monday-full and other days
diff backup.
But 2weeks back diff backups become too large -cca 1.3 GB. previous
size of diff backups was about 300mb. every diff backup in week backup
schema is incerasing from 1.3GB to 1,4GB. largest table in database is
about 500MB large. so I can't identify why about 1.3 GB is backuped
every day. Can anybody help me ?
How Diff backup stores changes ? by tables or by rows ? Thank for
answer.
thansk .Perhaps your indexes are fragmented. Try running DBCC INDEXDEFRAG or DBCC
DBREINDEX. Then see if the problem persists.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"oto pakozdy" <pakozdy@.pobox.sk> wrote in message
news:fa2dfe40.0409210421.7ea75192@.posting.google.com...
hello.
my server sql2000 has this backup schema: monday-full and other days
diff backup.
But 2weeks back diff backups become too large -cca 1.3 GB. previous
size of diff backups was about 300mb. every diff backup in week backup
schema is incerasing from 1.3GB to 1,4GB. largest table in database is
about 500MB large. so I can't identify why about 1.3 GB is backuped
every day. Can anybody help me ?
How Diff backup stores changes ? by tables or by rows ? Thank for
answer.
thansk .

Saturday, February 25, 2012

Problems with backup

Hello;
I've problems with the differents data bases backups with SQL SERVER 2000.
If I launch the backup manualy of the differents data bases I have no
problem; but when I schedule the backup it fails show me in the event viewer
application this message:
Origen: SQLSERVERAGENT
Categoria: Job Engine
ID: 208
Descripción:
SQL Server Scheduled Job 'Copia de seguridad master'
(0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
2005-09-20 13:14:35 - Message: El trabajó falló. No se puede determinar s
i
el
propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
acceso al servidor (motivo: No se puede conectar al servidor, compruebe los
registros de errores de SQL Server y del Agente SQL Server).
I'm spanish, and I have the spanish edtion, I will try to translate the
message.
Source: SQLSERVERAGENT
Category: Job Engine
ID: 208
Description:
SQL Server Scheduled Job 'Copia de seguridad master'
(0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
property (Domain\User) of de job "Copia de seguridad master" have access to
de server (reasolt: It can't conect to the server, check the error log in SQ
L
Server and the SQL Server Agent)
This error ocours when I backup all the data bases, not only with the master
ThanksP.Burguete wrote:
> Hello;
> I've problems with the differents data bases backups with SQL SERVER 2000.
> If I launch the backup manualy of the differents data bases I have no
> problem; but when I schedule the backup it fails show me in the event view
er
> application this message:
> Origen: SQLSERVERAGENT
> Categoria: Job Engine
> ID: 208
> Descripción:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: El trabajó falló. No se puede determinar
si
> el
> propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
> acceso al servidor (motivo: No se puede conectar al servidor, compruebe lo
s
> registros de errores de SQL Server y del Agente SQL Server).
> I'm spanish, and I have the spanish edtion, I will try to translate the
> message.
> Source: SQLSERVERAGENT
> Category: Job Engine
> ID: 208
> Description:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
> property (Domain\User) of de job "Copia de seguridad master" have access t
o
> de server (reasolt: It can't conect to the server, check the error log in
SQL
> Server and the SQL Server Agent)
> This error ocours when I backup all the data bases, not only with the mast
er
> Thanks
>
Which account is used to run the SQL Server Agent service? This should
be a domain account that has access to the folders/servers where the
backup files is placed.
Regards
Steen|||Hi,
Change the SQL Server Agent service startup account to an account which has
got previlages.
For testing purpose you can first change it to Local system account and
execute the scheduled task
and once u find every thing goes fine then change to a domain OS user which
has got prev. to backup
Thanks
Hari
SQL Server MVP
"P.Burguete" <PBurguete@.discussions.microsoft.com> wrote in message
news:FE7BC385-B6ED-4A63-980C-3500FB304739@.microsoft.com...
> Hello;
> I've problems with the differents data bases backups with SQL SERVER 2000.
> If I launch the backup manualy of the differents data bases I have no
> problem; but when I schedule the backup it fails show me in the event
> viewer
> application this message:
> Origen: SQLSERVERAGENT
> Categoria: Job Engine
> ID: 208
> Descripcin:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: El trabaj fall. No se puede determinar
> si
> el
> propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
> acceso al servidor (motivo: No se puede conectar al servidor, compruebe
> los
> registros de errores de SQL Server y del Agente SQL Server).
> I'm spanish, and I have the spanish edtion, I will try to translate the
> message.
> Source: SQLSERVERAGENT
> Category: Job Engine
> ID: 208
> Description:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
> property (Domain\User) of de job "Copia de seguridad master" have access
> to
> de server (reasolt: It can't conect to the server, check the error log in
> SQL
> Server and the SQL Server Agent)
> This error ocours when I backup all the data bases, not only with the
> master
> Thanks
>|||Try changing the job owner to sa. I think that this happens when Agent servi
ce account cannot access
enough info in domain to validate the Windows owner of the job. Setting the
owner to a SQL Server
account should take care of that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"P.Burguete" <PBurguete@.discussions.microsoft.com> wrote in message
news:FE7BC385-B6ED-4A63-980C-3500FB304739@.microsoft.com...
> Hello;
> I've problems with the differents data bases backups with SQL SERVER 2000.
> If I launch the backup manualy of the differents data bases I have no
> problem; but when I schedule the backup it fails show me in the event view
er
> application this message:
> Origen: SQLSERVERAGENT
> Categoria: Job Engine
> ID: 208
> Descripción:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: El trabajó falló. No se puede determinar
si
> el
> propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
> acceso al servidor (motivo: No se puede conectar al servidor, compruebe lo
s
> registros de errores de SQL Server y del Agente SQL Server).
> I'm spanish, and I have the spanish edtion, I will try to translate the
> message.
> Source: SQLSERVERAGENT
> Category: Job Engine
> ID: 208
> Description:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
> property (Domain\User) of de job "Copia de seguridad master" have access t
o
> de server (reasolt: It can't conect to the server, check the error log in
SQL
> Server and the SQL Server Agent)
> This error ocours when I backup all the data bases, not only with the mast
er
> Thanks
>

Problems with backup

Hello;
I've problems with the differents data bases backups with SQL SERVER 2000.
If I launch the backup manualy of the differents data bases I have no
problem; but when I schedule the backup it fails show me in the event viewer
application this message:
Origen: SQLSERVERAGENT
Categoria: Job Engine
ID: 208
DescripciÃ³n:
SQL Server Scheduled Job 'Copia de seguridad master'
(0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
2005-09-20 13:14:35 - Message: El trabajÃ³ fallÃ³. No se puede determinar si
el
propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
acceso al servidor (motivo: No se puede conectar al servidor, compruebe los
registros de errores de SQL Server y del Agente SQL Server).
I'm spanish, and I have the spanish edtion, I will try to translate the
message.
Source: SQLSERVERAGENT
Category: Job Engine
ID: 208
Description:
SQL Server Scheduled Job 'Copia de seguridad master'
(0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
property (Domain\User) of de job "Copia de seguridad master" have access to
de server (reasolt: It can't conect to the server, check the error log in SQL
Server and the SQL Server Agent)
This error ocours when I backup all the data bases, not only with the master
ThanksP.Burguete wrote:
> Hello;
> I've problems with the differents data bases backups with SQL SERVER 2000.
> If I launch the backup manualy of the differents data bases I have no
> problem; but when I schedule the backup it fails show me in the event viewer
> application this message:
> Origen: SQLSERVERAGENT
> Categoria: Job Engine
> ID: 208
> DescripciÃ³n:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: El trabajÃ³ fallÃ³. No se puede determinar si
> el
> propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
> acceso al servidor (motivo: No se puede conectar al servidor, compruebe los
> registros de errores de SQL Server y del Agente SQL Server).
> I'm spanish, and I have the spanish edtion, I will try to translate the
> message.
> Source: SQLSERVERAGENT
> Category: Job Engine
> ID: 208
> Description:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
> property (Domain\User) of de job "Copia de seguridad master" have access to
> de server (reasolt: It can't conect to the server, check the error log in SQL
> Server and the SQL Server Agent)
> This error ocours when I backup all the data bases, not only with the master
> Thanks
>
Which account is used to run the SQL Server Agent service? This should
be a domain account that has access to the folders/servers where the
backup files is placed.
Regards
Steen|||Hi,
Change the SQL Server Agent service startup account to an account which has
got previlages.
For testing purpose you can first change it to Local system account and
execute the scheduled task
and once u find every thing goes fine then change to a domain OS user which
has got prev. to backup
Thanks
Hari
SQL Server MVP
"P.Burguete" <PBurguete@.discussions.microsoft.com> wrote in message
news:FE7BC385-B6ED-4A63-980C-3500FB304739@.microsoft.com...
> Hello;
> I've problems with the differents data bases backups with SQL SERVER 2000.
> If I launch the backup manualy of the differents data bases I have no
> problem; but when I schedule the backup it fails show me in the event
> viewer
> application this message:
> Origen: SQLSERVERAGENT
> Categoria: Job Engine
> ID: 208
> Descripción:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: El trabajó falló. No se puede determinar
> si
> el
> propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
> acceso al servidor (motivo: No se puede conectar al servidor, compruebe
> los
> registros de errores de SQL Server y del Agente SQL Server).
> I'm spanish, and I have the spanish edtion, I will try to translate the
> message.
> Source: SQLSERVERAGENT
> Category: Job Engine
> ID: 208
> Description:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
> property (Domain\User) of de job "Copia de seguridad master" have access
> to
> de server (reasolt: It can't conect to the server, check the error log in
> SQL
> Server and the SQL Server Agent)
> This error ocours when I backup all the data bases, not only with the
> master
> Thanks
>|||Try changing the job owner to sa. I think that this happens when Agent service account cannot access
enough info in domain to validate the Windows owner of the job. Setting the owner to a SQL Server
account should take care of that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"P.Burguete" <PBurguete@.discussions.microsoft.com> wrote in message
news:FE7BC385-B6ED-4A63-980C-3500FB304739@.microsoft.com...
> Hello;
> I've problems with the differents data bases backups with SQL SERVER 2000.
> If I launch the backup manualy of the differents data bases I have no
> problem; but when I schedule the backup it fails show me in the event viewer
> application this message:
> Origen: SQLSERVERAGENT
> Categoria: Job Engine
> ID: 208
> DescripciÃ³n:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: El trabajÃ³ fallÃ³. No se puede determinar si
> el
> propietario (Dominio\Usuario) del trabajo Copia de seguridad master tiene
> acceso al servidor (motivo: No se puede conectar al servidor, compruebe los
> registros de errores de SQL Server y del Agente SQL Server).
> I'm spanish, and I have the spanish edtion, I will try to translate the
> message.
> Source: SQLSERVERAGENT
> Category: Job Engine
> ID: 208
> Description:
> SQL Server Scheduled Job 'Copia de seguridad master'
> (0xE038C2FDE1799C45ABB23A45F46B4BF4) - Status: Error - Invoked on:
> 2005-09-20 13:14:35 - Message: The job fails. It can't determinate if the
> property (Domain\User) of de job "Copia de seguridad master" have access to
> de server (reasolt: It can't conect to the server, check the error log in SQL
> Server and the SQL Server Agent)
> This error ocours when I backup all the data bases, not only with the master
> Thanks
>

Friday, March 30, 2012

Problems with TLog Backup

Friday, March 23, 2012

Problems with restoring master from a backup - SQL Server 2000

Problems with restoring master from a backup - SQL Server 2000

Problems with restoring master from a backup - SQL Server 2000

Problems with restoring DB

Problems with restoring DB

Problems with restoring DB

Wednesday, March 21, 2012

Problems with my Scheduled Backup Jobs on Windows Server 2003 SP2

Problems with my Scheduled Backup Jobs on Windows Server 2003 SP2

Problems with my Scheduled Backup Jobs on Windows Server 2003 SP2

Friday, March 9, 2012

problems with diff backup

problems with diff backup

Saturday, February 25, 2012

Problems with backup

Problems with backup

Problems using SQL

Blog Archive

About Me