Solved

Backup SQL 2000 to a network share

Posted on 2007-11-20
16
484 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
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 142

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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now