February 23, 2009 at 6:18 am
I trying to backup my database onto a network drive but iget this error
Cannot open backup device '\\OBBEAVER\Rev1\full\CHAP.BAK'. Operating system error 5(Access is denied.).
I checkede all the permissions on the remote computer it has full access for my sql server service account, also tried pinging network computer it looks gud. what else i need check??
February 23, 2009 at 6:34 am
Recheck permissions, both to the share and NTFS. The error message is unambiguous - it says access denied. Are you sure permissions have been granted to the right account (ie the one under which SQl Server is running?)
Mike
February 23, 2009 at 7:33 am
Backing up across the network's not recommended for several reasons. Firstly the network speed may make your backups take longer. If there's a network glitch, the backup will fail and the resulting file will be useless. Also there's the security issues.
Can you backup locally then copy the file?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2009 at 6:42 am
What version of SQL Server are you using?
February 24, 2009 at 6:47 am
There are two service accounts associated with SQL Server. The SQL Server account and the SQL Server Agent Account. These may or may not be the same. I believe that the backup runs under the SQL Server Agent and you need to check the account and permissions that the SQL Server Agent is running under.
--
JimFive
February 24, 2009 at 7:05 am
I'll add my two cents worth. If you need to store your backups on a network share, you should backup your database to a local file first and then move it to the network share.
As Gail stated, if there is a network issue, the backup will fail, and if there is congestion on the network during the backup, it will slow down the backup as well.
February 24, 2009 at 9:10 am
I would like to mention to those who are recommending a local backup first that not everyone has enough drive space on the server to hold their complete database twice. Also, in a large network environment it is entirely possible that the database files themselves are on a SAN or other network storage device making the concerns about network latency and uptime potentially moot.
There are plenty of good reasons to want to do a backup to a network device and it seems poor policy to backup a machine to itself. Even using separate drives in the same box leaves a single point of failure at the drive controller or the motherboard.
--
JimFive
February 24, 2009 at 9:27 am
James Goodwin (2/24/2009)
I would like to mention to those who are recommending a local backup first that not everyone has enough drive space on the server to hold their complete database twice.
Compressed backups (2008 or 3rd party tool)?
There are good reasons to have space available. Restoring is one of those reasons. If you're in a crunch and need to restore the backup, you don't want added latency of the network and added uncertainty in what's already a stressful time. Also, backup windows are shrinking in many systems and it's hard enough to backup big databases anyway, without involving a slower network.
The last DB I worked with was 1.2 TB. We ensured that we had enough space to keep 1 week of backups (weekly full, daily diff, tran logs 15 min) on the SAN so that if we had to restore we could do so fast.
Full backups took 3 hours to a SAN drive. We tried once over the network and cancelled the backup after 7.
Also, in a large network environment it is entirely possible that the database files themselves are on a SAN.
Fibre networks (to a san) tend to be faster and less congested than the rest of the LAN. At least, if the storage engineers are doing their job that is.
Most Fibre networks run at 2-4Gb/s or faster. I personally haven't seen a LAN faster than 1Gb/s
There are plenty of good reasons to want to do a backup to a network device and it seems poor policy to backup a machine to itself. Even using separate drives in the same box leaves a single point of failure at the drive controller or the motherboard.
I never suggested only having the backups local. That is stupid.
Backup local, copy elsewhere, archive for as long as necessary depending on data retention laws and business requirements
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2009 at 9:31 am
James Goodwin (2/24/2009)
I would like to mention to those who are recommending a local backup first that not everyone has enough drive space on the server to hold their complete database twice. Also, in a large network environment it is entirely possible that the database files themselves are on a SAN or other network storage device making the concerns about network latency and uptime potentially moot.There are plenty of good reasons to want to do a backup to a network device and it seems poor policy to backup a machine to itself. Even using separate drives in the same box leaves a single point of failure at the drive controller or the motherboard.
--
JimFive
I've had many backups die due to network issues. If your backups are critical enough for your business, you should have spec'd enough space on the server (local disks, SAN, NAS) that you can backup your databases to local storage, and then move the files to a separate network location if that is what is needed. You should have enough space for at least one or two days of backups local to the server.
February 24, 2009 at 1:48 pm
One way I've found to test the permissions issue is to allow ' Everyone' full control of the networked backup folder. If your backup runs OK, you have a permissions issue on the folder. Be sure to remove 'Everyone' from the folder permissions when you're done testing.
February 25, 2009 at 5:25 am
Good one JimFive!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply