Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK =
'\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:

> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK =
> '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>|||Hi Mike,
How to check SQL Server Services are running under a Domain Account and
the user has correct permissions to the share. ' Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||1. On the SQL Server, go to Control Panel/Administrative Tools/Services
and make sure the MSSQLServer service is running under an account
"Domain\User" instead of Local Service.
2. Go to the share on the network drive and look at Sharing and Security to
make sure that same user has read/write permissions on the share. You can
probably have your network group do this for you.
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:#OWSbgn7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi Mike,
>
> How to check SQL Server Services are running under a Domain Account and
> the user has correct permissions to the share. ' Please advise
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi Mike,
I checked that MSSQLServer service is running under Local Administrator
Account, and I also change the job owner to Local Administrator Account,
but it still occurs following error:
The job failed. The Job was invoked by User LocalMachine\Administrator.
The last step to run was step 1 (MyTesting Backup).
If the Backup Network Drive of PC just only running on Workgroup. How to
using T-SQL Backup Network Drive ?
Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:eJsI9wu7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi Mike,
> I checked that MSSQLServer service is running under Local Administrator
> Account, and I also change the job owner to Local Administrator Account,
> but it still occurs following error:
> The job failed. The Job was invoked by User LocalMachine\Administrator.
Right. It can NOT run under a Local Administrator account.
It needs to be a domain account.

> The last step to run was step 1 (MyTesting Backup).
> If the Backup Network Drive of PC just only running on Workgroup. How to
> using T-SQL Backup Network Drive ?
You can TRY to give it a username and password that the computer containing
the network drive also has, but I'm not sure it would would work.

> Please advise.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
When I change the owner to "Domain Administrator" to execute the T-SQL
to backup database to the network drive.
It display the following error:
The job failed. Unable to determine if the owner (Domain\Administrator)
of job Test Backup has server access (reason: Could not obtain
information about Windows NT group/user 'Domain\Administrator'.
[SQLSTATE 42000] (Error 8198)).
But the Shared Network Drive PC haven't connect Domain, runing on
workgroup only. I can't directly to access that PC. Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Try to change the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:ORQOrBy7EHA.3696@.TK2MSFTNGP10.phx.g
bl...
> Hi,
> When I change the owner to "Domain Administrator" to execute the T-SQL
> to backup database to the network drive.
> It display the following error:
> The job failed. Unable to determine if the owner (Domain\Administrator)
> of job Test Backup has server access (reason: Could not obtain
> information about Windows NT group/user 'Domain\Administrator'.
> [SQLSTATE 42000] (Error 8198)).
> But the Shared Network Drive PC haven't connect Domain, runing on
> workgroup only. I can't directly to access that PC. Please advise.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi.. I try to using sa, but it is same result.
Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Did you get the exact same error message after changing job owner to "sa". T
hat would be strange, as
the error messages you posted referred to "Domain\Administrator"...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:uEl5LwC8EHA.3236@.TK2MSFTNGP15.phx.g
bl...
>
> Hi.. I try to using sa, but it is same result.
> Please advise
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK =
'\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.
Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:

> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK =
> '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>
|||Hi Mike,
How to check SQL Server Services are running under a Domain Account and
the user has correct permissions to the share. ? Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||1. On the SQL Server, go to Control Panel/Administrative Tools/Services
and make sure the MSSQLServer service is running under an account
"Domain\User" instead of Local Service.
2. Go to the share on the network drive and look at Sharing and Security to
make sure that same user has read/write permissions on the share. You can
probably have your network group do this for you.
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:#OWSbgn7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi Mike,
>
> How to check SQL Server Services are running under a Domain Account and
> the user has correct permissions to the share. ? Please advise
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Mike,
I checked that MSSQLServer service is running under Local Administrator
Account, and I also change the job owner to Local Administrator Account,
but it still occurs following error:
The job failed. The Job was invoked by User LocalMachine\Administrator.
The last step to run was step 1 (MyTesting Backup).
If the Backup Network Drive of PC just only running on Workgroup. How to
using T-SQL Backup Network Drive ?
Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:eJsI9wu7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi Mike,
> I checked that MSSQLServer service is running under Local Administrator
> Account, and I also change the job owner to Local Administrator Account,
> but it still occurs following error:
> The job failed. The Job was invoked by User LocalMachine\Administrator.
Right. It can NOT run under a Local Administrator account.
It needs to be a domain account.

> The last step to run was step 1 (MyTesting Backup).
> If the Backup Network Drive of PC just only running on Workgroup. How to
> using T-SQL Backup Network Drive ?
You can TRY to give it a username and password that the computer containing
the network drive also has, but I'm not sure it would would work.

> Please advise.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
When I change the owner to "Domain Administrator" to execute the T-SQL
to backup database to the network drive.
It display the following error:
The job failed. Unable to determine if the owner (Domain\Administrator)
of job Test Backup has server access (reason: Could not obtain
information about Windows NT group/user 'Domain\Administrator'.
[SQLSTATE 42000] (Error 8198)).
But the Shared Network Drive PC haven't connect Domain, runing on
workgroup only. I can't directly to access that PC. Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Try to change the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:ORQOrBy7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I change the owner to "Domain Administrator" to execute the T-SQL
> to backup database to the network drive.
> It display the following error:
> The job failed. Unable to determine if the owner (Domain\Administrator)
> of job Test Backup has server access (reason: Could not obtain
> information about Windows NT group/user 'Domain\Administrator'.
> [SQLSTATE 42000] (Error 8198)).
> But the Shared Network Drive PC haven't connect Domain, runing on
> workgroup only. I can't directly to access that PC. Please advise.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi.. I try to using sa, but it is same result.
Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Did you get the exact same error message after changing job owner to "sa". That would be strange, as
the error messages you posted referred to "Domain\Administrator"...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:uEl5LwC8EHA.3236@.TK2MSFTNGP15.phx.gbl...
>
> Hi.. I try to using sa, but it is same result.
> Please advise
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK = '\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:
> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK => '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>sql

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.
You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegr oups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously. This means that you
cannot reliably "wait" until end of execution and then report back the error. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

backup SQL server databases

Hi,
At the moment I use a SQL job to make dumps of SQL server databases to
the file system. A few hours later I backup these files to tape.
Now I want to connect both processes. In the backup program I can
start .bat files and catch the error level of the batch file. But is
it possible to start a SQL server job from command line and to catch
the outcome of the SQL backup process (so that the backup to tape only
starts when the backup to disk succeeds)?
Many thanks in advance,
Stijn.You can start a job using sp_start_job. But jobs are executed asynchronously
. This means that you
cannot reliably "wait" until end of execution and then report back the error
. So, I suggest you grab
whatever TSQL you have in your job, put it in a script file and execute that
using SQLCMD.EXE (or
OSQL.EXE depending on version of SQL Server).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<stijn.calders@.gmail.com> wrote in message
news:1183967597.475367.257660@.o61g2000hsh.googlegroups.com...
> Hi,
> At the moment I use a SQL job to make dumps of SQL server databases to
> the file system. A few hours later I backup these files to tape.
> Now I want to connect both processes. In the backup program I can
> start .bat files and catch the error level of the batch file. But is
> it possible to start a SQL server job from command line and to catch
> the outcome of the SQL backup process (so that the backup to tape only
> starts when the backup to disk succeeds)?
> Many thanks in advance,
> Stijn.
>

Backup SQL Server 2005

Hi,

I created a job to backup my database. Sometimes I get the following error:
The job failed. Unable to determine if the owner (DOMAIN\Administrator) of job Backup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\Administrator'<c/> error code 0x54b. [SQLSTATE 42000] (Error 15404))

I already deleted the job and made it again. Same problem. I don't get that error every day. For example: I got it 4 times last month.

Does anybody know what I can do to resolve the error?This appears to be a problem with AD. I would guess the job is not able to authenticate itself, or query information from AD. That said, it REALLY a bad idea to run jobs as Domain Admin. You should use service accounts.

Backup Script Completing But Not Working

I have a SQL Server Agent job that executes the script listed below (using a step of type "Analysis Services Command") to do a backup of an SSAS database on a nightly basis. The job takes a while (say 15 mins or so) and appears to be busily doing something (meaning the CPU's on the server have activity). However, after the script completes, there is no backup file anywhere to be found. Anyone have any idea what might be happening?

BTW, if I execute this command manually as an XMLA command while in a query window in SSMS, it seems to complete successfully (producing the .abf file) every time...

Dave Fackler

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>DatabaseXYZ</DatabaseID>
</Object>
<File>DatabaseXYZ.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

There is a problem with SQL Agent not reporting propertly case of failed XMLA command.

It should have been fixed in SP2. Try installing CTP of SP2 and see if you are geting failure of SQL Agent Job.

Another suggestion. Make sure your SQL Agent job has server name defined correctly.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I'll take a look at SP2. However, even if that resolves the issue of SQL Agent not reporting when an XMLA command fails, it doesn't really help with WHY the XMLA command would be failing in the first place. The server name is definitely defined correctly and the server seems to be doing something while the command executes. But no .abf file is generated. Is there some way I can troubleshoot this issue to determine why the XMLA command might be failing?

Dave F.

|||

Once you get a fix in SQL Agent you'll be able to get a useful error message telling you the nature of the problem. And that should help troubleshooting SQL Agent job.

For now you can try installing CTP of SP2.

Another idea for you is to use ascmd utility that allows you to execute XMLA commands. It has great functionality allows for having parametric XMLA command, where you can simply plug name of your DB and that would take care backing up your database. It also allows logging output of XMLA command's execution. Once you get it working, you can use SQL Agent to schedule backup using ascmd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am running SQL 2005 with SP2 and have the same issue.

When I use a SQL Agent job with a Sql Server Analysis Services Command it does not report any error or failure in that command.

An example command is:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>PGPDailySales</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

If the cube definition or data has errors so this command fails, the Agent Job step still reports success.

How can this issue be reported to Microsoft so it may be fixed in the next version?

Backup Script Completing But Not Working

I have a SQL Server Agent job that executes the script listed below (using a step of type "Analysis Services Command") to do a backup of an SSAS database on a nightly basis. The job takes a while (say 15 mins or so) and appears to be busily doing something (meaning the CPU's on the server have activity). However, after the script completes, there is no backup file anywhere to be found. Anyone have any idea what might be happening?

BTW, if I execute this command manually as an XMLA command while in a query window in SSMS, it seems to complete successfully (producing the .abf file) every time...

Dave Fackler

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>DatabaseXYZ</DatabaseID>
</Object>
<File>DatabaseXYZ.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

There is a problem with SQL Agent not reporting propertly case of failed XMLA command.

It should have been fixed in SP2. Try installing CTP of SP2 and see if you are geting failure of SQL Agent Job.

Another suggestion. Make sure your SQL Agent job has server name defined correctly.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

I'll take a look at SP2. However, even if that resolves the issue of SQL Agent not reporting when an XMLA command fails, it doesn't really help with WHY the XMLA command would be failing in the first place. The server name is definitely defined correctly and the server seems to be doing something while the command executes. But no .abf file is generated. Is there some way I can troubleshoot this issue to determine why the XMLA command might be failing?

Dave F.

|||

Once you get a fix in SQL Agent you'll be able to get a useful error message telling you the nature of the problem. And that should help troubleshooting SQL Agent job.

For now you can try installing CTP of SP2.

Another idea for you is to use ascmd utility that allows you to execute XMLA commands. It has great functionality allows for having parametric XMLA command, where you can simply plug name of your DB and that would take care backing up your database. It also allows logging output of XMLA command's execution. Once you get it working, you can use SQL Agent to schedule backup using ascmd.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am running SQL 2005 with SP2 and have the same issue.

When I use a SQL Agent job with a Sql Server Analysis Services Command it does not report any error or failure in that command.

An example command is:

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>PGPDailySales</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>

If the cube definition or data has errors so this command fails, the Agent Job step still reports success.

How can this issue be reported to Microsoft so it may be fixed in the next version?

backup schedule

SQL server 2005 is installed but can not schedule a backup job.
In the maintenance plan when selecting a new maintenance plan or maintenance plan wizard an error is produced which is:
'Agent XPs' component is turned off as part of the security configuration for this server.
I can see that the SQL Server Agent (Agent XPs is disabled). When trying to start this by right clicking on it in object explorer, the error message is 'unable to start service SQLAgent$SQL2005 on server serverName'. Service Logon failure

How can I scedule a back up job on this server?

Any thoughts please?

"Service Logon Failure" message means that user name and pwd provided for the service are incorrect. You can fix them in services snap-in (type services.msc in commnd line or try My Computer -> Manage -> Services And Applications -> Services -> double click on SQLAgent$SQL2005 service -> Log On tab )|||

Hi,

Went to the Logon Tab.

There is already a log on setup as admin

What next?

Thanks

|||

There is already a log on setup as admin

Then provide a correct password for him.

Backup process lock others out of DB?

I had a backup fail on PM run because of diskspace issue. Fixed that and ran
the job this AM. It just about crippled our DB, and my web-server couldn't
get data, just time outs.
Is this normal for the DB backup to override requests for data? If so are
there other backup software packages to create a .bak file instead of EM's
jobs?
TIA
__Stephen
No, its not normal...were you writing to the same disk that the datafile(s)
reside on?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:O2wtHkv3FHA.1396@.TK2MSFTNGP12.phx.gbl...
>I had a backup fail on PM run because of diskspace issue. Fixed that and
>ran the job this AM. It just about crippled our DB, and my web-server
>couldn't get data, just time outs.
> Is this normal for the DB backup to override requests for data? If so are
> there other backup software packages to create a .bak file instead of EM's
> jobs?
> TIA
> __Stephen
>
|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:eSJ%23IEw3FHA.696@.TK2MSFTNGP09.phx.gbl...
> No, its not normal...were you writing to the same disk that the
> datafile(s) reside on?
No I write to a separate server for all backups and transaction logs. I
didn't think it was correct either.
Actually my server was getting hammered with batch work at the time. It's
Tues. morning so lets try to batch in 20% of our weekly business. I do a
tran log backup every 15 min and the one this morning at the time things got
hairy was only 514 meg. Normally its in the 5+ meg range. So that massive
file is an indication that I'll be beating on my system hard for the next
hour or two. We are a print shop and we feed 3 Xerox doc-u-beasts doing
financial outputs and checks.

Backup process lock others out of DB?

I had a backup fail on PM run because of diskspace issue. Fixed that and ran
the job this AM. It just about crippled our DB, and my web-server couldn't
get data, just time outs.
Is this normal for the DB backup to override requests for data? If so are
there other backup software packages to create a .bak file instead of EM's
jobs?
TIA
__StephenNo, its not normal...were you writing to the same disk that the datafile(s)
reside on?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:O2wtHkv3FHA.1396@.TK2MSFTNGP12.phx.gbl...
>I had a backup fail on PM run because of diskspace issue. Fixed that and
>ran the job this AM. It just about crippled our DB, and my web-server
>couldn't get data, just time outs.
> Is this normal for the DB backup to override requests for data? If so are
> there other backup software packages to create a .bak file instead of EM's
> jobs?
> TIA
> __Stephen
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:eSJ%23IEw3FHA.696@.TK2MSFTNGP09.phx.gbl...
> No, its not normal...were you writing to the same disk that the
> datafile(s) reside on?
No I write to a separate server for all backups and transaction logs. I
didn't think it was correct either.
Actually my server was getting hammered with batch work at the time. It's
Tues. morning so lets try to batch in 20% of our weekly business. I do a
tran log backup every 15 min and the one this morning at the time things got
hairy was only 514 meg. Normally its in the 5+ meg range. So that massive
file is an indication that I'll be beating on my system hard for the next
hour or two. We are a print shop and we feed 3 Xerox doc-u-beasts doing
financial outputs and checks.

Backup process lock others out of DB?

I had a backup fail on PM run because of diskspace issue. Fixed that and ran
the job this AM. It just about crippled our DB, and my web-server couldn't
get data, just time outs.
Is this normal for the DB backup to override requests for data? If so are
there other backup software packages to create a .bak file instead of EM's
jobs?
TIA
__StephenNo, its not normal...were you writing to the same disk that the datafile(s)
reside on?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:O2wtHkv3FHA.1396@.TK2MSFTNGP12.phx.gbl...
>I had a backup fail on PM run because of diskspace issue. Fixed that and
>ran the job this AM. It just about crippled our DB, and my web-server
>couldn't get data, just time outs.
> Is this normal for the DB backup to override requests for data? If so are
> there other backup software packages to create a .bak file instead of EM's
> jobs?
> TIA
> __Stephen
>|||"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:eSJ%23IEw3FHA.696@.TK2MSFTNGP09.phx.gbl...
> No, its not normal...were you writing to the same disk that the
> datafile(s) reside on?
No I write to a separate server for all backups and transaction logs. I
didn't think it was correct either.
Actually my server was getting hammered with batch work at the time. It's
Tues. morning so lets try to batch in 20% of our weekly business. I do a
tran log backup every 15 min and the one this morning at the time things got
hairy was only 514 meg. Normally its in the 5+ meg range. So that massive
file is an indication that I'll be beating on my system hard for the next
hour or two. We are a print shop and we feed 3 Xerox doc-u-beasts doing
financial outputs and checks.

Backup plan reccomendations for user with ZERO experience.

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.

Our database is fairly small we have 7 users with access to the
database. That is it.

any advice or good resources would be greatly appreciated.Look into Enterprise Manager, Maintenance Plan Wizard. It will help
you with various tasks including backups.
In my backup jobs, I first perform a log backup then a full backup.
This will keep the size of the transaction log in check as it gets
truncated during this process. (Assuming the database is in FULL and
not SIMPLE. Check the properties of the database via EM).

I found that when I only did a full backup, my transaction log kept
growing.
MC2
Rob

On Aug 14, 10:49 pm, scott s <scott.str...@.gmail.comwrote:

Quote:

Originally Posted by

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.
>
Our database is fairly small we have 7 users with access to the
database. That is it.
>
any advice or good resources would be greatly appreciated.

|||scott s (scott.streit@.gmail.com) writes:

Quote:

Originally Posted by

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.
>
Our database is fairly small we have 7 users with access to the
database. That is it.
>
any advice or good resources would be greatly appreciated.

First you need to determine which recovery model you want to use. There
are three to choose from FULL, BULK_LOGGED and SIMPLE. BULK_LOGGED is really
a variation of FULL, and we can leave it out for now.

In FULL recovery mode you are able to recover from a failure to any point
in time. In SIMPLE recovery, you can only recover by restoring the last
backup. So the question you need to answer: if you take a full backup
at midight, and at 15:00 something bad happens (disk crash, an UPDATE
without a WHERE clause or similar), what is your business requirement:
being able to recover the state at 14:59? Or are you fully content with
restoring the backup from midnight?

If you are content with restoring from the most recent backup, make
sure the database is simple recovery:

ALTER DATABASE db SET RECOVERY SIMPLE

now all you have to bother about is setting up full backup that is
scheduled each night, and that the backup file is copied somewhere. You
should make sure that you have backups a couple of days back in time
available, in case some accident is not discovered in time, or if a
backup is unreadable for some reason.

If you need up-to-the-point recovery, you also need to take log backups,
and you need full recovery. It's important to understand that taking log
backups is independet from database backups. If you never back up the log,
it will grow and grow. Since you are unexperienced, the best may be to
set up database backups through a maintenance plan in Enterprise
Manager.

Once you have your backups, you should also test restoring them, so
that you know the procedure if disaster strikes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Backup pefromed by SQLSERVERAGENT fails

I just created five new BU devices, then a job for each
and executed the jobs to create the intial backups on a
new database.
Four of the jobs worked fine. In Event Viewer, the Source
= MSSQLSERVER and the USER = SQLEXEC.
One job failed. Source = SQLSERVERAGENT and USER = N/A
Where did I go wrong? Why does the failing job use a
different USER and Source?
TIA MikeYou need to give us more info. Without the error message, it is like saying: "My computer doesn't
work. What is the problem?".
Check the job history, check "show step details", and go to the relevant step. See what the error
message is. Also, see the Agent errorlog.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Hoyt" <mhoyt@.affiliatedhealth.org> wrote in message
news:0ec301c39d9e$db5dff70$a001280a@.phx.gbl...
> I just created five new BU devices, then a job for each
> and executed the jobs to create the intial backups on a
> new database.
> Four of the jobs worked fine. In Event Viewer, the Source
> = MSSQLSERVER and the USER = SQLEXEC.
> One job failed. Source = SQLSERVERAGENT and USER = N/A
> Where did I go wrong? Why does the failing job use a
> different USER and Source?
> TIA Mike|||The Job is failing at Step 1, which was invoked by
Schedule 1. Step 1 is the only step, and it is the basic
backup syntax. The SQLAgent error log is blank for
failures and warnings, information is the usual start up
stuff.
I know the agent is running, th eother jobs run and I get
email alerts of their completion. I get the email alert
of the failure of this job.
What I'm hoping osmebody can tell me is why this job is
run by Source = SQLSERVERAGENT and USER = N/A and the
other, sucessfull jobs, are run by MSSQLSERVER and the
USER = SQLEXEC|||I haven't played with this to see if this is the case but do
the jobs have different owners?
-Sue
On Wed, 29 Oct 2003 08:55:41 -0800,
<anonymous@.discussions.microsoft.com> wrote:
>The Job is failing at Step 1, which was invoked by
>Schedule 1. Step 1 is the only step, and it is the basic
>backup syntax. The SQLAgent error log is blank for
>failures and warnings, information is the usual start up
>stuff.
>I know the agent is running, th eother jobs run and I get
>email alerts of their completion. I get the email alert
>of the failure of this job.
>What I'm hoping osmebody can tell me is why this job is
>run by Source = SQLSERVERAGENT and USER = N/A and the
>other, sucessfull jobs, are run by MSSQLSERVER and the
>USER = SQLEXEC|||Have you checked job step history? Right click the job, select history, and
check job step details.
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
<anonymous@.discussions.microsoft.com> wrote in message
news:0e5501c39e3d$7c4f95d0$a501280a@.phx.gbl...
> The Job is failing at Step 1, which was invoked by
> Schedule 1. Step 1 is the only step, and it is the basic
> backup syntax. The SQLAgent error log is blank for
> failures and warnings, information is the usual start up
> stuff.
> I know the agent is running, th eother jobs run and I get
> email alerts of their completion. I get the email alert
> of the failure of this job.
> What I'm hoping osmebody can tell me is why this job is
> run by Source = SQLSERVERAGENT and USER = N/A and the
> other, sucessfull jobs, are run by MSSQLSERVER and the
> USER = SQLEXEC

Backup operation/job fail information

Hi,
I would like to know about the internal information regarding sql backup
operation.
I want to monitor the Backup operation in all version of sql server. I am
able to get all the information from the msdb database but i'll not find any
information regarding backup fails.
Suppose i want to monitor a database in a certain time interval and i want
to collect data for successful backup and failed backup.
How do i collect these information through my code?
Should i read and parse Error log file or sql provide some system table or
stored procedure for this?
Or there are any api provided by sql?
I'm waiting for positive response.
Thanks n Regards,
Nikhil Kumar JainHi
Yes , Error Log should give you an info.
"Nikhil Kumar Jain" <NikhilKumarJain@.discussions.microsoft.com> wrote in
message news:B0074C50-F6B0-4370-B95A-8A8C7E7DB0AD@.microsoft.com...
> Hi,
> I would like to know about the internal information regarding sql backup
> operation.
> I want to monitor the Backup operation in all version of sql server. I am
> able to get all the information from the msdb database but i'll not find
> any
> information regarding backup fails.
> Suppose i want to monitor a database in a certain time interval and i want
> to collect data for successful backup and failed backup.
> How do i collect these information through my code?
> Should i read and parse Error log file or sql provide some system table or
> stored procedure for this?
> Or there are any api provided by sql?
> I'm waiting for positive response.
> Thanks n Regards,
> Nikhil Kumar Jain

Backup of transaction log

Alwin,
Create a job, use statement "backup log", and schedule the job.
AMB
"Alwin" wrote:

> How to schedule backup of transaction log?
>Thanks AMB for your quick reply ,
But I am new to sql server can you tell me stepswise solution.
Alejandro Mesa wrote:
[vbcol=seagreen]
> Alwin,
> Create a job, use statement "backup log", and schedule the job.
>
> AMB
> "Alwin" wrote:
>|||How to schedule backup of transaction log?|||Alwin,
Create a job, use statement "backup log", and schedule the job.
AMB
"Alwin" wrote:

> How to schedule backup of transaction log?
>|||Thanks AMB for your quick reply ,
But I am new to sql server can you tell me stepswise solution.
Alejandro Mesa wrote:
[vbcol=seagreen]
> Alwin,
> Create a job, use statement "backup log", and schedule the job.
>
> AMB
> "Alwin" wrote:
>|||Alwin wrote:
> Thanks AMB for your quick reply ,
> But I am new to sql server can you tell me stepswise solution.
>
> Alejandro Mesa wrote:
>
>
IF you're not familiar with SQL programming, the easiest might be to
create a maintenance plan to do the job. You can read up on maintenance
plans in Books On Line.
Regards
Steen Schlter Persson
DBA|||Alwin wrote:
> Thanks AMB for your quick reply ,
> But I am new to sql server can you tell me stepswise solution.
>
> Alejandro Mesa wrote:
>
>
IF you're not familiar with SQL programming, the easiest might be to
create a maintenance plan to do the job. You can read up on maintenance
plans in Books On Line.
Regards
Steen Schlter Persson
DBA

Backup of Databases

For a static READ Only database with no scheduled job, is there any
databases other than those mentioned below I have to make a backup for
recovery purpose ?
1) User Database
2) Master
3) MSDB
Thanks
"Stephen" schrieb:
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
No. With no scheduled jobs you probably don't even need the msdb (if there's
no alerts, operators, or email support defined).
The master db is only needed to restore the logins that might map to db user
accounts.
The user db is generally restorable without any system db restore (that's
why you can restore a user db on any given SQL Server ...).
|||Hi,
You are good enough.
If it is a read only database, then you could backup the database once and
keep it in a safe location. This includes all your system databases
(Master and MSDB) as well as your user databases.
Thanks
Hari
SQL Server MVP
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23h5mcSXeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
>

Backup of Databases

For a static READ Only database with no scheduled job, is there any
databases other than those mentioned below I have to make a backup for
recovery purpose ?
1) User Database
2) Master
3) MSDB
Thanks"Stephen" schrieb:
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
No. With no scheduled jobs you probably don't even need the msdb (if there's
no alerts, operators, or email support defined).
The master db is only needed to restore the logins that might map to db user
accounts.
The user db is generally restorable without any system db restore (that's
why you can restore a user db on any given SQL Server ...).|||Hi,
You are good enough.
If it is a read only database, then you could backup the database once and
keep it in a safe location. This includes all your system databases
(Master and MSDB) as well as your user databases.
Thanks
Hari
SQL Server MVP
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23h5mcSXeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
>

Backup of Databases

For a static READ Only database with no scheduled job, is there any
databases other than those mentioned below I have to make a backup for
recovery purpose ?
1) User Database
2) Master
3) MSDB
Thanks"Stephen" schrieb:
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
No. With no scheduled jobs you probably don't even need the msdb (if there's
no alerts, operators, or email support defined).
The master db is only needed to restore the logins that might map to db user
accounts.
The user db is generally restorable without any system db restore (that's
why you can restore a user db on any given SQL Server ...).|||Hi,
You are good enough.
If it is a read only database, then you could backup the database once and
keep it in a safe location. This includes all your system databases
(Master and MSDB) as well as your user databases.
Thanks
Hari
SQL Server MVP
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23h5mcSXeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
>