Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

Backup SQL 2000 to a network share

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
dpetershagen
Asked:
dpetershagen
  • 7
  • 6
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how do you specify the path to that remote share?
0
 
Chris MangusDatabase AdministratorCommented:
To access a true share you will not only need NTFS permission but you will need share permission.
0
 
dpetershagenAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Chris MangusDatabase AdministratorCommented:
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
 
dpetershagenAuthor Commented:
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
 
imitchieCommented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
dpetershagenAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
dpetershagenAuthor Commented:
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
 
dpetershagenAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
dpetershagenAuthor Commented:
Was that with domain credentials set for both services?
0
 
Chris MangusDatabase AdministratorCommented:
Yes.  We use domain authentication on all 130+ SQL Servers.
0
 
imitchieCommented:
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
 
dpetershagenAuthor Commented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now