Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Backup SQL 2000 to a network share

Posted on 2007-11-20
16
Medium Priority
?
503 Views
Last Modified: 2011-06-24
I am able to backup a SQL 2000 database to a network share as long as the network share resides on the C:\ drive of the computer I am writing to.  If the network share is created on an additional disk (not the c drive), I am unable to run the Back up.  What is it about the c:\ drive that makes it different from let's say the e:\ drive?  Does it have something to do with the MBR or is it more complicated than that?
0
Comment
Question by:dpetershagen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20322828
how do you specify the path to that remote share?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20322908
To access a true share you will not only need NTFS permission but you will need share permission.
0
 

Author Comment

by:dpetershagen
ID: 20322998
I created a share on Server 2's C:\ called SQLBU.  From Server 1 (the SQL server) in Enterprise Mgr, I created a BU Device \\Server2\SQLBU\SQL.bak.  This works successfully.  But if I try to do the same on Server 2's E:\ drive (DISK 1), Enterprise Manager is unable to confirm that the backup device is online or exists.  It also fails when attempting to back up.  I'm still using a UNC when setting up the backup device.  Very strange, so I figured that maybe it had something to do with the MBR that exists on the C:\ drive.  I had a similar problem once when setting up the quorum for a cluster.  Does that clear it up at all?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20323088
Does the account the SQLServer service uses have appropriate permission to the share?  Normally, you'd be using a domain account for your SQL service in your scenario.
0
 

Author Comment

by:dpetershagen
ID: 20323199
The SQL server starts with the System account.  The System account on the second computer has been granted full access to the share.  However, is it the system that is in control of the backup and need access to the share or the person who has created the back up job?  Either way, I have both myself and the system with full access.  Just for the record, the permissions are set for EVERYONE full access.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20323232
SQL Server cannot read mapped drives. In your backup and restore commands always refer to the network drive or network share using UNC path. UNC path has the following format: \\MachineName\ShareName or \\MachineName\DriveLetter$\Path

Here is an example to backup the pubs database to a share called 'AllBackups' on a remote machine named 'BackupServer':

BACKUP DATABASE Pubs TO DISK='\\BackupServer\AllBackups\Pubs.BAK'

To backup pubs database to a the admin share 'D$' on a remote server named 'BackupServer':

BACKUP DATABASE Pubs TO DISK='\\BackupServer\D$\MSSQL7\BACKUP\Pubs.BAK'

For network backups and restores to work, make sure your SQL Server and SQL Agent services are NOT running under system account. These services must run using a domain account and this domain account must have read and write permissions on the network share or drive.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20323264
Those are not the same System accounts.  Those will each be local accounts on each respective server.  The job will attempt to connect to the network resource using the same account your SQL Agent uses.

My suggestion is to change your SQL Server Agent and Server to start up with a domain account.  Then, give that account access to the remote share.
0
 

Author Comment

by:dpetershagen
ID: 20323291
Yep, I'm running backups via UNC not mapped drives.  Good tip on the services account.  I am currently running under system and have not yet tried changing to domain credentials.  However, running with system account and using the UNC works fine if I'm writing to the C:\ of computer two.  It fails whenever I try and create a backup to the E:\ drive of computer two.  That's the delima.  So what is the difference between Disk 0 and Disk 1?  Possibly the MBR record on Disk 0???
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20323348
So, you're saying \\server\c$ works while \\server\e$ doesn't?

Are you sure that E:\ on the second server is an actual local physical disk?

0
 

Author Comment

by:dpetershagen
ID: 20323351
I am in an enterprise environment and can not stop the SQL service in order to change the service log in account.  I will change the SQL agent account and see how that affects things.
0
 

Author Comment

by:dpetershagen
ID: 20323397
cmanqus, that is correct c works and e does not.  I've tried E (Disk 1) in two different configurations neither of which work.  1) New Physical Disk and 2) Extended partition and then set up a logical drive with an MBR.

I've also tried writing to the E drive of another server with no luck.

But in another domain environment I have had success writing to the C drive of another server.  This all got me thinking that it must have something to do with the MBR or something that is exclusive to the c:\ drive.  

Has anyone else had success writing to a second physical disk?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20323453
FWIW, I just did a test on one of my systems and it wrote a backup to Disk 0, 1, and 2 of a remote server.

0
 

Author Comment

by:dpetershagen
ID: 20323481
Was that with domain credentials set for both services?
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 20323523
Yes.  We use domain authentication on all 130+ SQL Servers.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20323545
re >>
I am in an enterprise environment and can not stop the SQL service in order to change the service log in account.  I will change the SQL agent account and see how that affects things.

to test the SQL agent account, you can schedule the backup job.
running it from query analyzer/enterprise manager uses the normal SQL service
0
 

Author Closing Comment

by:dpetershagen
ID: 31597116
Thank you cmangus.  I'll go with this and test it once I can switch the service credentials over.  Sounds like the only thing I'm missing thus far.  Thanks for testing it out!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question