Backup to Network Drive: network

Showing posts with label network. Show all posts

Tuesday, March 27, 2012

Basic Mirroring Setup Help

After going through the Enterprise Manage mirroring setup, I get the error message ...

The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

I have three instances of SQL Server 2005 Enterprise Edtion
MSSQLSERVER (primary) endpoint 5022
MSSQLSERVER (partner) endpoint 5023
MSSQLSERVER (witness) endpoint 5024

I have enabled TCP\IP and named pipes on all three protocols.
In surface configuration I have enabled everything.
Each instance has the -T1400 flag set.
Each instance is running under mixed security.
Each sql Server and sql agent instance runs exactly in the same domain account.
The machine has been rebooted.
The following tests all pass

telnet MYMACH.mynet.net 5022
telnet MYMACH.mynet.net 5023
telnet MYMACH.mynet.net 5024

This problem happens both with Enterprise Edition and Developer Edition.

Any ideas?

AIM

Hi,

Thank you for providing informtaion into the problem that occurred. It is very difficult to diagnose some of these problems with so little information.

My first guess is security, but lets start from the top.

I assume that you have all three instances on the same machine, correct? That should work just fine. And you have setup the endpoints to listen on the different ports, which is correct. Now you said that you can telnet to the different ports, I'm assuming that you did it from the same machine. If you can try the telnet test again from a different machine that would be good, but not neccessary. Should be fine.

If this is the recent SP1 CTP build, http://www.microsoft.com/sql/ctp_sp1.mspx, then you do not need the 1400 trace flag (it is just a noop now). Should be fine.

Now the account that the instances are running under? What type of account is it? Can we make it a Sys Admin account on all three instances? Look in the errorlog for the instances. If you see anything along the lines of "denied access" in there?

Also, one last thing; you state that the error message is:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

If you want the principal to listen on the 5022 port, then you should have gotten an error saying "... MYMACH.mynet.net:5023..." Can you go back and check your setup?

Thanks,

Mark

|||

O.K.

FIRST

All three instances are running under a Windows Account that is an Adminstrator on the machine. This has always been this way.

SECOND

I said:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

I meant to say:

"The server network address "TCP://MYMACH.mynet.net:5023" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

O.K. WHAT NEXT SHOULD I TRY?

THANKS.

AIMDBA

|||

Can you verify that the Windows account that the instances are running as are "SQL Server System Administrators?" Thanks.

If you could attach the error logs during the time that you tried to setup database mirroring, that would help a lot. Grab the error logs from both the principal and mirror instance. By default these are located at:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

Get the "errorlog" that contains the time at which you issued the start mirroring command.

At this point, I see two approaches.

1. posting the errorlogs so that I can look at them.

2. trying to setup mirroring just using t-sql. There are some examples in BOL.

Let me know how things are going.

Thanks,

Mark

|||

I'm getting the same error here. Below is my sql server error (machines are on different IP's) and below this is my error log.

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

Alter failed for Database 'UOP_GSO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2040.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The server network address "TCP://BOGEYWRKSTATION.svc.nt.edu:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

2006-04-12 16:48:01.75 spid51 Using 'xpstar90.dll' version '2005.90.2040' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2006-04-12 16:48:02.55 spid12s The Service Broker protocol transport is disabled or not configured.
2006-04-12 16:48:02.57 spid12s Server is listening on [ 'any' <ipv4> 5022].
2006-04-12 16:48:02.57 spid12s The Database Mirroring protocol transport is now listening for connections.
2006-04-12 16:48:02.77 spid12s Service Broker manager has started.
2006-04-12 16:48:04.49 spid5s Recovery is complete. This is an informational message only. No user action is required.

|||

First thing to do is to go to the "Troubleshooting Database Mirroring Setup" topic in BOL. It was designed to solve this first basic question of "why do I get error 1418?"

The snippette of the errorlog that you posted indicates that the database mirroring endpoint was started at one point, but that doesn't tell me much.

If you could post the part of the errorlog around the time you got the 1418 error, that would tell me much more. It is generally one of two things.

1. Network error where we tried to open the port and for some reason the network said we failed.

2. Security problem where we connected to the port but for some reason, our security credentials were not correct.

Thanks,

Mark

|||

Got it up and running. Had nothing to do with the specific error that I was receiving but I had not restored the failover correctly.

thanks

|||

Bogey1,

What did method did you use, (what did you do) to do the correct failover restore?

AIMDBA

|||Two seperate machines on the network, but still in the same domain. Running domain accounts for sql server. I did a backup of my primary and applied that to my mirror, but I kept getting the error my mirror is not in sync. So I just restored my backup to my primary and started the mirroring. Works quite well.|||

So, to initialize the mirror, you must do a full backup of the database on the princpal, restore the full backup to the mirror with NO_RECOVERY, and then usually (there are some cases where you do not have to do another backup or restore, but they are not normal operating scenarios) take a log backup and restore it to the mirror with NO_RECOVERY. You must also restore any other log backups that are taken on the principal to the mirror.

Thanks,

Mark

|||Rather that starting an entirely new thread, I figured I would post my question here since my issues are stemming from the next point of the mirror creation process.

Basically I take my full principal backup and then run the restore with no recovery to the mirror, once the restore is underway and then complete, according to my sql logs, I have the DBname (Restoring) indicator in management studio. Low and behold this message can be there for countless hours and in this scenario the database is unalvailable for use. Any ideas on what could be causing this?

Thanks
|||

This is correct behavior. Doing any type of restore with NO_RECOVERY leaves the database in an unrecovered state.

Continue with setting up database mirroring.

Thanks,

Mark

|||

I'm facing the same issue with error 1418. I have 3 VM servers ( hosted in the same host machine) that host each an instance of SQL server. I'm using a domain account with admin right on each server to run SQL server and my database mirroring. I have granted System admin right to that account and full right on all sql folder. Telnet test is successfull. after the mirroring failed, there is no failure audit in the security audit log. Everything seems to be setup properly. I have added the service account in all group created by SQL Server during the installation. But I'm still receiving this error.

In another network, where the servers are not VM Ware server but real physical machine I do not have any problem The mirroring is working fine. But with the VMWare server , I have the problem. Is this problem linked to the virtual machine architecture? Why I don't have the problem with physical machine?

Basic Mirroring Setup Help

After going through the Enterprise Manage mirroring setup, I get the error message ...

The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

I have three instances of SQL Server 2005 Enterprise Edtion
MSSQLSERVER (primary) endpoint 5022
MSSQLSERVER (partner) endpoint 5023
MSSQLSERVER (witness) endpoint 5024

telnet MYMACH.mynet.net 5022
telnet MYMACH.mynet.net 5023
telnet MYMACH.mynet.net 5024

This problem happens both with Enterprise Edition and Developer Edition.

Any ideas?

AIM

Hi,

Thank you for providing informtaion into the problem that occurred. It is very difficult to diagnose some of these problems with so little information.

My first guess is security, but lets start from the top.

If this is the recent SP1 CTP build, http://www.microsoft.com/sql/ctp_sp1.mspx, then you do not need the 1400 trace flag (it is just a noop now). Should be fine.

Also, one last thing; you state that the error message is:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

If you want the principal to listen on the 5022 port, then you should have gotten an error saying "... MYMACH.mynet.net:5023..." Can you go back and check your setup?

Thanks,

Mark

|||

O.K.

FIRST

All three instances are running under a Windows Account that is an Adminstrator on the machine. This has always been this way.

SECOND

I said:

"The server network address "TCP://MYMACH.mynet.net:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

I meant to say:

"The server network address "TCP://MYMACH.mynet.net:5023" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

O.K. WHAT NEXT SHOULD I TRY?

THANKS.

AIMDBA

|||

Can you verify that the Windows account that the instances are running as are "SQL Server System Administrators?" Thanks.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

Get the "errorlog" that contains the time at which you issued the start mirroring command.

At this point, I see two approaches.

1. posting the errorlogs so that I can look at them.

2. trying to setup mirroring just using t-sql. There are some examples in BOL.

Let me know how things are going.

Thanks,

Mark

|||

I'm getting the same error here. Below is my sql server error (machines are on different IP's) and below this is my error log.

An error occurred while starting mirroring.

ADDITIONAL INFORMATION:

Alter failed for Database 'UOP_GSO'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

|||

First thing to do is to go to the "Troubleshooting Database Mirroring Setup" topic in BOL. It was designed to solve this first basic question of "why do I get error 1418?"

The snippette of the errorlog that you posted indicates that the database mirroring endpoint was started at one point, but that doesn't tell me much.

If you could post the part of the errorlog around the time you got the 1418 error, that would tell me much more. It is generally one of two things.

1. Network error where we tried to open the port and for some reason the network said we failed.

2. Security problem where we connected to the port but for some reason, our security credentials were not correct.

Thanks,

Mark

|||

Got it up and running. Had nothing to do with the specific error that I was receiving but I had not restored the failover correctly.

thanks

|||

Bogey1,

What did method did you use, (what did you do) to do the correct failover restore?

AIMDBA

Thanks,

Mark

|||Rather that starting an entirely new thread, I figured I would post my question here since my issues are stemming from the next point of the mirror creation process.

Thanks
|||

This is correct behavior. Doing any type of restore with NO_RECOVERY leaves the database in an unrecovered state.

Continue with setting up database mirroring.

Thanks,

Mark

|||

Monday, March 19, 2012

Bad replication performance in SQL2k5 on links with high latency

I tested replication performance on slow network channels, for SQL2k and
SQL2k5. And quite disapplointed by SQL2k5 results...
Network channels were simulated, ping time 200 ms between distributor and
subscriber, unlimited bandwidth.
I tested how many transactions replication can deliver to subsciber. All
transactions are nearby same, they represent typical transactions in my
system.
Publisher, distributor and subsciber are all located on different computers.
In order to check capabilities of hardware, on each of the computers I run
tests to check speed of how many transactions per second it may insert into
database with turned on replication. For each computer, result was ~112
transactions per second, both for SQL2k and SQL2k5.
Next I started to measure replication delivery rate, how many transactions
per second distributor will be able to deliver to subscriber.
In SQL2k, replication in such condition was able to deliver 9 transactions
per second. The best result was obtained with 1 sec polling interval for log
reader and distribution agent, 64kb packet size for distribution agent. No
more differences from default profile.
In case if turn off network delay, rate of inserting transactions on
publisher become same as rate of delivering - replication start to deliver
~112 transactions per second.
For SQL2k5, results were around 5 transactions per second. The best result
was obtained on 1 sec polling interval for log reader and distribution agent,
64kb packet size for distribution agent, 3 SubscriptionStreams. No more
differences from default profile. If turn off network delays, it again
deliver at same rate as it inserting on publisher, about 112 transactions per
second.
Network delay simulator was tested several times to check is it really
simulate network delays as expected. All tests showed correct work of network
delay simulator. For simulating network delays, was used Shundra Cloud.
The question is - Is the replication in SQL2k5 really lost performance in
comparison with SQL2k? Is anything can be done to improve replication
delivery rate in SQL2k5?
the distribution agents in SQL 2005 has autosense network link technology
built into them. They will close down when they detect the link going down
and start up again when it is reconnected. I suspect you are bumping into
this.
I would open a support incident with MS to see if you can disable this
behavior or troubleshoot it further.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>I tested replication performance on slow network channels, for SQL2k and
> SQL2k5. And quite disapplointed by SQL2k5 results...
> Network channels were simulated, ping time 200 ms between distributor and
> subscriber, unlimited bandwidth.
> I tested how many transactions replication can deliver to subsciber. All
> transactions are nearby same, they represent typical transactions in my
> system.
> Publisher, distributor and subsciber are all located on different
> computers.
> In order to check capabilities of hardware, on each of the computers I run
> tests to check speed of how many transactions per second it may insert
> into
> database with turned on replication. For each computer, result was ~112
> transactions per second, both for SQL2k and SQL2k5.
> Next I started to measure replication delivery rate, how many transactions
> per second distributor will be able to deliver to subscriber.
> In SQL2k, replication in such condition was able to deliver 9 transactions
> per second. The best result was obtained with 1 sec polling interval for
> log
> reader and distribution agent, 64kb packet size for distribution agent. No
> more differences from default profile.
> In case if turn off network delay, rate of inserting transactions on
> publisher become same as rate of delivering - replication start to deliver
> ~112 transactions per second.
> For SQL2k5, results were around 5 transactions per second. The best result
> was obtained on 1 sec polling interval for log reader and distribution
> agent,
> 64kb packet size for distribution agent, 3 SubscriptionStreams. No more
> differences from default profile. If turn off network delays, it again
> deliver at same rate as it inserting on publisher, about 112 transactions
> per
> second.
> Network delay simulator was tested several times to check is it really
> simulate network delays as expected. All tests showed correct work of
> network
> delay simulator. For simulating network delays, was used Shundra Cloud.
> The question is - Is the replication in SQL2k5 really lost performance in
> comparison with SQL2k? Is anything can be done to improve replication
> delivery rate in SQL2k5?
|||But network, during the tests, is not goes down - it is always work, at
constant ping time.
"Hilary Cotter" wrote:

> the distribution agents in SQL 2005 has autosense network link technology
> built into them. They will close down when they detect the link going down
> and start up again when it is reconnected. I suspect you are bumping into
> this.
> I would open a support incident with MS to see if you can disable this
> behavior or troubleshoot it further.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>
>
|||I would open a support incident with Microsoft on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D9A495E2-6AD2-480E-B78F-A999A3781FC6@.microsoft.com...[vbcol=seagreen]
> But network, during the tests, is not goes down - it is always work, at
> constant ping time.
> "Hilary Cotter" wrote:

Thursday, March 8, 2012

Backups to network share fail

I am having an odd failure when backing up my SQL 2000 SP3 server to a
network share. I receive the following error:
BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
failed to create. Operating system error = 5(Access is denied.).
However, the following T-SQL command does run and create the appropriate
file in the same network folder:
EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
Both the SQL Server and SQL Server Agent is running under a domain user
account which has the required permissions to this share. Other SQL Servers
(SP3 and SP4) which also run under the same domain user account are backing
up to the same share without any errors.
Any insight into resolving this issue would be appreciated.HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>I am having an odd failure when backing up my SQL 2000 SP3 server to a
> network share. I receive the following error:
> BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> failed to create. Operating system error = 5(Access is denied.).
> However, the following T-SQL command does run and create the appropriate
> file in the same network folder:
> EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> Both the SQL Server and SQL Server Agent is running under a domain user
> account which has the required permissions to this share. Other SQL
> Servers
> (SP3 and SP4) which also run under the same domain user account are
> backing
> up to the same share without any errors.
> Any insight into resolving this issue would be appreciated.
>|||The maintenance plan is properly configured to backup to the UNC name - in
the exact same manner as all the other servers that are working properly.
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > network share. I receive the following error:
> >
> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > failed to create. Operating system error = 5(Access is denied.).
> >
> > However, the following T-SQL command does run and create the appropriate
> > file in the same network folder:
> >
> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > Both the SQL Server and SQL Server Agent is running under a domain user
> > account which has the required permissions to this share. Other SQL
> > Servers
> > (SP3 and SP4) which also run under the same domain user account are
> > backing
> > up to the same share without any errors.
> >
> > Any insight into resolving this issue would be appreciated.
> >
>|||I have found in those cases that it is usually either a name resolution
issue or a permissions issue.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:53C105CD-2656-4A02-A708-4E6C43F4077F@.microsoft.com...
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
>> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
>> > network share. I receive the following error:
>> >
>> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
>> > failed to create. Operating system error = 5(Access is denied.).
>> >
>> > However, the following T-SQL command does run and create the
>> > appropriate
>> > file in the same network folder:
>> >
>> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
>> >
>> > Both the SQL Server and SQL Server Agent is running under a domain user
>> > account which has the required permissions to this share. Other SQL
>> > Servers
>> > (SP3 and SP4) which also run under the same domain user account are
>> > backing
>> > up to the same share without any errors.
>> >
>> > Any insight into resolving this issue would be appreciated.
>> >
>>|||Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
\\server\share > \\server\share\test.txt' "
On Apr 10, 4:16 pm, Richard Cote
<RichardC...@.discussions.microsoft.com> wrote:
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
>
> "Geoff N. Hiten" wrote:
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> >http://support.microsoft.com/?kbid=555128
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > network share. I receive the following error:
> > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > failed to create. Operating system error = 5(Access is denied.).
> > > However, the following T-SQL command does run and create the appropriate
> > > file in the same network folder:
> > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > account which has the required permissions to this share. Other SQL
> > > Servers
> > > (SP3 and SP4) which also run under the same domain user account are
> > > backing
> > > up to the same share without any errors.
> > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> - Show quoted text -|||The xp_cmdshell command works for any sysadmin user and the SQL Server and
SQL Server Agent logins. However, backups still fail to the same network
share.
This problem seems to be isolated to this one server, and is very
perplexing. Turning on auditing on the file share does not reveal any audit
failures. Choosing a different share with full file and share permissions
granted to Everyone also results in the same access denied error.
"schal" wrote:
> Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
> \\server\share > \\server\share\test.txt' "
>
> On Apr 10, 4:16 pm, Richard Cote
> <RichardC...@.discussions.microsoft.com> wrote:
> > The maintenance plan is properly configured to backup to the UNC name - in
> > the exact same manner as all the other servers that are working properly.
> >
> >
> >
> > "Geoff N. Hiten" wrote:
> > > HowTo: Backup to UNC name using Database Maintenance Wizard
> > >http://support.microsoft.com/?kbid=555128
> >
> > > --
> > > Geoff N. Hiten
> > > Senior Database Administrator
> > > Microsoft SQL Server MVP
> >
> > > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> > >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > > network share. I receive the following error:
> >
> > > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > > failed to create. Operating system error = 5(Access is denied.).
> >
> > > > However, the following T-SQL command does run and create the appropriate
> > > > file in the same network folder:
> >
> > > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > > account which has the required permissions to this share. Other SQL
> > > > Servers
> > > > (SP3 and SP4) which also run under the same domain user account are
> > > > backing
> > > > up to the same share without any errors.
> >
> > > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> >
> > - Show quoted text -
>
>

Backups to network share fail

Backups over a network

I am using the following syntax in sql 2000 in order to transfer the backup across the network to a backup machine:

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'

The problem I am experiencing is that when I run this script, I get the response:

Access is denied.
0 file(s) copied

Can anyone tell me what I am doing wrong?Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

Still, don't you think it's a security thing?|||Originally posted by Brett Kaiser
Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

he is backing up locally and then trying to copy it over the network .. Brett

read the post again

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'|||Ok, ive given up and having sql do the move for me. Not exactly sure whats causing the problem, but I have worked around it. I have just written a bat file that copies over a mapped drive that is run thru the schedule tasks interface. Not as clean as I would like, but it works.

Thanks again guys

Friday, February 10, 2012

Backup To Network Share

I would like to backup my databases to a network share (NAS) instead of local disk using Maintenance Plans created by Enterprise Manager. I have successfully used a UNC path to target the destination network share but have not been to figure out how to submit a logon to the network share before the backup is executed.

The SQL Server instance is running in the context of the local system account.

Can I insert a step in the SQL SQL job that is created by the Maintenance Plans that changes the Windows account that the backup runs under? If yes what command syntax would I use in the inserted step or is there another way to accomplish that I'm attempting to do?

thanks
HPTo do this you need the SQL Service to run as a network user account that has pemissions over the share.

That being said it is generally reccomended that you create your backup file locally and then copy it over the network using a DOS batch file that uses copy or some such method.

Backup to network share

This summary is not available. Please click here to view the post.

Backup to Network Resource from a Server Machine Service Account

SQL Server 2000 SP4
Trying to backup to a network resource when SQL uses the machine service
account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
work because of permissions.
Fairly new to DBA stuff so please have patience with me.
How can you do this?
Regards,
Richard.
You would need to have SQL Server using a service account that has permissions on that share.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.
|||I don't think Windows security works like that:
Services Using The System Account
Services that use the system account start in the system context (without
credentials). In Windows NT 3.5 and later, Windows NT services with no
credentials (no domain name, user name, or password) that attempt to connect
to network resources are denied access because they have no credentials and
they are using a null session. For additional information, click the article
number below to view the article in the Microsoft Knowledge Base:
from: http://support.microsoft.com/kb/132679
Essentially the local system account has no credentials so cannot
athenticate to anything on the network.
Is there anyway round this?
"Tibor Karaszi" wrote:

> You would need to have SQL Server using a service account that has permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
>
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.
|||>I don't think Windows security works like that:
I don't see in what way what I said differs from the text you quoted.

> Is there anyway round this?
Not AFAIK...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...[vbcol=seagreen]
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to connect
> to network resources are denied access because they have no credentials and
> they are using a null session. For additional information, click the article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
|||As Tibor pointed out you need an account to run SQL Server Service under
that has the appropriate permissions to that share. A LOCAL account has no
permissions outside that box. So change it to a Domain account with the
appropriate permissions.
Andrew J. Kelly SQL MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...[vbcol=seagreen]
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to
> connect
> to network resources are denied access because they have no credentials
> and
> they are using a null session. For additional information, click the
> article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
|||Hello,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write
access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.

Backup to Network Resource from a Server Machine Service Account

SQL Server 2000 SP4
Trying to backup to a network resource when SQL uses the machine service
account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
'\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
work because of permissions.
Fairly new to DBA stuff so please have patience with me.
How can you do this?
Regards,
Richard.You would need to have SQL Server using a service account that has permissions on that share.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.|||I don't think Windows security works like that:
Services Using The System Account
Services that use the system account start in the system context (without
credentials). In Windows NT 3.5 and later, Windows NT services with no
credentials (no domain name, user name, or password) that attempt to connect
to network resources are denied access because they have no credentials and
they are using a null session. For additional information, click the article
number below to view the article in the Microsoft Knowledge Base:
from: http://support.microsoft.com/kb/132679
Essentially the local system account has no credentials so cannot
athenticate to anything on the network.
Is there anyway round this?
"Tibor Karaszi" wrote:
> You would need to have SQL Server using a service account that has permissions on that share.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> > SQL Server 2000 SP4
> >
> > Trying to backup to a network resource when SQL uses the machine service
> > account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
> > '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
> > work because of permissions.
> >
> > Fairly new to DBA stuff so please have patience with me.
> >
> > How can you do this?
> >
> > Regards,
> > Richard.
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.|||>I don't think Windows security works like that:
I don't see in what way what I said differs from the text you quoted.
> Is there anyway round this?
Not AFAIK...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to connect
> to network resources are denied access because they have no credentials and
> they are using a null session. For additional information, click the article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
>> You would need to have SQL Server using a service account that has permissions on that share.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
>> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
>> > SQL Server 2000 SP4
>> >
>> > Trying to backup to a network resource when SQL uses the machine service
>> > account. Obviously something like EXEC sp_addumpdevice 'disk', 'device_name',
>> > '\\filer_name\share_name\path\file_name.ext' would be used but this doesn't
>> > work because of permissions.
>> >
>> > Fairly new to DBA stuff so please have patience with me.
>> >
>> > How can you do this?
>> >
>> > Regards,
>> > Richard.
>>|||As Tibor pointed out you need an account to run SQL Server Service under
that has the appropriate permissions to that share. A LOCAL account has no
permissions outside that box. So change it to a Domain account with the
appropriate permissions.
--
Andrew J. Kelly SQL MVP
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:C2D36167-F2BE-464E-9404-3A689C9A0A22@.microsoft.com...
>I don't think Windows security works like that:
> Services Using The System Account
> Services that use the system account start in the system context (without
> credentials). In Windows NT 3.5 and later, Windows NT services with no
> credentials (no domain name, user name, or password) that attempt to
> connect
> to network resources are denied access because they have no credentials
> and
> they are using a null session. For additional information, click the
> article
> number below to view the article in the Microsoft Knowledge Base:
> from: http://support.microsoft.com/kb/132679
> Essentially the local system account has no credentials so cannot
> athenticate to anything on the network.
>
> Is there anyway round this?
> "Tibor Karaszi" wrote:
>> You would need to have SQL Server using a service account that has
>> permissions on that share.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
>> news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
>> > SQL Server 2000 SP4
>> >
>> > Trying to backup to a network resource when SQL uses the machine
>> > service
>> > account. Obviously something like EXEC sp_addumpdevice 'disk',
>> > 'device_name',
>> > '\\filer_name\share_name\path\file_name.ext' would be used but this
>> > doesn't
>> > work because of permissions.
>> >
>> > Fairly new to DBA stuff so please have patience with me.
>> >
>> > How can you do this?
>> >
>> > Regards,
>> > Richard.
>>|||Hello,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write
access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
Backup database <dbname> to Disk='\\backup\tmp\networkdatabase.dat' with
init ( With init will overwrite the backup file every time)
Thanks
Hari
"BQ_rmsterling" <richard.sterling@.bluesq.com> wrote in message
news:77F10996-5141-4C32-BE8B-6CF9A973FC0C@.microsoft.com...
> SQL Server 2000 SP4
> Trying to backup to a network resource when SQL uses the machine service
> account. Obviously something like EXEC sp_addumpdevice 'disk',
> 'device_name',
> '\\filer_name\share_name\path\file_name.ext' would be used but this
> doesn't
> work because of permissions.
> Fairly new to DBA stuff so please have patience with me.
> How can you do this?
> Regards,
> Richard.

Backup to Network Resource from a Server Machine Service Account

Backup to network drive using Workgroup/Standard.

I am using SQL Server 2005 Express Edition. I am planning on upgrading to either Workgroup or Standard edition. While working with Express Edition I did find out that this version does not support backup to a network drive.
My question is:

Is it possible to backup directly to a network drive using a Workgroup/ Standard edition of SQL Server rather than backing up to a local drive and then 'copy and paste' to a network drive like the Express Edition.My other question is :
Given a database limit of 50GB, which one of the editions, Workgroup or Standard, will be appropriate for use.Thank you for your time and patience.
Regards
Berly Sam

You cannot backup to a network share with any edition of SQL Server.

SQL Server Backup will only used locally attached drives, or NAS/SAN LUNs. You can either backup locally, and then move the file to a network share (both tasks can be automated with SQL Agent -not included with SQL Express), or Check out the several third party backup tools. (Idera, Red-Gate, to name a couple.)

Workgroup-Standard-Enterprise, which is 'best'. It depends upon the functionality that you require. With a database of that size, you are definitely between the Standard and Enterprise Editions (in my opinion). You may wish to check out the functionality differences at this site.

|||Thats not exactly true, you cannot see the backup media in the SQL Server dialog as it only displays local fixed drives, but you can specify a UNC share within the dialog textbox or within the script. Depending on the situation you should rather consider using a local /SAN / NAS backup first and then copy it to a network share if needed.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

I consider it a gentle ‘challenge’ for me to give more consideration to why I responded as I did. And I appreciate that.

As I contemplate why I thought so, all I can remember is ever since 6.5, I have always taught to backup locally and xcopy to a net share. I can only imagine that was because of the speed issues of what used to be slower networks, as well as the persistence and permissions issues related to mapped drives.

And I now realize that I have not reconsidered or changed that practice in the intervening years.

I’m glad that you challenged me, now I have tested and affirmed that a backup to a UNC works just fine. But I doubt that I will change my practice of Backing up locally and using xcopy to move the backup file to a network resource -the BACKUP won't fail because of any network issues.

Regards,

- Arnie Rowland

backup to network drive

I'm trying to create a job to backup to a network drive. The following SQL does not execute successfully:
USE master
EXEC sp_addumpdevice 'disk', 'Thurs6', '\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
BACKUP LOG Mock8 TO Thurs6 WITH INIT
However, if I change the destination file to one located on the SQL server, it executes fine. I've also tried this using a mapped drive letter, and that doesn't work either. Any thoughts?
Backups are done under the account that sql server runs under and not the
account you are logged on as. So if SQL Server is not running under a domain
account with access to that share it can not see it.
Andrew J. Kelly SQL MVP
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||It could be that the account used by SQL Server doesn't have permissions to
that share. Can you post the exact error messages?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?
|||Hi,
To solve the issue you have start the SQL Server service using an Domain OS
user which got write access to the remote share.
So go to Control Panel -- Admin Tools -- Services -- MSSQL Server sercice--
Double click and select the "Log on" option.
There you give a Valid Domain OS user and password to start the service. Now
stop and start the MSSQL Serevr service.
Note: That domain user should have previlages in the remote share to write
the file as well as prev. to start the SQL server.
After this you try to execute the Backup database command in Query
Analyzer:-
USE master
go
EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
go
BACKUP LOG Mock8 TO Thurs6 WITH INIT
Thanks
Hari
MCDBA
"mike" <mike@.discussions.microsoft.com> wrote in message
news:85586673-2350-4BC0-A64D-4B2664703C92@.microsoft.com...
> I'm trying to create a job to backup to a network drive. The following SQL
does not execute successfully:
> USE master
> EXEC sp_addumpdevice 'disk', 'Thurs6',
'\\servername\DATA\SQL\Backup\Thursday 6 PM.txt'
> BACKUP LOG Mock8 TO Thurs6 WITH INIT
> However, if I change the destination file to one located on the SQL
server, it executes fine. I've also tried this using a mapped drive letter,
and that doesn't work either. Any thoughts?

Backup to Network Drive

We are attempting to set our SQL Server 2000 backups to point to a network
drive. We have created a domain user and set the SQL Server Service and SQL
Server Agent to use this account. We have also set this account as a member
of the Administrators group on the SQL Server itself and added it to the sys
admin group within SQL Server. On the destination server, the service account
has full control on the share that we'd like to write to. Unfortunately, our
backups are failing. The SQL Server Log provides the following message
"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
create. Operating system error = 5(Access is denied.)."
What are we missing here?Marcia,
Did you restart the SQL Server once the drive was mounted on your
server? Have you verified that as the user which SQL Server starts
under on the box you can read and write data to that location? These
are some of the gotcha's. I wish SQL Server allowed you to see network
mount points when you go to Backup Database options through Enterprise
Manager.
Shahryar
MarciaN wrote:
>We are attempting to set our SQL Server 2000 backups to point to a network
>drive. We have created a domain user and set the SQL Server Service and SQL
>Server Agent to use this account. We have also set this account as a member
>of the Administrators group on the SQL Server itself and added it to the sys
>admin group within SQL Server. On the destination server, the service account
>has full control on the share that we'd like to write to. Unfortunately, our
>backups are failing. The SQL Server Log provides the following message
>"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
>create. Operating system error = 5(Access is denied.)."
>What are we missing here?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> We are attempting to set our SQL Server 2000 backups to point to a network
> drive. We have created a domain user and set the SQL Server Service and
> SQL
> Server Agent to use this account. We have also set this account as a
> member
> of the Administrators group on the SQL Server itself and added it to the
> sys
> admin group within SQL Server. On the destination server, the service
> account
> has full control on the share that we'd like to write to. Unfortunately,
> our
> backups are failing. The SQL Server Log provides the following message
> "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> create. Operating system error = 5(Access is denied.)."
> What are we missing here?|||We have followed these procedures and still no luck. Can you give some
specifics related to the access levels that the WIndows Service account
should have?
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> > We are attempting to set our SQL Server 2000 backups to point to a network
> > drive. We have created a domain user and set the SQL Server Service and
> > SQL
> > Server Agent to use this account. We have also set this account as a
> > member
> > of the Administrators group on the SQL Server itself and added it to the
> > sys
> > admin group within SQL Server. On the destination server, the service
> > account
> > has full control on the share that we'd like to write to. Unfortunately,
> > our
> > backups are failing. The SQL Server Log provides the following message
> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> > create. Operating system error = 5(Access is denied.)."
> >
> > What are we missing here?
>
>|||The SQL Server service account requires FULL CONTROL over the share and the
underlying NTFS directory structure.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> We have followed these procedures and still no luck. Can you give some
> specifics related to the access levels that the WIndows Service account
> should have?
>
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> > We are attempting to set our SQL Server 2000 backups to point to a
>> > network
>> > drive. We have created a domain user and set the SQL Server Service and
>> > SQL
>> > Server Agent to use this account. We have also set this account as a
>> > member
>> > of the Administrators group on the SQL Server itself and added it to
>> > the
>> > sys
>> > admin group within SQL Server. On the destination server, the service
>> > account
>> > has full control on the share that we'd like to write to.
>> > Unfortunately,
>> > our
>> > backups are failing. The SQL Server Log provides the following message
>> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> > to
>> > create. Operating system error = 5(Access is denied.)."
>> >
>> > What are we missing here?
>>|||We gave the service account FULL CONTROL over the share. After an
unsuccessful test of that, we added the service account to the local admins
group on the destination server and still no luck. It seems like we have
covered all the bases but still can not make this work. Can you think of
anything else?
"Geoff N. Hiten" wrote:
> The SQL Server service account requires FULL CONTROL over the share and the
> underlying NTFS directory structure.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> > We have followed these procedures and still no luck. Can you give some
> > specifics related to the access levels that the WIndows Service account
> > should have?
> >
> >
> > "Geoff N. Hiten" wrote:
> >
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/?kbid=555128
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> > network
> >> > drive. We have created a domain user and set the SQL Server Service and
> >> > SQL
> >> > Server Agent to use this account. We have also set this account as a
> >> > member
> >> > of the Administrators group on the SQL Server itself and added it to
> >> > the
> >> > sys
> >> > admin group within SQL Server. On the destination server, the service
> >> > account
> >> > has full control on the share that we'd like to write to.
> >> > Unfortunately,
> >> > our
> >> > backups are failing. The SQL Server Log provides the following message
> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> > to
> >> > create. Operating system error = 5(Access is denied.)."
> >> >
> >> > What are we missing here?
> >>
> >>
> >>
>
>|||Verify NTFS file permissions on the destination directory. This is
different from the share security permissions.
Log in to the console of the SQL Server as the service account and try to
access the network share directly. Create, rename, and delete a file.
Run a backup via Query Analyzer (BACKUP DATABASE command) to the share
location.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local
> admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and
>> the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service
>> >> > and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the
>> >> > service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following
>> >> > message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup'
>> >> > failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||I'd start by logging in on the SQL Server machine using the service account and see if I can access
and create files. If so, use xp_cmdshell to see if you can do the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||It's working! We deleted the shared folder and recreated it. Now it works.
Thanks to all for your suggestions.
"Tibor Karaszi" wrote:
> I'd start by logging in on the SQL Server machine using the service account and see if I can access
> and create files. If so, use xp_cmdshell to see if you can do the same.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> > We gave the service account FULL CONTROL over the share. After an
> > unsuccessful test of that, we added the service account to the local admins
> > group on the destination server and still no luck. It seems like we have
> > covered all the bases but still can not make this work. Can you think of
> > anything else?
> >
> > "Geoff N. Hiten" wrote:
> >
> >> The SQL Server service account requires FULL CONTROL over the share and the
> >> underlying NTFS directory structure.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> >> > We have followed these procedures and still no luck. Can you give some
> >> > specifics related to the access levels that the WIndows Service account
> >> > should have?
> >> >
> >> >
> >> > "Geoff N. Hiten" wrote:
> >> >
> >> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> >> http://support.microsoft.com/?kbid=555128
> >> >>
> >> >> --
> >> >> Geoff N. Hiten
> >> >> Senior Database Administrator
> >> >> Microsoft SQL Server MVP
> >> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> >> > network
> >> >> > drive. We have created a domain user and set the SQL Server Service and
> >> >> > SQL
> >> >> > Server Agent to use this account. We have also set this account as a
> >> >> > member
> >> >> > of the Administrators group on the SQL Server itself and added it to
> >> >> > the
> >> >> > sys
> >> >> > admin group within SQL Server. On the destination server, the service
> >> >> > account
> >> >> > has full control on the share that we'd like to write to.
> >> >> > Unfortunately,
> >> >> > our
> >> >> > backups are failing. The SQL Server Log provides the following message
> >> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> >> > to
> >> >> > create. Operating system error = 5(Access is denied.)."
> >> >> >
> >> >> > What are we missing here?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||Windows has two level access control.
You need to give the sql account first the access to the shared
drive/directory and then you need to give access to the
files
1. Access to the SHARE
2. Access to files on that share
1. On the shared directory right click-> sharing->permissions. Check the
SQL account has access rights.
2. same right click->security->permissions. check the SQL account has
rights.
That should do it
rgrds Matti

Backup to Network Drive

I need to be able to backup my databases through EM to a network drive. The problem is EM won't recognize any of the network drives. This worked for me in the past but I am now running sp3a and for some reason cannot backup to my remote servers anymore.
Does anyone know how to fix this issue?You should use a UNC name instead of a drive letter... any way..
just type in the UNC name..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kelly" <mavericc@.wepsoft.com> wrote in message
news:DCE35BB4-9F00-483A-ADB1-97F38DDB3075@.microsoft.com...
> I need to be able to backup my databases through EM to a network drive.
The problem is EM won't recognize any of the network drives. This worked
for me in the past but I am now running sp3a and for some reason cannot
backup to my remote servers anymore.
> Does anyone know how to fix this issue?|||Just for the record, trace flag 1807 is not needed to backup to a network
share. The flag is intended to allow SQL Server to use network attached
storage devices for database files as described in MSKB 304261
<http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql>
.
As Wayne said earlier in this thread, UNCs can be used. If this isn't
working, the likely cause us that the SQL Server service account lacks
permissions to the share. Note that this needs to be a Windows account
rather than Local System.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ted" <anonymous@.discussions.microsoft.com> wrote in message
news:179E57A3-786D-4CF6-B4D0-8DB9D5C987B6@.microsoft.com...
> If the trace didn't work, and it must be set, then it's permission of the
SQLServer and Agent services. I have a domain account I use and it has the
appropriate permission on the network. I use that same account for mail
notification as well. So:
> 1.) Verify the user starting the services has permission to the network
drive.
> 2.) Set the trace flag on
> 3.) Try it. If it works, you don't need to worry about the reboot. It
knows it wrote to the network once and will do it again. (I've never had a
problem).
> Ted-|||Ah yes. I stand corrected. The trace is for NAS. Thanks for the correction Dan.

Tuesday, March 27, 2012

Basic Mirroring Setup Help

Basic Mirroring Setup Help

Monday, March 19, 2012

Bad replication performance in SQL2k5 on links with high latency

Thursday, March 8, 2012

Backups to network share fail

Backups to network share fail

Backups to network share fail

Backups over a network

Friday, February 10, 2012

Backup To Network Share

Backup to network share

Backup to Network Resource from a Server Machine Service Account

Backup to Network Resource from a Server Machine Service Account

Backup to Network Resource from a Server Machine Service Account

Backup to network drive using Workgroup/Standard.

backup to network drive

Backup to Network Drive

Backup to Network Drive

Backup to Network Drive

Blog Archive

About Me