Maintenance Planner Question

Dear All,
I am sorry if this is a silly question but is it possible to run a database
maintenance plan to backup a database to a none fixed hard disk, e.g. a
network share. I have tried running the wizard and at the "Setup Backup Disk
Directory", I only see the fixed hard disk drive (ie C-drive).
I am totally confused. Why do I not see the network share drives. I am using
SQL Server 2000 and Windows XP Professional. I do not have admin rights to
the network but have admin rights to the local machine. Is it to do with the
rights of the logged on user?
As a user I see an H-drive but I do not see it when opening up the wizard.
Thanks for any advice offered.
Alastair
Type in the UNC name. Make sure that the SQL Server Service account has permissions on the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote in message
news:4B0F083D-6B6E-4D19-BC1C-2D01DD9F3010@.microsoft.com...
> Dear All,
> I am sorry if this is a silly question but is it possible to run a database
> maintenance plan to backup a database to a none fixed hard disk, e.g. a
> network share. I have tried running the wizard and at the "Setup Backup Disk
> Directory", I only see the fixed hard disk drive (ie C-drive).
> I am totally confused. Why do I not see the network share drives. I am using
> SQL Server 2000 and Windows XP Professional. I do not have admin rights to
> the network but have admin rights to the local machine. Is it to do with the
> rights of the logged on user?
> As a user I see an H-drive but I do not see it when opening up the wizard.
> Thanks for any advice offered.
> Alastair
|||Thanks Tobor for the quick reply.
I am a newcomer to SQL Server and I understand the reference to UNC but I
don't understand "SQL Server Service account has permissions on the share".
Can you please expand upon this or point me to BOL that explains this?
Thanks again and I appreciate your help.
"Tibor Karaszi" wrote:

> Type in the UNC name. Make sure that the SQL Server Service account has permissions on the share.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote in message
> news:4B0F083D-6B6E-4D19-BC1C-2D01DD9F3010@.microsoft.com...
>
>
|||If you login to the machine using the same Windows account as the SQL Server service is using (ask a
Windows/Network person if that doesn't make sense), you should be able to write to the share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote in message
news:54E0F4EC-8BCF-4B33-B022-D4E2B281A315@.microsoft.com...[vbcol=seagreen]
> Thanks Tobor for the quick reply.
> I am a newcomer to SQL Server and I understand the reference to UNC but I
> don't understand "SQL Server Service account has permissions on the share".
> Can you please expand upon this or point me to BOL that explains this?
> Thanks again and I appreciate your help.
> "Tibor Karaszi" wrote:
|||Also see: http://support.microsoft.com/default...b;en-us;555128
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:us1SB417FHA.4076@.tk2msftngp13.phx.gbl...
> If you login to the machine using the same Windows account as the SQL Server service is using (ask
> a Windows/Network person if that doesn't make sense), you should be able to write to the share.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote in message
> news:54E0F4EC-8BCF-4B33-B022-D4E2B281A315@.microsoft.com...
>
|||Tibor,
I understand what you mean and I will check with our network guys because I
don't know how to check what credentials the SQL Service is logging in as.
Thanks again.
Alastair
"Tibor Karaszi" wrote:

> Also see: http://support.microsoft.com/default...b;en-us;555128
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:us1SB417FHA.4076@.tk2msftngp13.phx.gbl...
>
>
|||You can check at the service level or in Enterprise Manager.
Several ways to get to services...
a. Control Panel | Administration Tools | Services
b. Right-click My Computer | Manage, expand Services and Applications and
click Services.
c. Start menu | Administrative Tools | Services.
You need to check these two services: MSSQLSERVER and SQLSERVERAGENT.
Double-click the service.
Click on the log-on tab.
Change/confirm the credentials.
Note: If you have multiple instances of SQL Server installed, EACH instance
has it's own services and therefore has it's own property settings. The
default instance is always "MSSQLSERVER and SQLSERVERAGENT". A named
instance of Joe would be "MSSQL$Joe and SQLAgent$Joe".
In Enterprise Manager, right-click the server object (i.e. (LOCAL)) and
select properties. (Not to be confused with Edit SQL Server Registration
Properties.) For SQL Agent, expand the Management folder, right-click SQL
Server Agent, choose properites.
BTW, the SQL Server Agent needs to be running in order for a database
maintenance plan or a job to execute. And, you cannot use "Local System
Account" because the local system account is not allowed to access the
network. I'm assuming you have a domain and will therefore need a domain
user account for this purpose. Best practices would have you setup a
separate domain user account (i.e. _sql, _joesqlserver, etc.) and apply only
the permissions necessary for SQL Server to do it's work. If you have
multiple instances of SQL Server with different security needs, it would best
to treat each instance or logical group of SQL Servers and apply a separate
user account. For example, if production uses "_sqlserver", you might not
want to use that domain user account for testing as it would give you write
access to production folders, etc. Something to think about.
Hope this helps,
Joe
"Alastair MacFarlane" wrote:
[vbcol=seagreen]
> Tibor,
> I understand what you mean and I will check with our network guys because I
> don't know how to check what credentials the SQL Service is logging in as.
> Thanks again.
> Alastair
> "Tibor Karaszi" wrote:
|||To me, it ooks like SQL Server 2000 is installed on a Windows XP
professional PC. If this is the case, then SQL Server service will be running
as Local System account, and this local system account won't be able to
write to your network drive(H). If you are using a domain account, for
example, something like (Domain\AMacFarlane) to logon to this Windows XP
professional PC and you have Administrator privilages on this PC, try to
change the SQL Server and SQL Server Agent services "Log on as" to your
account (Domain\AMacFarlane) and I believe this will allow you to write to
your network drive(which is mapped to H). You may have to use the UNC name as
mentioned before.
"Joe from WI" wrote:
[vbcol=seagreen]
> You can check at the service level or in Enterprise Manager.
> Several ways to get to services...
> a. Control Panel | Administration Tools | Services
> b. Right-click My Computer | Manage, expand Services and Applications and
> click Services.
> c. Start menu | Administrative Tools | Services.
> You need to check these two services: MSSQLSERVER and SQLSERVERAGENT.
> Double-click the service.
> Click on the log-on tab.
> Change/confirm the credentials.
> Note: If you have multiple instances of SQL Server installed, EACH instance
> has it's own services and therefore has it's own property settings. The
> default instance is always "MSSQLSERVER and SQLSERVERAGENT". A named
> instance of Joe would be "MSSQL$Joe and SQLAgent$Joe".
> In Enterprise Manager, right-click the server object (i.e. (LOCAL)) and
> select properties. (Not to be confused with Edit SQL Server Registration
> Properties.) For SQL Agent, expand the Management folder, right-click SQL
> Server Agent, choose properites.
> BTW, the SQL Server Agent needs to be running in order for a database
> maintenance plan or a job to execute. And, you cannot use "Local System
> Account" because the local system account is not allowed to access the
> network. I'm assuming you have a domain and will therefore need a domain
> user account for this purpose. Best practices would have you setup a
> separate domain user account (i.e. _sql, _joesqlserver, etc.) and apply only
> the permissions necessary for SQL Server to do it's work. If you have
> multiple instances of SQL Server with different security needs, it would best
> to treat each instance or logical group of SQL Servers and apply a separate
> user account. For example, if production uses "_sqlserver", you might not
> want to use that domain user account for testing as it would give you write
> access to production folders, etc. Something to think about.
> Hope this helps,
> Joe
> "Alastair MacFarlane" wrote:
|||> To me, it ooks like SQL Server 2000 is installed on a Windows XP
> professional PC. If this is the case, then SQL Server service will be running
> as Local System account,
The OS that SQL Server is installed on does not affect the service account chosen. The SQL Server
install program gives you the same options to select service account regardless of the OS you use.

> If you are using a domain account, for
> example, something like (Domain\AMacFarlane) to logon to this Windows XP
> professional PC and you have Administrator privilages on this PC, try to
> change the SQL Server and SQL Server Agent services "Log on as" to your
> account (Domain\AMacFarlane) and I believe this will allow you to write to
> your network drive(which is mapped to H).
Possibly, but only if you first login interactively using that account and map the drive. Mapped
drives are per used in Windows. Hence the recommendation to use UNC naming.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RV" <RV@.discussions.microsoft.com> wrote in message
news:D0B7C6B9-9AB9-4267-988D-6D79626ECE4C@.microsoft.com...[vbcol=seagreen]
> To me, it ooks like SQL Server 2000 is installed on a Windows XP
> professional PC. If this is the case, then SQL Server service will be running
> as Local System account, and this local system account won't be able to
> write to your network drive(H). If you are using a domain account, for
> example, something like (Domain\AMacFarlane) to logon to this Windows XP
> professional PC and you have Administrator privilages on this PC, try to
> change the SQL Server and SQL Server Agent services "Log on as" to your
> account (Domain\AMacFarlane) and I believe this will allow you to write to
> your network drive(which is mapped to H). You may have to use the UNC name as
> mentioned before.
> "Joe from WI" wrote: