backup failed

hey
Sql Server 2005
I have Sql Server 2005 installed on a server. Each night the Sql Server
Agent start a job. This job fails. I wonder what causing this job to fail.
I've checked in Event viewer and it doesn't say more than "the job failed,
the job was invoked by Schedule 2 (backup_db), the last step to run was 1
(subplan)"
Any ideas how to find what causing this? Could I manually start this Sql
Server Agent job and then see what causing the job to failed?
This job have been running for months, it's only during the last 3 days it
started to fail
Basically I want to fix this job so it get completed successfully, so I
thought knowing what causing it to fail could help solving the problem...
but the main thing is that I want the job to complete successfully
any suggestions?Jeff
Please specify an output file in the step of the job (Steps--Edit--Advanced)
and see what is going on
"Jeff" <donot@.spam.me> wrote in message
news:uhhzFYIQIHA.4444@.TK2MSFTNGP02.phx.gbl...
> hey
> Sql Server 2005
> I have Sql Server 2005 installed on a server. Each night the Sql Server
> Agent start a job. This job fails. I wonder what causing this job to fail.
> I've checked in Event viewer and it doesn't say more than "the job failed,
> the job was invoked by Schedule 2 (backup_db), the last step to run was 1
> (subplan)"
> Any ideas how to find what causing this? Could I manually start this Sql
> Server Agent job and then see what causing the job to failed?
> This job have been running for months, it's only during the last 3 days it
> started to fail
> Basically I want to fix this job so it get completed successfully, so I
> thought knowing what causing it to fail could help solving the problem...
> but the main thing is that I want the job to complete successfully
> any suggestions?
>
>|||Hi Jeff,
First check the job history information. Right-click your job, View History
and click the + sign to see the job steps.
Also, check the SQL Server error log for related error messages.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jeff" wrote:
> hey
> Sql Server 2005
> I have Sql Server 2005 installed on a server. Each night the Sql Server
> Agent start a job. This job fails. I wonder what causing this job to fail.
> I've checked in Event viewer and it doesn't say more than "the job failed,
> the job was invoked by Schedule 2 (backup_db), the last step to run was 1
> (subplan)"
> Any ideas how to find what causing this? Could I manually start this Sql
> Server Agent job and then see what causing the job to failed?
> This job have been running for months, it's only during the last 3 days it
> started to fail
> Basically I want to fix this job so it get completed successfully, so I
> thought knowing what causing it to fail could help solving the problem...
> but the main thing is that I want the job to complete successfully
> any suggestions?
>
>|||Hi Jeff
It sounds like you are not looking at the steps in the job history. Also
check out the SQL Server error log to see if anything has been written.
Which version of SQL Server are you running (SELECT @.@.VERSION)? It sounds
like this job is from a maintenance plan, you could try dropping the plan and
re-creating it.
John
"Jeff" wrote:
> hey
> Sql Server 2005
> I have Sql Server 2005 installed on a server. Each night the Sql Server
> Agent start a job. This job fails. I wonder what causing this job to fail.
> I've checked in Event viewer and it doesn't say more than "the job failed,
> the job was invoked by Schedule 2 (backup_db), the last step to run was 1
> (subplan)"
> Any ideas how to find what causing this? Could I manually start this Sql
> Server Agent job and then see what causing the job to failed?
> This job have been running for months, it's only during the last 3 days it
> started to fail
> Basically I want to fix this job so it get completed successfully, so I
> thought knowing what causing it to fail could help solving the problem...
> but the main thing is that I want the job to complete successfully
> any suggestions?
>
>|||select @.@.version returned
Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86) Feb 13 2007 23:02:48
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 2)
Step history (View History) said "Executed as user: PC1\SYSTEM. The package
execution failed. The step failed"
I didn't find any info in the Sql Server Error Logs about this|||select @.@.version returned
Microsoft SQL Server 2005 - 9.00.2050.00 (Intel X86) Feb 13 2007 23:02:48
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 2)
Step history (View History) said "Executed as user: PC1\SYSTEM. The package
execution failed. The step failed"
I didn't find any info in the Sql Server Error Logs about this
any suggestions?|||In the Sql Server Error Log I found this info (this info was added to the
log about 3 seconds after the Job started):
Message
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE
statement to install.
***
This info was added to the Sql Server Error Log about the time the error
occured
CHECKDB for database 'test_db' finished without errors on 2007-12-17
04:00:04.470 (local time). This is an informational message only; no user
action is required.
any suggestions?|||In the Sql Server Error Log I found this info (this info was added to the
log about 3 seconds after the Job started):
Message
Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE
statement to install.
***
This info was added to the Sql Server Error Log about the time the error
occured
CHECKDB for database 'test_db' finished without errors on 2007-12-17
04:00:04.470 (local time). This is an informational message only; no user
action is required.
any suggestions?|||In the the Log File Viewer, I select Maintenace Plan and there I see to rows
marked with a green icon and a + sign to the left... I clicked on the + to
reveal what's under the row.. then it revealed a row with an red stop icon
to the left...
On one of the rows with red stop icon, I read this error message:
Could not generate mail report.An exception occurred while executing a
Transact-SQL statement or batch.No global profile is configured. Specify a
profile name in the @.profile_name parameter.
any suggestions?|||Hi Jeff
It looks like it is having problems sending the email notification. Try
changing the maintenance plan to remove these. There is probably a notify
operator task that needs to be re-configured. Also check that you can send
test emails for database mail and SQL Agent mail configuration (in the
properties)
John
"Jeff" wrote:
> In the the Log File Viewer, I select Maintenace Plan and there I see to rows
> marked with a green icon and a + sign to the left... I clicked on the + to
> reveal what's under the row.. then it revealed a row with an red stop icon
> to the left...
> On one of the rows with red stop icon, I read this error message:
> Could not generate mail report.An exception occurred while executing a
> Transact-SQL statement or batch.No global profile is configured. Specify a
> profile name in the @.profile_name parameter.
> any suggestions?
>
>|||I modifyed the Maintenance Plan -> selected the Maintenance plan I selected
"Modify". then I opened the "Reporting and Logging" window and unchecked
"Send report to an email recipient". Maybe the error was here... the agent
operator set as recipient was an domain account which not exist anymore
It's a long time since this domain account was removed - strange if caused
problem for sqlserver now
I saved my modifications to the maintenance plan...
How do I manually test if this actually solved the problem'
Best Regards
Jeff|||Hi Jeff
It may be that the account had been disabled but the mailbox remained until
more recently!!
At least everything works now!
John
"Jeff" wrote:
> I modifyed the Maintenance Plan -> selected the Maintenance plan I selected
> "Modify". then I opened the "Reporting and Logging" window and unchecked
> "Send report to an email recipient". Maybe the error was here... the agent
> operator set as recipient was an domain account which not exist anymore
> It's a long time since this domain account was removed - strange if caused
> problem for sqlserver now
> I saved my modifications to the maintenance plan...
> How do I manually test if this actually solved the problem'
> Best Regards
> Jeff
>
>|||Right click on the maintanence plan and select Execute from the popup menu
and View History for that plan (by right clicking on it again...)
--
Ekrem Önsoy
"Jeff" <donot@.spam.me> wrote in message
news:eDsSQBLQIHA.4584@.TK2MSFTNGP03.phx.gbl...
>I modifyed the Maintenance Plan -> selected the Maintenance plan I selected
>"Modify". then I opened the "Reporting and Logging" window and unchecked
>"Send report to an email recipient". Maybe the error was here... the agent
>operator set as recipient was an domain account which not exist anymore
> It's a long time since this domain account was removed - strange if caused
> problem for sqlserver now
> I saved my modifications to the maintenance plan...
> How do I manually test if this actually solved the problem'
> Best Regards
> Jeff
>|||I tryed to execute the maintenace plan, but it failed... the second time I
tryed to execute it it gave me this error message:
Execute Maintenance Plan
An exception occured while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectInfo)
SqlServerAgent Error: Request to run job backup_db (from <domain>\Jeff)
refused because the job is already running from a request by User
<domain>\Jeff (Microsft Sql Server, Error 22022)
I'm not sure how the first error message was (I tryed to execute the
maintenance plan twice)...
strange if I should get an error that the job is already running...
well, maybe it was caused by my user account, which not a member of the
local administrator on that server, but I'm a member of the administrators
in the domain
any suggestions?|||Hi
Did you try to execute the job or the maintenance plan? If you look at the
view history of the plan it may have something in for the last run, if it is
still running then it would be incomplete information.
John
"Jeff" wrote:
> I tryed to execute the maintenace plan, but it failed... the second time I
> tryed to execute it it gave me this error message:
> Execute Maintenance Plan
> An exception occured while executing a Transact-SQL statement or batch
> (Microsoft.SqlServer.ConnectInfo)
> SqlServerAgent Error: Request to run job backup_db (from <domain>\Jeff)
> refused because the job is already running from a request by User
> <domain>\Jeff (Microsft Sql Server, Error 22022)
> I'm not sure how the first error message was (I tryed to execute the
> maintenance plan twice)...
> strange if I should get an error that the job is already running...
> well, maybe it was caused by my user account, which not a member of the
> local administrator on that server, but I'm a member of the administrators
> in the domain
> any suggestions?
>
>|||It fails because as the error message refers it's already running. You
should check the history for any detailed error messages about the running
job if there is any.
--
Ekrem Önsoy
"Jeff" <donot@.spam.me> wrote in message
news:enncutLQIHA.5184@.TK2MSFTNGP05.phx.gbl...
>I tryed to execute the maintenace plan, but it failed... the second time I
>tryed to execute it it gave me this error message:
> Execute Maintenance Plan
> An exception occured while executing a Transact-SQL statement or batch
> (Microsoft.SqlServer.ConnectInfo)
> SqlServerAgent Error: Request to run job backup_db (from <domain>\Jeff)
> refused because the job is already running from a request by User
> <domain>\Jeff (Microsft Sql Server, Error 22022)
> I'm not sure how the first error message was (I tryed to execute the
> maintenance plan twice)...
> strange if I should get an error that the job is already running...
> well, maybe it was caused by my user account, which not a member of the
> local administrator on that server, but I'm a member of the administrators
> in the domain
> any suggestions?
>

backup exec fails (though having the sql addon)

Hi group
Working with backup exec 10, sql agent and advanced open file options
included to backup our databases. We have 11 SQL databases varying in size
from 200 to 5000mb. There is one new dbase which is the database for a
website that is now not being backed up (size, approx 1.5 GB), this has
never worked since it was created last week.
(for now we are doing a full backup dump in non production hours to a
location that is backed up)
Additional info:
-Backupexec runs under a domain admin account
-SQL Server runs under another account
-owner for databases is domain admin
-2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
-backupexec shows fileskipped messages without specifying the file
-no usable info in eventvwr
-there are jobs running on this database (although not when the backup is
run)
-no locks/active connections are shown in the enterprise manager
Are there any issues with sql databases that are used by websites?
Has anybody have experienced similar problems and have a sollution available
that might work for me?
thx.
PerryPerry,
When you view the history for the backup job with BackupExec what error
message is recorded, if any?
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
Using? Are you using Enterprise Manager? If not, does that work? Any
errors in the SQL errorlog for the backup failing?
HTH
Jerry
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Hi
Never use open file options against a SQL Server database as the DB and
Transaction log will never be restorable as they will not be in sync at a
point in time.
Backup the database to disk from within SQL Server (see BACKUP command in
BOL), or use the SQL Server maintenance wizard.
Backup the backup file files that SQL Server generated.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Mike,
I have this same argument with my IT director. It's been one of those he
won't budge...I won't budge thing so...Veritas does have an agent for SQL
backups. We have the license but I've chosen not to use it (yet) not
because it doesn't work but because it is a bit more challenging and
involved. I do backup the databases using EM (jobs) then I use Veritas
BackupExec to backup the backup files to tape for off-site storage.
HTH
Jerry
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ObMOEeVxFHA.3756@.tk2msftngp13.phx.gbl...
> Hi
> Never use open file options against a SQL Server database as the DB and
> Transaction log will never be restorable as they will not be in sync at a
> point in time.
> Backup the database to disk from within SQL Server (see BACKUP command in
> BOL), or use the SQL Server maintenance wizard.
> Backup the backup file files that SQL Server generated.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Hi group
>> Working with backup exec 10, sql agent and advanced open file options
>> included to backup our databases. We have 11 SQL databases varying in
>> size from 200 to 5000mb. There is one new dbase which is the database for
>> a website that is now not being backed up (size, approx 1.5 GB), this has
>> never worked since it was created last week.
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
>> Additional info:
>> -Backupexec runs under a domain admin account
>> -SQL Server runs under another account
>> -owner for databases is domain admin
>> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
>> -backupexec shows fileskipped messages without specifying the file
>> -no usable info in eventvwr
>> -there are jobs running on this database (although not when the backup is
>> run)
>> -no locks/active connections are shown in the enterprise manager
>> Are there any issues with sql databases that are used by websites?
>> Has anybody have experienced similar problems and have a sollution
>> available that might work for me?
>> thx.
>> Perry
>>
>>
>|||Jerry,
Below is what the log says, the servername is DC4, the databasename is
SYNERGY:
Job Completion Status
Job ended: Wednesday, September 28, 2005 at 8:36:41 PM
Completed status: Completed with exceptions
Errors
Click an error below to locate it in the job log
Backup- DC4 Database ?SYNERGY was not found, or could not be accessed.
Exceptions
Click an exception below to locate it in the job log
Backup- DC4 The item DC4\SYNERGY in use - skipped.Perry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u4j16tUxFHA.3740@.tk2msftngp13.phx.gbl...
> Perry,
> When you view the history for the backup job with BackupExec what error
> message is recorded, if any?
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
> Using? Are you using Enterprise Manager? If not, does that work? Any
> errors in the SQL errorlog for the backup failing?
> HTH
> Jerry
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Hi group
>> Working with backup exec 10, sql agent and advanced open file options
>> included to backup our databases. We have 11 SQL databases varying in
>> size from 200 to 5000mb. There is one new dbase which is the database for
>> a website that is now not being backed up (size, approx 1.5 GB), this has
>> never worked since it was created last week.
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
>> Additional info:
>> -Backupexec runs under a domain admin account
>> -SQL Server runs under another account
>> -owner for databases is domain admin
>> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
>> -backupexec shows fileskipped messages without specifying the file
>> -no usable info in eventvwr
>> -there are jobs running on this database (although not when the backup is
>> run)
>> -no locks/active connections are shown in the enterprise manager
>> Are there any issues with sql databases that are used by websites?
>> Has anybody have experienced similar problems and have a sollution
>> available that might work for me?
>> thx.
>> Perry
>>
>>
>

backup exec fails (though having the sql addon)

Hi group
Working with backup exec 10, sql agent and advanced open file options
included to backup our databases. We have 11 SQL databases varying in size
from 200 to 5000mb. There is one new dbase which is the database for a
website that is now not being backed up (size, approx 1.5 GB), this has
never worked since it was created last week.
(for now we are doing a full backup dump in non production hours to a
location that is backed up)
Additional info:
-Backupexec runs under a domain admin account
-SQL Server runs under another account
-owner for databases is domain admin
-2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
-backupexec shows fileskipped messages without specifying the file
-no usable info in eventvwr
-there are jobs running on this database (although not when the backup is
run)
-no locks/active connections are shown in the enterprise manager
Are there any issues with sql databases that are used by websites?
Has anybody have experienced similar problems and have a sollution available
that might work for me?
thx.
Perry
Perry,
When you view the history for the backup job with BackupExec what error
message is recorded, if any?

> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
Using? Are you using Enterprise Manager? If not, does that work? Any
errors in the SQL errorlog for the backup failing?
HTH
Jerry
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>
|||Hi
Never use open file options against a SQL Server database as the DB and
Transaction log will never be restorable as they will not be in sync at a
point in time.
Backup the database to disk from within SQL Server (see BACKUP command in
BOL), or use the SQL Server maintenance wizard.
Backup the backup file files that SQL Server generated.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>
|||Mike,
I have this same argument with my IT director. It's been one of those he
won't budge...I won't budge thing so...Veritas does have an agent for SQL
backups. We have the license but I've chosen not to use it (yet) not
because it doesn't work but because it is a bit more challenging and
involved. I do backup the databases using EM (jobs) then I use Veritas
BackupExec to backup the backup files to tape for off-site storage.
HTH
Jerry
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ObMOEeVxFHA.3756@.tk2msftngp13.phx.gbl...
> Hi
> Never use open file options against a SQL Server database as the DB and
> Transaction log will never be restorable as they will not be in sync at a
> point in time.
> Backup the database to disk from within SQL Server (see BACKUP command in
> BOL), or use the SQL Server maintenance wizard.
> Backup the backup file files that SQL Server generated.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>
|||Jerry,
Below is what the log says, the servername is DC4, the databasename is
SYNERGY:
Job Completion Status
Job ended: Wednesday, September 28, 2005 at 8:36:41 PM
Completed status: Completed with exceptions
Errors
Click an error below to locate it in the job log
Backup- DC4 Database ?SYNERGY was not found, or could not be accessed.
Exceptions
Click an exception below to locate it in the job log
Backup- DC4 The item DC4\SYNERGY in use - skipped.Perry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u4j16tUxFHA.3740@.tk2msftngp13.phx.gbl...
> Perry,
> When you view the history for the backup job with BackupExec what error
> message is recorded, if any?
>
> Using? Are you using Enterprise Manager? If not, does that work? Any
> errors in the SQL errorlog for the backup failing?
> HTH
> Jerry
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

backup exec fails (though having the sql addon)

Hi group
Working with backup exec 10, sql agent and advanced open file options
included to backup our databases. We have 11 SQL databases varying in size
from 200 to 5000mb. There is one new dbase which is the database for a
website that is now not being backed up (size, approx 1.5 GB), this has
never worked since it was created last week.
(for now we are doing a full backup dump in non production hours to a
location that is backed up)
Additional info:
-Backupexec runs under a domain admin account
-SQL Server runs under another account
-owner for databases is domain admin
-2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
-backupexec shows fileskipped messages without specifying the file
-no usable info in eventvwr
-there are jobs running on this database (although not when the backup is
run)
-no locks/active connections are shown in the enterprise manager
Are there any issues with sql databases that are used by websites?
Has anybody have experienced similar problems and have a sollution available
that might work for me?
thx.
PerryPerry,
When you view the history for the backup job with BackupExec what error
message is recorded, if any?

> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
Using? Are you using Enterprise Manager? If not, does that work? Any
errors in the SQL errorlog for the backup failing?
HTH
Jerry
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Hi
Never use open file options against a SQL Server database as the DB and
Transaction log will never be restorable as they will not be in sync at a
point in time.
Backup the database to disk from within SQL Server (see BACKUP command in
BOL), or use the SQL Server maintenance wizard.
Backup the backup file files that SQL Server generated.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Mike,
I have this same argument with my IT director. It's been one of those he
won't budge...I won't budge thing so...Veritas does have an agent for SQL
backups. We have the license but I've chosen not to use it (yet) not
because it doesn't work but because it is a bit more challenging and
involved. I do backup the databases using EM (jobs) then I use Veritas
BackupExec to backup the backup files to tape for off-site storage.
HTH
Jerry
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ObMOEeVxFHA.3756@.tk2msftngp13.phx.gbl...
> Hi
> Never use open file options against a SQL Server database as the DB and
> Transaction log will never be restorable as they will not be in sync at a
> point in time.
> Backup the database to disk from within SQL Server (see BACKUP command in
> BOL), or use the SQL Server maintenance wizard.
> Backup the backup file files that SQL Server generated.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>|||Jerry,
Below is what the log says, the servername is DC4, the databasename is
SYNERGY:
Job Completion Status
Job ended: Wednesday, September 28, 2005 at 8:36:41 PM
Completed status: Completed with exceptions
Errors
Click an error below to locate it in the job log
Backup- DC4 Database ?SYNERGY was not found, or could not be accessed.
Exceptions
Click an exception below to locate it in the job log
Backup- DC4 The item DC4\SYNERGY in use - skipped.Perry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u4j16tUxFHA.3740@.tk2msftngp13.phx.gbl...
> Perry,
> When you view the history for the backup job with BackupExec what error
> message is recorded, if any?
>
> Using? Are you using Enterprise Manager? If not, does that work? Any
> errors in the SQL errorlog for the backup failing?
> HTH
> Jerry
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

Backup Exec and MDAC update

I have a client in a Windows 2000 SP4 network environment
that is using Backup Exec 9.0 with a remote agent for
Exchange and a remote server. They ran the Windows
Updates last Friday and now they are receiving errors on
their backup job when it tries to back up the server that
they updated. The event id in the event viewer is 34323
and reads as follows:
Update to catalog file Catalog index database failed.
Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]
Violation of PRIMARY KEY
constraint 'PK_CatMedia_68487DD7'. Cannot insert dupl
cat_RecordSet:Reopen[]
d:\be\tybee\4367r\becat\server\dll\catme
dia.cpp[753]
I also get the following event id 34338 in conjunction
with the above error:
Backup Exec Alert: Catalog Error
[Server:"servername"]ODBC access error. Possible lost
connection to database or unsuccessful access to catalog
index in the database.
I have submitted this to Veritas forum as well but if
anyone has any ideas, I would appreciate it. The Windows
Update apparently applied KB832483 which is an MDAC
update. I don't see where I can roll this back either.
Thanks. DeannaDeanna,
did you ever figure out a solution to this problem? i'm experiencing
the same thing in the same type of environment.
thank you,
Lenny.
Deanna wrote:
> *I have a client in a Windows 2000 SP4 network environment
> that is using Backup Exec 9.0 with a remote agent for
> Exchange and a remote server. They ran the Windows
> Updates last Friday and now they are receiving errors on
> their backup job when it tries to back up the server that
> they updated. The event id in the event viewer is 34323
> and reads as follows:
> Update to catalog file Catalog index database failed.
> Reason: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Violation of PRIMARY KEY
> constraint 'PK_CatMedia_68487DD7'. Cannot insert dupl
> cat_RecordSet:Reopen[]
> d:\be\tybee\4367r\becat\server\dll\catme
dia.cpp[753]
> I also get the following event id 34338 in conjunction
> with the above error:
> Backup Exec Alert: Catalog Error
> [Server:"servername"]ODBC access error. Possible lost
> connection to database or unsuccessful access to catalog
> index in the database.
> I have submitted this to Veritas forum as well but if
> anyone has any ideas, I would appreciate it. The Windows
> Update apparently applied KB832483 which is an MDAC
> update. I don't see where I can roll this back either.
> Thanks. Deanna *
lennyi
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message422513.html

Backup database aborts job in the agent, but not when in a query window

Does anyone know why a backup database statement aborts a job in a stored procedure so that you can't handle the error with @.@.error, but when you run the same stored procedure in a query window the error handling does get run?

The snippet of code looks like this:

SET XACT_ABORT OFF

SET ANSI_NULLS OFF

set ansi_warnings off

set arithabort on

declare @.backup_status int

declare @.cmd varchar(1024)

SET NOCOUNT ON

print 'Options value: ' + convert(varchar, @.@.options)

set @.backup_status = 0

if (@.backup_mode = 'F')

begin

BACKUP DATABASE

@.DB TO DISK = @.BackupFile

with

NAME = @.Name,

DESCRIPTION = @.Description,

MEDIANAME = @.MediaName,

MEDIADESCRIPTION = @.Description,

STATS = 10,

init, nounload, noskip

if @.@.error != 0

begin

set @.backup_status = 1

print '**************** Backup of ' + @.DB + ' Failed'

end end

When this is run in a query window, the print statement at the bottom does get executed, but when run as a step in a sql agent job it does not.

Thanks.

As an additional comment, this doesn't seem to happen on my SQL 2000 server, just the SQL 2005 sp1 server.

Backup Agent for SQL Server ?

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>
> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Backup Agent for SQL Server ?

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
Thanks> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
A SQL Server backup agent provides centralized administration of SQL Server
backups and backup media. A SQL Server backup agent is usually an ad-on to
a general backup solution and can integrate with other backups as well to
facilitate administration and management.

> What will happen if we don't use the backup agent but just copy the MDF
> file ?
Copying database files (mdf, ndf, ldf) directly cannot be done while the SQL
Server service is running. If you copy these without an agent, you have no
integrity and the copies are useless for recovery.
Your current approach (BACKUP DATABASE and copy to tape) is fine and allows
you to quickly restore (assume the disk backup file is available) and also
allows you store tape backups off-site for archive or disaster recovery
purposes. The downside is that you need to have additional disk space
available for the disk backups, although third party tools are available for
backup compression.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl...
> We perform full backup every night and then copy the BAK files to tape.
>

> What will happen if we don't use the backup agent but just copy the MDF
> file ?
> Thanks
>|||In article <Ouj0YWD0HHA.3772@.TK2MSFTNGP06.phx.gbl>,
Peter@.discussions.microsoft.com says...
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
>
>
Just to add to Dan's comments. We run BackupExec here, but the same
issue applies to any backup program. Databases present a special case
because they are in effect always open, which means that a "normal"
backup will not see them -- it cannot get the exclusive control needed
to allow the backup to happen. So in essence what add-on products such
as Exchange Agent, Sql Agent, Advanced Open File Option (all from
Symantec for Backup Exec) do is to track the changes being made during
the backup and effectively merge them in. Often the Volume Copy Shadow
service is involved. If you look at the messages in the sql log and
agent log from around the time your backup runs, you should see some
events related to databases you did not know even existed that you might
find interesting.
The backup to disk and then to tape works fine. Just be sure to time
coordinate with your backup solution or recovery may get messy
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Backup Agent for SQL Server

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF fi
le
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.

Backup Agent for SQL Server

We perform full backup every night and then copy the BAK files to tape.
The backup vendor suggests us purchasing backup agent for SQL Server. We
would like to know what is the advantage of those agents ?
What will happen if we don't use the backup agent but just copy the MDF file
?
ThanksOn Jul 27, 5:20 am, "Peter" <Pe...@.discussions.microsoft.com> wrote:
> We perform full backup every night and then copy the BAK files to tape.
> The backup vendor suggests us purchasing backup agent for SQL Server. We
> would like to know what is the advantage of those agents ?
> What will happen if we don't use the backup agent but just copy the MDF file
> ?
> Thanks
We use a third party backup tool that greatly compresses the database,
and thus speeds our backups tremendously. In regards to the backup
agents, one example I can think of, but don't personally have
experience with is the Veritas SQL Backup agent. The only thing I can
tell you is that our backup team decided to uninstall that particular
agent and simply backup the .bak files as you mentioned.

Backup

How do you back up the Agent Jobs and the Local Packages under Data
Transformation services? Does this get backed up when you back up the Master
database?
Thanks
RickThey get backed up when you backup the msdb database.
-Sue
On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
<rick@.di-wave.com> wrote:
>How do you back up the Agent Jobs and the Local Packages under Data
>Transformation services? Does this get backed up when you back up the Master
>database?
> Thanks
> Rick
>|||Thanks very much!!!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:9ngljvsocne65i7n5lsftphqpddsqs579u@.4ax.com...
> They get backed up when you backup the msdb database.
> -Sue
> On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
> <rick@.di-wave.com> wrote:
> >How do you back up the Agent Jobs and the Local Packages under Data
> >Transformation services? Does this get backed up when you back up the
Master
> >database?
> >
> > Thanks
> > Rick
> >
>

Backup

I have a windows 2003 server(let call it server a) running arcserve v11 with
backup agent for SQL. I have another server (server b) running SQL. I can
not get the SQL databases from server b backup up on to the tape I have on
server a. All I see on server a is the basic SQL databases that get
installed when the agent is installed. Can someone tell me why? ThankFlores
BACKUP DATABASE dbname TO DISK =
N'\\ServerB\shared_folder\dbname.BAK'
"Flores" <Flores@.discussions.microsoft.com> wrote in message
news:2D4AE076-5C65-49CC-9AE4-6DFE6861001D@.microsoft.com...
>I have a windows 2003 server(let call it server a) running arcserve v11
>with
> backup agent for SQL. I have another server (server b) running SQL. I
> can
> not get the SQL databases from server b backup up on to the tape I have on
> server a. All I see on server a is the basic SQL databases that get
> installed when the agent is installed. Can someone tell me why? Thank|||I'm not sure what you mean to a disk. I want the data to be back up on a
tape like the rest of the data in my network: using arcserve v11 incremental
with grandfather-father-son rotation. Also, I'm not backing up the .bak
files, I'm backing up the .mdf and ldf right from the \Data directory under
Microsoft SQL. Would that make a difference?
"Uri Dimant" wrote:

> Flores
> BACKUP DATABASE dbname TO DISK =
> N'\\ServerB\shared_folder\dbname.BAK'
>
>
>
> "Flores" <Flores@.discussions.microsoft.com> wrote in message
> news:2D4AE076-5C65-49CC-9AE4-6DFE6861001D@.microsoft.com...
>
>|||Flores (Flores@.discussions.microsoft.com) writes:
> I'm not sure what you mean to a disk. I want the data to be back up on
> a tape like the rest of the data in my network: using arcserve v11
> incremental with grandfather-father-son rotation. Also, I'm not backing
> up the .bak files, I'm backing up the .mdf and ldf right from the \Data
> directory under Microsoft SQL. Would that make a difference?
You are doing what? Alright, you can backup the MDF and LDF files, *if*
you have stopped SQL Server. But normally, you have SQL Server running,
those backups are useless, as they will not be transactionally consistent.
You should schedule backups of your database from SQL Server Agent, and
then you can copy the .bak files to disk with ARCSERVE.
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|||Yes...if you are using nothing but the open file agent, you
need to manage it all through ArcServe.
It's generally less problematic and much cheaper to skip the
open file agents, use SQL Server native backups, then backup
the SQL Server backup files to tape.
-Sue
On Thu, 22 Feb 2007 05:24:12 -0800, Flores
<Flores@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I'm not sure what you mean to a disk. I want the data to be back up on a
>tape like the rest of the data in my network: using arcserve v11 incrementa
l
>with grandfather-father-son rotation. Also, I'm not backing up the .bak
>files, I'm backing up the .mdf and ldf right from the \Data directory under
>Microsoft SQL. Would that make a difference?
>"Uri Dimant" wrote:
>|||I've done the SQL backups through the agent for years and from what I
understand, the agent is suppose to backup the databases while they're open.
But for the last few days I've been backing up a directory named backup with
.bak files in it. This directory is under the SQL directory. Is this
directory a result of the SQL native backup you're talking about? If I need
to do a database restore, will I restore it to the backup directory? if so
how does the database get to the SQL\Data directory? Sorry for all these
questions but I do not know much about SQL. Thanks
"Sue Hoegemeier" wrote:

> Yes...if you are using nothing but the open file agent, you
> need to manage it all through ArcServe.
> It's generally less problematic and much cheaper to skip the
> open file agents, use SQL Server native backups, then backup
> the SQL Server backup files to tape.
> -Sue
> On Thu, 22 Feb 2007 05:24:12 -0800, Flores
> <Flores@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:2633
The open file backup agents backup the databases while they
are open using their own code and their own software. You
manage the backups and restores through their software.
When you do native backups, you use the BACKUP command in
SQL Server. SQL Server will backup the database to a file on
disk or tape. This can be done while the databases are in
use. SQL Server can restore those files to restore a
database using the RESTORE command.
The directory with the .bak files may very likely be SQL
Server native backup files. If it's a backup file, you can
view the details by executing
RESTORE HEADERONLY
FROM DISK = 'C:\PathToDirectory\SomeFilename.bak'
- with whatever is the necessary path to the file.
That won't restore the database, it will just return some
information about what's in the bak file.
If you restore a database, the log and data files will be
restored to the same location that the backed up database
had for file locations. You can view those locations for a
backup file by executing
RESTORE FILELISTONLY
FROM DISK = 'C:\PathToDirectory\SomeFilename.bak'
- with whatever is the necessary path to the file.
There is a lot of information about backup and restore in
SQL Server Books Online.
-Sue
On Tue, 27 Feb 2007 08:13:16 -0800, Flores
<Flores@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I've done the SQL backups through the agent for years and from what I
>understand, the agent is suppose to backup the databases while they're open
.
>But for the last few days I've been backing up a directory named backup wit
h
>.bak files in it. This directory is under the SQL directory. Is this
>directory a result of the SQL native backup you're talking about? If I nee
d
>to do a database restore, will I restore it to the backup directory? if so
>how does the database get to the SQL\Data directory? Sorry for all these
>questions but I do not know much about SQL. Thanks
>"Sue Hoegemeier" wrote:
>|||I am having a similar prboem but do not believe it has to do witht he SP2.
My problem started on 3/21/7 and I had not done an updage of anything since
3/3/7... My guess is that it has something to do with the security updates
provided in another patch to Exchange. I am not sure whyt he delay for me.
--
"Flores" wrote:

> I have a windows 2003 server(let call it server a) running arcserve v11 wi
th
> backup agent for SQL. I have another server (server b) running SQL. I ca
n
> not get the SQL databases from server b backup up on to the tape I have on
> server a. All I see on server a is the basic SQL databases that get
> installed when the agent is installed. Can someone tell me why? Thank

Backing up to Network Location

Is there a way to get SQL Server to backup to a file on the network? I have tried running the SQL Server Agent under login with suitable access and using a UNC path. The SQL Server 2000 Maintenance Plan Wizard only allows you to browse to local drives which seems to indicate that this is true. The SQL 2005 Maintenance Plan looks more flexible but I haven't explored that possibility yet.

I solve the problem with a bit of VBScipt that copies the files and cleans up any old files on the local drive and on the network but I am curious as to whether there is another way. One reason for not alowing this could be to maximise the chance of the drive being available I suppose.

Thanks,

Dick Campbell

If you're running an Agent job to do the backup, SQL Agent has to have permissions on the network share. If you're executing a backup statement then the SQL Server process has to have permissions on the share and you have to be logged in as an admin.

Not sure about the Maintenance Plan Wizard behavior.

|||

Thanks, I thought that I had tried using an appropriate Agent login and network share. I will try again.

|||

Now I am really confused. I tried the SQL Server Agent running under my login (with rights to the network) and I copied and pasted the UNC for the network location from Windows Explorer into the backup path. When I do this, the Job fails. My alternative approach, which is a VBScript Step in a Job that copies the backup files to exactly the same network location fails with the SQL Server Agent running under the local system account but succeeds if I run it under my login.

Thanks for your replies so far.

|||If the Sql server startup account is local system the job will fail because with local system you cannot access the network resources .............the sql server startup account needs to be a ID which has access to network resources
And I guess since your id has has the necessary privileges to the network path you would have executed the backup command in the query analyzer and it would hav e succeeded i guess...........Always if you run it as a job you will need the startup account to be an id other than local system.............
|||

I got it working today by running the SQL Server Service under a login with network access rather than just the SQL Server Agent Service. I am not yet whether BOTH services need such a login.

It seems that SQL Server is set up to discourage direct backups over the network and I am not sure whether this id by design.

Any comments on the pros and cons of baking up directly to a network location?

Backing up to Network Location

Is there a way to get SQL Server to backup to a file on the network? I have tried running the SQL Server Agent under login with suitable access and using a UNC path. The SQL Server 2000 Maintenance Plan Wizard only allows you to browse to local drives which seems to indicate that this is true. The SQL 2005 Maintenance Plan looks more flexible but I haven't explored that possibility yet.

I solve the problem with a bit of VBScipt that copies the files and cleans up any old files on the local drive and on the network but I am curious as to whether there is another way. One reason for not alowing this could be to maximise the chance of the drive being available I suppose.

Thanks,

Dick Campbell

If you're running an Agent job to do the backup, SQL Agent has to have permissions on the network share. If you're executing a backup statement then the SQL Server process has to have permissions on the share and you have to be logged in as an admin.

Not sure about the Maintenance Plan Wizard behavior.

|||

Thanks, I thought that I had tried using an appropriate Agent login and network share. I will try again.

|||

Now I am really confused. I tried the SQL Server Agent running under my login (with rights to the network) and I copied and pasted the UNC for the network location from Windows Explorer into the backup path. When I do this, the Job fails. My alternative approach, which is a VBScript Step in a Job that copies the backup files to exactly the same network location fails with the SQL Server Agent running under the local system account but succeeds if I run it under my login.

Thanks for your replies so far.

|||If the Sql server startup account is local system the job will fail because with local system you cannot access the network resources .............the sql server startup account needs to be a ID which has access to network resources
And I guess since your id has has the necessary privileges to the network path you would have executed the backup command in the query analyzer and it would hav e succeeded i guess...........Always if you run it as a job you will need the startup account to be an id other than local system.............
|||

I got it working today by running the SQL Server Service under a login with network access rather than just the SQL Server Agent Service. I am not yet whether BOTH services need such a login.

It seems that SQL Server is set up to discourage direct backups over the network and I am not sure whether this id by design.

Any comments on the pros and cons of baking up directly to a network location?