Backup File Name

Dear all,

On scheduling the online backup for SQL server 2005 with SQL server management studio, how the backup filename can be automatically change for everyday. I mean the backup filename be automatically generated every day.

Thanks

Imran Hasware

You can take help of database maintenance plan in this case, where you can automatically set the filename to change.

Or else refer to the Tara's weblog http://weblogs.sqlteam.com/tarad/ where she got very useful script that can used without any issues.

|||

Dear Satya,

I tried Maintenance plan, but i am getting the following error:

Creating maintenance plan "SAP_Backup_Full" (Error)

Messages

Create maintenance plan failed.

ADDITIONAL INFORMATION:

Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The specified '@.subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476

When I checked the Link, No information is described there. Can you please suggest me.

Thank

Imran Hasware

Backup failure

All,
I am receiving the following error when I try to backup to tape from SQL using backup under the Management folder in Enterprise manager. Logged in as administrator. Can backup the pubs, Northwind and master databases with no problem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using option to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Dave
can you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Dave

Backup failure

All,
I am receiving the following error when I try to backup to tape from SQL usi
ng backup under the Management folder in Enterprise manager. Logged in as ad
ministrator. Can backup the pubs, Northwind and master databases with no pro
blem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using op
tion to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for
more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536,
position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offse
t: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes
: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Davecan you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Davesql

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

Backup and Restore Events Report

In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
Backup and Restore Events Report which does not show any infromation
for the database even though the database has been backed up and is
showing on the DB properties page. Is their any settings that need to
be changed to show this information in the SSRS report?
It's a bug (in the report)
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=148116
that is fixed in SP2 - and of course in SP2 you can write your own report
:-)
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<robin9876@.hotmail.com> wrote in message
news:1173197849.398089.27250@.c51g2000cwc.googlegro ups.com...
> In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
> Backup and Restore Events Report which does not show any infromation
> for the database even though the database has been backed up and is
> showing on the DB properties page. Is their any settings that need to
> be changed to show this information in the SSRS report?
>

Backup and Restore Events Report

In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
Backup and Restore Events Report which does not show any infromation
for the database even though the database has been backed up and is
showing on the DB properties page. Is their any settings that need to
be changed to show this information in the SSRS report?Is the default trace enabled? (sp_configure)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<robin9876@.hotmail.com> wrote in message
news:1173197849.398089.27250@.c51g2000cwc.googlegroups.com...
> In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
> Backup and Restore Events Report which does not show any infromation
> for the database even though the database has been backed up and is
> showing on the DB properties page. Is their any settings that need to
> be changed to show this information in the SSRS report?
>|||It's a bug (in the report)
http://connect.microsoft.com/SQLSer...=14811
6
that is fixed in SP2 - and of course in SP2 you can write your own report
:-)
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<robin9876@.hotmail.com> wrote in message
news:1173197849.398089.27250@.c51g2000cwc.googlegroups.com...
> In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
> Backup and Restore Events Report which does not show any infromation
> for the database even though the database has been backed up and is
> showing on the DB properties page. Is their any settings that need to
> be changed to show this information in the SSRS report?
>

Backup and Restore Events Report

In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
Backup and Restore Events Report which does not show any infromation
for the database even though the database has been backed up and is
showing on the DB properties page. Is their any settings that need to
be changed to show this information in the SSRS report?Is the default trace enabled? (sp_configure)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<robin9876@.hotmail.com> wrote in message
news:1173197849.398089.27250@.c51g2000cwc.googlegroups.com...
> In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
> Backup and Restore Events Report which does not show any infromation
> for the database even though the database has been backed up and is
> showing on the DB properties page. Is their any settings that need to
> be changed to show this information in the SSRS report?
>|||It's a bug (in the report)
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=148116
that is fixed in SP2 - and of course in SP2 you can write your own report
:-)
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
<robin9876@.hotmail.com> wrote in message
news:1173197849.398089.27250@.c51g2000cwc.googlegroups.com...
> In SQL 2005 Management Studio (SP1) at the DB level their is a SSRS
> Backup and Restore Events Report which does not show any infromation
> for the database even though the database has been backed up and is
> showing on the DB properties page. Is their any settings that need to
> be changed to show this information in the SSRS report?
>

Backing up to a network share from SQL Server 2005

Hello-
I'm trying to back my database up to a network share. Using the same account
that I'm logged into SQL Server Management Studio with, I'm able to add and
delete files on the network share just fine. When I try to do a backup to the
same location, I get this message:
System.Data.SqlClient.SqlError: Cannot open backup device
'\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
denied.). (Microsoft.SqlServer.Smo)
Does anyone know why this might be or how best to troubleshoot it?
Regards-
EricIt is the SQL Server service account that need permissions on the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> Hello-
> I'm trying to back my database up to a network share. Using the same account
> that I'm logged into SQL Server Management Studio with, I'm able to add and
> delete files on the network share just fine. When I try to do a backup to the
> same location, I get this message:
> System.Data.SqlClient.SqlError: Cannot open backup device
> '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> denied.). (Microsoft.SqlServer.Smo)
> Does anyone know why this might be or how best to troubleshoot it?
> Regards-
> Eric|||Tibor-
Thank you. Exactly the info I was looking for. One more question. I have the
SQL Server Agent running under the local system account. Do you know if it is
possible to give the local system account permissions on a network share? If
not, I can either backup to a local file and then copy it onto the network
share, or I can change the service the agent runs under. Any recommendations
or best practices?
Regards-
Eric
"Tibor Karaszi" wrote:
> It is the SQL Server service account that need permissions on the share.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> > Hello-
> >
> > I'm trying to back my database up to a network share. Using the same account
> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> > delete files on the network share just fine. When I try to do a backup to the
> > same location, I get this message:
> >
> > System.Data.SqlClient.SqlError: Cannot open backup device
> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> > denied.). (Microsoft.SqlServer.Smo)
> >
> > Does anyone know why this might be or how best to troubleshoot it?
> >
> > Regards-
> > Eric
>
>|||It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
you probably want to talk to some Windows experts on this. I think you can elaborate with the
machine account and get LocalSystem to work, but I've never done it. I always use a domain account
when I need for my SQL Servers to reach outside the box.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> Tibor-
> Thank you. Exactly the info I was looking for. One more question. I have the
> SQL Server Agent running under the local system account. Do you know if it is
> possible to give the local system account permissions on a network share? If
> not, I can either backup to a local file and then copy it onto the network
> share, or I can change the service the agent runs under. Any recommendations
> or best practices?
> Regards-
> Eric
> "Tibor Karaszi" wrote:
>> It is the SQL Server service account that need permissions on the share.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
>> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
>> > Hello-
>> >
>> > I'm trying to back my database up to a network share. Using the same account
>> > that I'm logged into SQL Server Management Studio with, I'm able to add and
>> > delete files on the network share just fine. When I try to do a backup to the
>> > same location, I get this message:
>> >
>> > System.Data.SqlClient.SqlError: Cannot open backup device
>> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
>> > denied.). (Microsoft.SqlServer.Smo)
>> >
>> > Does anyone know why this might be or how best to troubleshoot it?
>> >
>> > Regards-
>> > Eric
>>|||Tibor-
Are you sure there isn't a SQL Server setting that might be preventing me
from backing up to a network share? I set up a domain account for the Sql
Server Agent to run under and it still fails. I set permissions on the
network share to give Everyone full control. I also tried running the TSQL
for the backup via a query (logged in as a domain admin) and I still get the
same access denied error.
Regards-
Eric
"Tibor Karaszi" wrote:
> It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
> you probably want to talk to some Windows experts on this. I think you can elaborate with the
> machine account and get LocalSystem to work, but I've never done it. I always use a domain account
> when I need for my SQL Servers to reach outside the box.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> > Tibor-
> >
> > Thank you. Exactly the info I was looking for. One more question. I have the
> > SQL Server Agent running under the local system account. Do you know if it is
> > possible to give the local system account permissions on a network share? If
> > not, I can either backup to a local file and then copy it onto the network
> > share, or I can change the service the agent runs under. Any recommendations
> > or best practices?
> >
> > Regards-
> > Eric
> >
> > "Tibor Karaszi" wrote:
> >
> >> It is the SQL Server service account that need permissions on the share.
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> >> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> >> > Hello-
> >> >
> >> > I'm trying to back my database up to a network share. Using the same account
> >> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> >> > delete files on the network share just fine. When I try to do a backup to the
> >> > same location, I get this message:
> >> >
> >> > System.Data.SqlClient.SqlError: Cannot open backup device
> >> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> >> > denied.). (Microsoft.SqlServer.Smo)
> >> >
> >> > Does anyone know why this might be or how best to troubleshoot it?
> >> >
> >> > Regards-
> >> > Eric
> >>
> >>
> >>
>
>|||I think I've got this. It's a strage file share permission problem. I"ll move
this over to a Windows Server discussion group. Thanks for your help.
Regards-
Eric
"Eric Marthinsen" wrote:
> Tibor-
> Are you sure there isn't a SQL Server setting that might be preventing me
> from backing up to a network share? I set up a domain account for the Sql
> Server Agent to run under and it still fails. I set permissions on the
> network share to give Everyone full control. I also tried running the TSQL
> for the backup via a query (logged in as a domain admin) and I still get the
> same access denied error.
> Regards-
> Eric
> "Tibor Karaszi" wrote:
> > It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
> > you probably want to talk to some Windows experts on this. I think you can elaborate with the
> > machine account and get LocalSystem to work, but I've never done it. I always use a domain account
> > when I need for my SQL Servers to reach outside the box.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> > news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
> > > Tibor-
> > >
> > > Thank you. Exactly the info I was looking for. One more question. I have the
> > > SQL Server Agent running under the local system account. Do you know if it is
> > > possible to give the local system account permissions on a network share? If
> > > not, I can either backup to a local file and then copy it onto the network
> > > share, or I can change the service the agent runs under. Any recommendations
> > > or best practices?
> > >
> > > Regards-
> > > Eric
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> It is the SQL Server service account that need permissions on the share.
> > >>
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> > >> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> > >> > Hello-
> > >> >
> > >> > I'm trying to back my database up to a network share. Using the same account
> > >> > that I'm logged into SQL Server Management Studio with, I'm able to add and
> > >> > delete files on the network share just fine. When I try to do a backup to the
> > >> > same location, I get this message:
> > >> >
> > >> > System.Data.SqlClient.SqlError: Cannot open backup device
> > >> > '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> > >> > denied.). (Microsoft.SqlServer.Smo)
> > >> >
> > >> > Does anyone know why this might be or how best to troubleshoot it?
> > >> >
> > >> > Regards-
> > >> > Eric
> > >>
> > >>
> > >>
> >
> >
> >|||On Tue, 28 Nov 2006 16:10:02 -0800, Eric Marthinsen
<EricMarthinsen@.discussions.microsoft.com> wrote:
>Tibor-
>Are you sure there isn't a SQL Server setting that might be preventing me
>from backing up to a network share? I set up a domain account for the Sql
>Server Agent to run under and it still fails. I set permissions on the
>network share to give Everyone full control. I also tried running the TSQL
>for the backup via a query (logged in as a domain admin) and I still get the
>same access denied error.
>Regards-
> Eric
The Agent Service is not performing the backup, it is directing SQL
Server to back itself up. As was said in previous messages, the
account that needs the rights is the account the SQL Server service is
running under.
When I was up to date on this stuff a few years ago the only way to
allow the local system account access to a network resource amounted
to removing all restrictions and security on the target system. I
doubt it is any safer today, in which case you should look into
creating a proper account for the SQL Server service.
Roy Harvey
Beacon Falls, CT

Backing up to a network share from SQL Server 2005

Hello-
I'm trying to back my database up to a network share. Using the same account
that I'm logged into SQL Server Management Studio with, I'm able to add and
delete files on the network share just fine. When I try to do a backup to the
same location, I get this message:
System.Data.SqlClient.SqlError: Cannot open backup device
'\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
denied.). (Microsoft.SqlServer.Smo)
Does anyone know why this might be or how best to troubleshoot it?
Regards-
Eric
Tibor-
Thank you. Exactly the info I was looking for. One more question. I have the
SQL Server Agent running under the local system account. Do you know if it is
possible to give the local system account permissions on a network share? If
not, I can either backup to a local file and then copy it onto the network
share, or I can change the service the agent runs under. Any recommendations
or best practices?
Regards-
Eric
"Tibor Karaszi" wrote:

> It is the SQL Server service account that need permissions on the share.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
>
>
|||Tibor-
Are you sure there isn't a SQL Server setting that might be preventing me
from backing up to a network share? I set up a domain account for the Sql
Server Agent to run under and it still fails. I set permissions on the
network share to give Everyone full control. I also tried running the TSQL
for the backup via a query (logged in as a domain admin) and I still get the
same access denied error.
Regards-
Eric
"Tibor Karaszi" wrote:

> It isn't Agent, it is *SQL Servers* service account. You might be able to do with LocalSystem, but
> you probably want to talk to some Windows experts on this. I think you can elaborate with the
> machine account and get LocalSystem to work, but I've never done it. I always use a domain account
> when I need for my SQL Servers to reach outside the box.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in message
> news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
>
>
|||I think I've got this. It's a strage file share permission problem. I"ll move
this over to a Windows Server discussion group. Thanks for your help.
Regards-
Eric
"Eric Marthinsen" wrote:
[vbcol=seagreen]
> Tibor-
> Are you sure there isn't a SQL Server setting that might be preventing me
> from backing up to a network share? I set up a domain account for the Sql
> Server Agent to run under and it still fails. I set permissions on the
> network share to give Everyone full control. I also tried running the TSQL
> for the backup via a query (logged in as a domain admin) and I still get the
> same access denied error.
> Regards-
> Eric
> "Tibor Karaszi" wrote:
|||On Tue, 28 Nov 2006 16:10:02 -0800, Eric Marthinsen
<EricMarthinsen@.discussions.microsoft.com> wrote:

>Tibor-
>Are you sure there isn't a SQL Server setting that might be preventing me
>from backing up to a network share? I set up a domain account for the Sql
>Server Agent to run under and it still fails. I set permissions on the
>network share to give Everyone full control. I also tried running the TSQL
>for the backup via a query (logged in as a domain admin) and I still get the
>same access denied error.
>Regards-
> Eric
The Agent Service is not performing the backup, it is directing SQL
Server to back itself up. As was said in previous messages, the
account that needs the rights is the account the SQL Server service is
running under.
When I was up to date on this stuff a few years ago the only way to
allow the local system account access to a network resource amounted
to removing all restrictions and security on the target system. I
doubt it is any safer today, in which case you should look into
creating a proper account for the SQL Server service.
Roy Harvey
Beacon Falls, CT

Backing up to a network share from SQL Server 2005

Hello-
I'm trying to back my database up to a network share. Using the same account
that I'm logged into SQL Server Management Studio with, I'm able to add and
delete files on the network share just fine. When I try to do a backup to th
e
same location, I get this message:
System.Data.SqlClient.SqlError: Cannot open backup device
'\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
denied.). (Microsoft.SqlServer.Smo)
Does anyone know why this might be or how best to troubleshoot it?
Regards-
EricIt is the SQL Server service account that need permissions on the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in messag
e
news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
> Hello-
> I'm trying to back my database up to a network share. Using the same accou
nt
> that I'm logged into SQL Server Management Studio with, I'm able to add an
d
> delete files on the network share just fine. When I try to do a backup to
the
> same location, I get this message:
> System.Data.SqlClient.SqlError: Cannot open backup device
> '\\dc01\Backup\Database\fogbugz.bak'. Operating system error 5(Access is
> denied.). (Microsoft.SqlServer.Smo)
> Does anyone know why this might be or how best to troubleshoot it?
> Regards-
> Eric|||Tibor-
Thank you. Exactly the info I was looking for. One more question. I have the
SQL Server Agent running under the local system account. Do you know if it i
s
possible to give the local system account permissions on a network share? If
not, I can either backup to a local file and then copy it onto the network
share, or I can change the service the agent runs under. Any recommendations
or best practices?
Regards-
Eric
"Tibor Karaszi" wrote:

> It is the SQL Server service account that need permissions on the share.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in mess
age
> news:F0C9CDF7-83B7-4C0F-AE36-06F71B94198E@.microsoft.com...
>
>|||It isn't Agent, it is *SQL Servers* service account. You might be able to d
o with LocalSystem, but
you probably want to talk to some Windows experts on this. I think you can e
laborate with the
machine account and get LocalSystem to work, but I've never done it. I alway
s use a domain account
when I need for my SQL Servers to reach outside the box.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in messag
e
news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...[vbcol=seagreen]
> Tibor-
> Thank you. Exactly the info I was looking for. One more question. I have t
he
> SQL Server Agent running under the local system account. Do you know if it
is
> possible to give the local system account permissions on a network share?
If
> not, I can either backup to a local file and then copy it onto the network
> share, or I can change the service the agent runs under. Any recommendatio
ns
> or best practices?
> Regards-
> Eric
> "Tibor Karaszi" wrote:
>|||Tibor-
Are you sure there isn't a SQL Server setting that might be preventing me
from backing up to a network share? I set up a domain account for the Sql
Server Agent to run under and it still fails. I set permissions on the
network share to give Everyone full control. I also tried running the TSQL
for the backup via a query (logged in as a domain admin) and I still get the
same access denied error.
Regards-
Eric
"Tibor Karaszi" wrote:

> It isn't Agent, it is *SQL Servers* service account. You might be able to
do with LocalSystem, but
> you probably want to talk to some Windows experts on this. I think you can
elaborate with the
> machine account and get LocalSystem to work, but I've never done it. I alw
ays use a domain account
> when I need for my SQL Servers to reach outside the box.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eric Marthinsen" <EricMarthinsen@.discussions.microsoft.com> wrote in mess
age
> news:5D92B78E-2089-4677-82DD-67C421796A71@.microsoft.com...
>
>|||I think I've got this. It's a strage file share permission problem. I"ll mov
e
this over to a Windows Server discussion group. Thanks for your help.
Regards-
Eric
"Eric Marthinsen" wrote:
[vbcol=seagreen]
> Tibor-
> Are you sure there isn't a SQL Server setting that might be preventing me
> from backing up to a network share? I set up a domain account for the Sql
> Server Agent to run under and it still fails. I set permissions on the
> network share to give Everyone full control. I also tried running the TSQL
> for the backup via a query (logged in as a domain admin) and I still get t
he
> same access denied error.
> Regards-
> Eric
> "Tibor Karaszi" wrote:
>|||On Tue, 28 Nov 2006 16:10:02 -0800, Eric Marthinsen
<EricMarthinsen@.discussions.microsoft.com> wrote:

>Tibor-
>Are you sure there isn't a SQL Server setting that might be preventing me
>from backing up to a network share? I set up a domain account for the Sql
>Server Agent to run under and it still fails. I set permissions on the
>network share to give Everyone full control. I also tried running the TSQL
>for the backup via a query (logged in as a domain admin) and I still get th
e
>same access denied error.
>Regards-
> Eric
The Agent Service is not performing the backup, it is directing SQL
Server to back itself up. As was said in previous messages, the
account that needs the rights is the account the SQL Server service is
running under.
When I was up to date on this stuff a few years ago the only way to
allow the local system account access to a network resource amounted
to removing all restrictions and security on the target system. I
doubt it is any safer today, in which case you should look into
creating a proper account for the SQL Server service.
Roy Harvey
Beacon Falls, CT