Restore database fails only when source is read from another machine ( server),even wi

Steen,
Thank you, but it fails with the same message when using an UNC like
disk='\\Peter\C\backups\dottietest.bak', instead of disk =
'K:\backups\dottietest.bak'
where \\Peter\C\backups is copied from the address bar of the Windows
Explorer, so its syntax is correct.
If I use an UNC pointing to the local machine, i.e. the one I am executing
it from and on which the target server is, like
disk='\\thisserver\C\Temp\dottietest.bak', then the UNC format works.
What could it be?
PKuhne
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OG1uxTjJFHA.2628@.tk2msftngp13.phx.gbl...
> You can't use a mapped drive, so you'll have to use the UNC path instead -
> that will do the trick....
> Regards
> Steen
> PKuhne wrote:
>The account that's running the backups needs to have permissions to the
share on the other machine. You have to make sure that:
1. You are using a UNC. You can't use mapped drives for directories on
other machines.
2. The account that's running the backup has write permissions on the share
for the other machine.
"PKuhne" <peter@.chsoft.com> wrote in message
news:OLFH4jmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
> Steen,
> Thank you, but it fails with the same message when using an UNC like
> disk='\\Peter\C\backups\dottietest.bak', instead of disk =
> 'K:\backups\dottietest.bak'
> where \\Peter\C\backups is copied from the address bar of the Windows
> Explorer, so its syntax is correct.
> If I use an UNC pointing to the local machine, i.e. the one I am executing
> it from and on which the target server is, like
> disk='\\thisserver\C\Temp\dottietest.bak', then the UNC format works.
> What could it be?
> PKuhne
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OG1uxTjJFHA.2628@.tk2msftngp13.phx.gbl...
instead -[vbcol=seagreen]
>|||Derrick,
1. When I execute this command on a Win2k Prof (drive d like
" restore database restoretest
from disk = 'k:\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
...",
where K: is a Win2k server and d: is on the Win2kProf machine then it works
without UNC.
2. When I reverse this, ie I execute this command on the WIN2k Server (drive
d to restore from the Win2kProf machine like
" restore database restoretest
from disk = '\\win2kprof\d\backups\dottietest.bak'
with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
...",
then I get the original error "... Operating system error = 5(Access is
denied.)."
At this time the Property page's Security tab for the dottietest.bak file on
the winProf machine, looked at from the Win2kServer via WinExplorer, has:
'Everyone' as group name, with all these perimissions checked, but greyed
out:
Full control,Modify, Read & Execute,Read, write, and no other group or user.
The win2k server and win2k prof machines are on the same network (belonging
to the same workgroup, with no domain present)
3. Do you really mean that ".. the account that's running the backup needs
to have permissions to the
share on the other machine." ? I could have gotten the *.bak file that I
try to restore from any server in the world, and that is why I think that
you mean ".. the account that's running the RESTORE needs to have
permissions to the share on the other machine.
If this is so, then all this boils down now to "assigning the permissions".
I wonder how this is done, i.e. what more has to be done beyond the server
having rights to reading and writing from/to the Win2kProf machine (at least
via drag and drop of files through Win Explorer).
It is strange that when I execute this from a batch file:
" copy \\Laptop\Laptop_C\vamdata6\sample\sample
.bak c:\
isql /S . /U vamlogin /P go /i restore_db.sql "
..with this in restore_db.sql :
"restore database sample
from disk = 'c:\sample.bak'
with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
move 'vsm_log' to 'd:\temp\vsmlog.ldf',
stats=10"
.. then the restore works, i.e. I seem to be able to copy the file from the
win2kProf machine, but it fails to execute the "restore" from that same
machine (as shown further up).
Perhaps this can only work on a domain controller system, which I do not
intend to establish.
Any suggestion?
TIA
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> The account that's running the backups needs to have permissions to the
> share on the other machine. You have to make sure that:
> 1. You are using a UNC. You can't use mapped drives for directories on
> other machines.
> 2. The account that's running the backup has write permissions on the
share
> for the other machine.
>
>
> "PKuhne" <peter@.chsoft.com> wrote in message
> news:OLFH4jmJFHA.4012@.TK2MSFTNGP09.phx.gbl...
executing[vbcol=seagreen]
> instead -
'd:\vamdata\sample\vamsystemdata.mdf',[vbcol=seagreen]
"[vbcol=seagreen]
the[vbcol=seagreen]
'k:\backups\dottietest.bak'[vbcol=seagreen]
is[vbcol=seagreen]
>|||Run that copy command from an xp_cmdshell and see if you get the permissions
error. Also, on the other machine set up a specific share for the backup
directory. You can do this my right-clicking on My Computer and going to
Manage. Click on Shared Folders/Shares/Right-click on Shares to Add
Share, then add the backups. Put full permissions to everyone. You will
then map the restore with like this:
\\win2kprof\sharename\backupfile.bak
The mapped drive might work when you are logged in; however, it will not
work when you log off. Remember that mapped drive is for your profile.
"PKuhne" <p.kuhne@.verizon.net> wrote in message
news:#Mpzr$yJFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Derrick,
> 1. When I execute this command on a Win2k Prof (drive d like
> " restore database restoretest
> from disk = 'k:\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> ...",
> where K: is a Win2k server and d: is on the Win2kProf machine then it
works
> without UNC.
> 2. When I reverse this, ie I execute this command on the WIN2k Server
(drive
> d to restore from the Win2kProf machine like
> " restore database restoretest
> from disk = '\\win2kprof\d\backups\dottietest.bak'
> with move 'vam_system_data' to 'd:\vamdata\sample\vamsystemdata.mdf',
> ...",
> then I get the original error "... Operating system error = 5(Access is
> denied.)."
> At this time the Property page's Security tab for the dottietest.bak file
on
> the winProf machine, looked at from the Win2kServer via WinExplorer, has:
> 'Everyone' as group name, with all these perimissions checked, but greyed
> out:
> Full control,Modify, Read & Execute,Read, write, and no other group or
user.
> The win2k server and win2k prof machines are on the same network
(belonging
> to the same workgroup, with no domain present)
> 3. Do you really mean that ".. the account that's running the backup needs
> to have permissions to the
> share on the other machine." ? I could have gotten the *.bak file that I
> try to restore from any server in the world, and that is why I think that
> you mean ".. the account that's running the RESTORE needs to have
> permissions to the share on the other machine.
> If this is so, then all this boils down now to "assigning the
permissions".
> I wonder how this is done, i.e. what more has to be done beyond the server
> having rights to reading and writing from/to the Win2kProf machine (at
least
> via drag and drop of files through Win Explorer).
> It is strange that when I execute this from a batch file:
> " copy \\Laptop\Laptop_C\vamdata6\sample\sample
.bak c:\
> isql /S . /U vamlogin /P go /i restore_db.sql "
> ..with this in restore_db.sql :
> "restore database sample
> from disk = 'c:\sample.bak'
> with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
> move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
> move 'vsm_log' to 'd:\temp\vsmlog.ldf',
> stats=10"
> .. then the restore works, i.e. I seem to be able to copy the file from
the
> win2kProf machine, but it fails to execute the "restore" from that same
> machine (as shown further up).
> Perhaps this can only work on a domain controller system, which I do not
> intend to establish.
> Any suggestion?
> TIA
>
>
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> share
> executing
target[vbcol=seagreen]
> 'd:\vamdata\sample\vamsystemdata.mdf',
> "
"[vbcol=seagreen]
> the
> 'k:\backups\dottietest.bak'
> is
>|||Hi
You also need to make sure that the account that runs the SQL Server Service
(and maybe also the SQL Server Agent service) has access to the shares you
need to access. It's this account that determines the access - not the
account you are logged on with.
Regards
Steen
PKuhne wrote:[vbcol=seagreen]
> Derrick,
> 1. When I execute this command on a Win2k Prof (drive d like
> " restore database restoretest
> from disk = 'k:\backups\dottietest.bak'
> with move 'vam_system_data' to
> 'd:\vamdata\sample\vamsystemdata.mdf', ...",
> where K: is a Win2k server and d: is on the Win2kProf machine then it
> works without UNC.
> 2. When I reverse this, ie I execute this command on the WIN2k Server
> (drive d to restore from the Win2kProf machine like
> " restore database restoretest
> from disk = '\\win2kprof\d\backups\dottietest.bak'
> with move 'vam_system_data' to
> 'd:\vamdata\sample\vamsystemdata.mdf', ...",
> then I get the original error "... Operating system error = 5(Access
> is denied.)."
> At this time the Property page's Security tab for the dottietest.bak
> file on the winProf machine, looked at from the Win2kServer via
> WinExplorer, has: 'Everyone' as group name, with all these
> perimissions checked, but greyed out:
> Full control,Modify, Read & Execute,Read, write, and no other group
> or user.
> The win2k server and win2k prof machines are on the same network
> (belonging to the same workgroup, with no domain present)
> 3. Do you really mean that ".. the account that's running the backup
> needs to have permissions to the
> share on the other machine." ? I could have gotten the *.bak file
> that I try to restore from any server in the world, and that is why I
> think that you mean ".. the account that's running the RESTORE needs
> to have permissions to the share on the other machine.
> If this is so, then all this boils down now to "assigning the
> permissions". I wonder how this is done, i.e. what more has to be
> done beyond the server having rights to reading and writing from/to
> the Win2kProf machine (at least via drag and drop of files through
> Win Explorer).
> It is strange that when I execute this from a batch file:
> " copy \\Laptop\Laptop_C\vamdata6\sample\sample
.bak c:\
> isql /S . /U vamlogin /P go /i restore_db.sql "
> ..with this in restore_db.sql :
> "restore database sample
> from disk = 'c:\sample.bak'
> with move 'vsm_system_data' to 'd:\temp\vsmsystemdata.ndf' ,
> move 'vsm_user_data' to 'd:\temp\vsmuserdata.ndf',
> move 'vsm_log' to 'd:\temp\vsmlog.ldf',
> stats=10"
> .. then the restore works, i.e. I seem to be able to copy the file
> from the win2kProf machine, but it fails to execute the "restore"
> from that same machine (as shown further up).
> Perhaps this can only work on a domain controller system, which I do
> not intend to establish.
> Any suggestion?
> TIA
>
>
>
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:etVuFJxJFHA.4064@.tk2msftngp13.phx.gbl...
> 'd:\vamdata\sample\vamsystemdata.mdf',