restore database.

I was attempting to restore a database and it hung up. Now when I try to
restore I get the message, re-issue restore statement using WITH REPLACE, but
not sure how to do this, is it a restore dbase option through the GUI, thanks.
Paul G
Software engineer.
I believe the option in the gui is something like Force Restore over existing
database or something similar. Also, make sure that on the first tab you
have the correct name to restore the database as. I think it defaults to
Master which is not what you want.
"Paul" wrote:

> I was attempting to restore a database and it hung up. Now when I try to
> restore I get the message, re-issue restore statement using WITH REPLACE, but
> not sure how to do this, is it a restore dbase option through the GUI, thanks.
> --
> Paul G
> Software engineer.
sql

restore database.

I was attempting to restore a database and it hung up. Now when I try to
restore I get the message, re-issue restore statement using WITH REPLACE, bu
t
not sure how to do this, is it a restore dbase option through the GUI, thank
s.
--
Paul G
Software engineer.I believe the option in the gui is something like Force Restore over existin
g
database or something similar. Also, make sure that on the first tab you
have the correct name to restore the database as. I think it defaults to
Master which is not what you want.
"Paul" wrote:

> I was attempting to restore a database and it hung up. Now when I try to
> restore I get the message, re-issue restore statement using WITH REPLACE,
but
> not sure how to do this, is it a restore dbase option through the GUI, tha
nks.
> --
> Paul G
> Software engineer.

restore database.

I was attempting to restore a database and it hung up. Now when I try to
restore I get the message, re-issue restore statement using WITH REPLACE, but
not sure how to do this, is it a restore dbase option through the GUI, thanks.
--
Paul G
Software engineer.I believe the option in the gui is something like Force Restore over existing
database or something similar. Also, make sure that on the first tab you
have the correct name to restore the database as. I think it defaults to
Master which is not what you want.
"Paul" wrote:
> I was attempting to restore a database and it hung up. Now when I try to
> restore I get the message, re-issue restore statement using WITH REPLACE, but
> not sure how to do this, is it a restore dbase option through the GUI, thanks.
> --
> Paul G
> Software engineer.

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

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...[vbcol=seagreen]
> 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 -
>
|||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[vbcol=seagreen]
> 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
>
|||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[vbcol=seagreen]
> 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',
> "
"
> 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',
sql

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',

Restore database fails because database is in use.

I'm a newbie so please be gentle.

In attempting to run a restore I get the following error message:
"Exclusive access could not be obtained because the database is in
use." However, it doesn't appear that there are any connections to the
database. At the suggestion of another thread, I ran sp_who2 and there
are no connections to the database in question. I've been running the
same restore for months and all has been well, until yesterday...

I wrote an .asp page (below) to test connectivity to this database.
The vb code ran successfully and soon thereafter, my nightly restore
failed. Task manager does no show that the asp page is active.

Lastly, I did add a user ("jbtest") as well as change the option to use
either Windows authentication or SQL Server authentication in
Enterprise Manager.

Any ideas would be greatly appreciated.

Thanks.

<%@. Language=VBScript %
<%
companyName = "agemni"

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER=SQL
Server;SERVER=SHEELA-NA-GIG;UID=jbtest;PWD=test1;APP=Microsoft
Development Environment;WSID=SHEELA-NA-GIG;DATABASE=" & companyName &
";Trusted_Connection=No"

cnn.ConnectionTimeout = 300
cnn.CommandTimeout = 300
cnn.Open

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Preferences"
RS.Open strSQL, cnn

Response.Write "connection established with the <b>" & RS("Company
Name") & "</b> database. They are a <b>" & RS("street") & "</b>
customer. "

RS.Close
Set RS = Nothing
%<jbmccluskey@.satcountry.com> wrote in message
news:1112117455.317127.10740@.g14g2000cwa.googlegro ups.com...
> I'm a newbie so please be gentle.
> In attempting to run a restore I get the following error message:
> "Exclusive access could not be obtained because the database is in
> use." However, it doesn't appear that there are any connections to the
> database. At the suggestion of another thread, I ran sp_who2 and there
> are no connections to the database in question. I've been running the
> same restore for months and all has been well, until yesterday...
> I wrote an .asp page (below) to test connectivity to this database.
> The vb code ran successfully and soon thereafter, my nightly restore
> failed. Task manager does no show that the asp page is active.
> Lastly, I did add a user ("jbtest") as well as change the option to use
> either Windows authentication or SQL Server authentication in
> Enterprise Manager.
> Any ideas would be greatly appreciated.
> Thanks.

<snip
Probably the most reliable solution is to use ALTER DATABASE to get rid of
any open connections, wherever they're from:

alter database MyDB set restricted_user with rollback immediate

See Books Online for the other state options (single_user, offline etc.) -
one of them might suit your needs better.

You don't mention how you're running the restore, but if it's via a
scheduled job, you should also check that the selected database for the
restore step is set to master, not to the database you're restoring,
otherwise the job can block itself.

Simon|||Thanks for the reply Simon. The problem was that I was trying to run
the RESTORE in the database itself as opposed to running it under
MASTER.

JB

Restore database error

I get ODBC SQLState error 42000 with message "internal
consistency error occured" while I restored database.
I tried MSSQL 7 and MSSQL 2000 with the same result.
This database was running without problem (MSSQL 7) and backup
was created "successfully".
I have another backups on the tape (for three years)
and all files act equally.
Any idea?
Perhaps the database was corrupt when you backuped it? Can you run a DBCC CHECKDB on the original
database (assuming you still have that online)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Horak" <richard_horak@.hotmail.com> wrote in message
news:eGWqC6k0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> I get ODBC SQLState error 42000 with message "internal
> consistency error occured" while I restored database.
> I tried MSSQL 7 and MSSQL 2000 with the same result.
> This database was running without problem (MSSQL 7) and backup
> was created "successfully".
> I have another backups on the tape (for three years)
> and all files act equally.
> Any idea?
|||Corrupt? I think, that database, running and serving all requests
wouldn't be corrupt.
Original server is running now new OS and new SQL, return back is not
possible.....
Tibor Karaszi wrote:
> Perhaps the database was corrupt when you backuped it? Can you run a DBCC CHECKDB on the original
> database (assuming you still have that online)?
>
|||You can still have a corruption in the database even if you work against it. This is why you want to
run regular DBCC CHECKDB commands to see if you have any type of corruption (most often introduced
by hardware problems). I suggest you open a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Horak" <richard_horak@.hotmail.com> wrote in message
news:%23%23lMjD50EHA.3840@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Corrupt? I think, that database, running and serving all requests
> wouldn't be corrupt.
> Original server is running now new OS and new SQL, return back is not
> possible.....
> Tibor Karaszi wrote:
original[vbcol=seagreen]

Restore Database

When I try to restore my database receive the message:

"The media set for database 'MyDB' has 2 family members, but only 1 are provided. All members must be provided."

I don't find the other dump device.

Do I can restore anyway??

thanks,

Ericson.I am having a similar problem with a backup of a database that has been successfully running the past two months. Suddenly this weekend, the same error message started appearing for me?

Any ideas?|||Originally posted by acg_ray
I am having a similar problem with a backup of a database that has been successfully running the past two months. Suddenly this weekend, the same error message started appearing for me?

Any ideas?

I have no succes to restore my database.
I has create the database again e loses the data.|||One of my customers reported the same problem that you guys were having. I found in his case that he had spanned his backup across multiple files when he made it.

So, in the Database Backup Destination box, he had multiple entries before backing up. Then when he went to restore the database to another machine, he was unaware that he should have had multiple files. This restore file was coded to tell Sql Server that it was only one part of the backup.

In his case, I had him go back to the server and create only one backup file.|||Are you using tape backup? It may be that your database backup run across two tapes and it's looking for another backup media.

Restore Database

I'm trying to restore a data base to a different server
and ame getting this error message
'Logical file x is not part of database x'
can any one explain
CheersCan you post the RESTORE command you are executing?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c48447$0b4719d0$a301280a@.phx.gbl...
> I'm trying to restore a data base to a different server
> and ame getting this error message
> 'Logical file x is not part of database x'
> can any one explain
> Cheers|||restore database x
FROM TAPE = '\\.\tape0'
with
MOVE datafile TO location,
MOVE logfile TO location,
nounload

>--Original Message--
>Can you post the RESTORE command you are executing?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:041501c48447$0b4719d0$a301280a@.phx.gbl...
>
>.
>|||Thanks. It seems that the logical filenames for the database doesn't match t
he filenames you
specified in the RESTORE command. I suggest you use RESTORE HEADERONLY and R
ESTORE FILELISTONLY to
verify the logical filenames etc, and based on that construct your RESTORE c
ommand.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...[vbcol=seagreen]
> restore database x
> FROM TAPE = '\\.\tape0'
> with
> MOVE datafile TO location,
> MOVE logfile TO location,
> nounload
>
> message|||Cheers
We have already done that and they match
>--Original Message--
>Thanks. It seems that the logical filenames for the
database doesn't match the filenames you
>specified in the RESTORE command. I suggest you use
RESTORE HEADERONLY and RESTORE FILELISTONLY to
>verify the logical filenames etc, and based on that
construct your RESTORE command.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...
server[vbcol=seagreen]
>
>.
>|||I see. I'm afraid that we can't be of much help here if you don't post the o
utput from these
statements and the exact restore command you try to execute. Basically, we d
on't have anything to go
on.
You might want to open a case with MS support if you feel that you have cons
tructed the statement
correctly and that SQL Server behaves in some erratic way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:751601c4844c$e2127d60$a501280a@.phx.gbl...[vbcol=seagreen]
> Cheers
> We have already done that and they match
> database doesn't match the filenames you
> RESTORE HEADERONLY and RESTORE FILELISTONLY to
> construct your RESTORE command.
> server|||Is it possible that
There are multiple backups on the tape and you are getting the wrong one
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
<anonymous@.discussions.microsoft.com> wrote in message
news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...[vbcol=seagreen]
> restore database x
> FROM TAPE = '\\.\tape0'
> with
> MOVE datafile TO location,
> MOVE logfile TO location,
> nounload
>
> message|||Ahh, OK. Glad you found it. As you probably notice, we tend to ask for exact
output and the command
executed. This is just to spot things like this. It is easy to forget that a
backup set (especially
when backing up to disk) can have several files in it, something that Wayne
picked up on... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:78b901c48453$dcc6e1f0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Tibor, the problem has ironed its self our. THe DBA
> who refered the question to me assured me that he was
> selecting the correct backups within the back up set, alas
> he wasn't...thanks for your help
> Mat
> you don't post the output from these
> execute. Basically, we don't have anything to go
> that you have constructed the statement
> message

Restore Database

I'm trying to restore a data base to a different server
and ame getting this error message
'Logical file x is not part of database x'
can any one explain
CheersCan you post the RESTORE command you are executing?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:041501c48447$0b4719d0$a301280a@.phx.gbl...
> I'm trying to restore a data base to a different server
> and ame getting this error message
> 'Logical file x is not part of database x'
> can any one explain
> Cheers|||restore database x
FROM TAPE = '\\.\tape0'
with
MOVE datafile TO location,
MOVE logfile TO location,
nounload
>--Original Message--
>Can you post the RESTORE command you are executing?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:041501c48447$0b4719d0$a301280a@.phx.gbl...
>> I'm trying to restore a data base to a different server
>> and ame getting this error message
>> 'Logical file x is not part of database x'
>> can any one explain
>> Cheers
>
>.
>|||Thanks. It seems that the logical filenames for the database doesn't match the filenames you
specified in the RESTORE command. I suggest you use RESTORE HEADERONLY and RESTORE FILELISTONLY to
verify the logical filenames etc, and based on that construct your RESTORE command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...
> restore database x
> FROM TAPE = '\\.\tape0'
> with
> MOVE datafile TO location,
> MOVE logfile TO location,
> nounload
>
> >--Original Message--
> >Can you post the RESTORE command you are executing?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Mat" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:041501c48447$0b4719d0$a301280a@.phx.gbl...
> >> I'm trying to restore a data base to a different server
> >> and ame getting this error message
> >>
> >> 'Logical file x is not part of database x'
> >> can any one explain
> >>
> >> Cheers
> >
> >
> >.
> >|||Cheers
We have already done that and they match
>--Original Message--
>Thanks. It seems that the logical filenames for the
database doesn't match the filenames you
>specified in the RESTORE command. I suggest you use
RESTORE HEADERONLY and RESTORE FILELISTONLY to
>verify the logical filenames etc, and based on that
construct your RESTORE command.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...
>> restore database x
>> FROM TAPE = '\\.\tape0'
>> with
>> MOVE datafile TO location,
>> MOVE logfile TO location,
>> nounload
>>
>> >--Original Message--
>> >Can you post the RESTORE command you are executing?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Mat" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:041501c48447$0b4719d0$a301280a@.phx.gbl...
>> >> I'm trying to restore a data base to a different
server
>> >> and ame getting this error message
>> >>
>> >> 'Logical file x is not part of database x'
>> >> can any one explain
>> >>
>> >> Cheers
>> >
>> >
>> >.
>> >
>
>.
>|||I see. I'm afraid that we can't be of much help here if you don't post the output from these
statements and the exact restore command you try to execute. Basically, we don't have anything to go
on.
You might want to open a case with MS support if you feel that you have constructed the statement
correctly and that SQL Server behaves in some erratic way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:751601c4844c$e2127d60$a501280a@.phx.gbl...
> Cheers
> We have already done that and they match
> >--Original Message--
> >Thanks. It seems that the logical filenames for the
> database doesn't match the filenames you
> >specified in the RESTORE command. I suggest you use
> RESTORE HEADERONLY and RESTORE FILELISTONLY to
> >verify the logical filenames etc, and based on that
> construct your RESTORE command.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...
> >> restore database x
> >> FROM TAPE = '\\.\tape0'
> >> with
> >> MOVE datafile TO location,
> >> MOVE logfile TO location,
> >> nounload
> >>
> >>
> >> >--Original Message--
> >> >Can you post the RESTORE command you are executing?
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> >"Mat" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:041501c48447$0b4719d0$a301280a@.phx.gbl...
> >> >> I'm trying to restore a data base to a different
> server
> >> >> and ame getting this error message
> >> >>
> >> >> 'Logical file x is not part of database x'
> >> >> can any one explain
> >> >>
> >> >> Cheers
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Is it possible that
There are multiple backups on the tape and you are getting the wrong one
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
<anonymous@.discussions.microsoft.com> wrote in message
news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...
> restore database x
> FROM TAPE = '\\.\tape0'
> with
> MOVE datafile TO location,
> MOVE logfile TO location,
> nounload
>
> >--Original Message--
> >Can you post the RESTORE command you are executing?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Mat" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:041501c48447$0b4719d0$a301280a@.phx.gbl...
> >> I'm trying to restore a data base to a different server
> >> and ame getting this error message
> >>
> >> 'Logical file x is not part of database x'
> >> can any one explain
> >>
> >> Cheers
> >
> >
> >.
> >|||Thanks Tibor, the problem has ironed its self our. THe DBA
who refered the question to me assured me that he was
selecting the correct backups within the back up set, alas
he wasn't...thanks for your help
Mat
>--Original Message--
>I see. I'm afraid that we can't be of much help here if
you don't post the output from these
>statements and the exact restore command you try to
execute. Basically, we don't have anything to go
>on.
>You might want to open a case with MS support if you feel
that you have constructed the statement
>correctly and that SQL Server behaves in some erratic way.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:751601c4844c$e2127d60$a501280a@.phx.gbl...
>> Cheers
>> We have already done that and they match
>> >--Original Message--
>> >Thanks. It seems that the logical filenames for the
>> database doesn't match the filenames you
>> >specified in the RESTORE command. I suggest you use
>> RESTORE HEADERONLY and RESTORE FILELISTONLY to
>> >verify the logical filenames etc, and based on that
>> construct your RESTORE command.
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...
>> >> restore database x
>> >> FROM TAPE = '\\.\tape0'
>> >> with
>> >> MOVE datafile TO location,
>> >> MOVE logfile TO location,
>> >> nounload
>> >>
>> >>
>> >> >--Original Message--
>> >> >Can you post the RESTORE command you are executing?
>> >> >
>> >> >--
>> >> >Tibor Karaszi, SQL Server MVP
>> >> >http://www.karaszi.com/sqlserver/default.asp
>> >> >http://www.solidqualitylearning.com/
>> >> >
>> >> >
>> >> >"Mat" <anonymous@.discussions.microsoft.com> wrote in
>> >> message
>> >> >news:041501c48447$0b4719d0$a301280a@.phx.gbl...
>> >> >> I'm trying to restore a data base to a different
>> server
>> >> >> and ame getting this error message
>> >> >>
>> >> >> 'Logical file x is not part of database x'
>> >> >> can any one explain
>> >> >>
>> >> >> Cheers
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||Ahh, OK. Glad you found it. As you probably notice, we tend to ask for exact output and the command
executed. This is just to spot things like this. It is easy to forget that a backup set (especially
when backing up to disk) can have several files in it, something that Wayne picked up on... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:78b901c48453$dcc6e1f0$a401280a@.phx.gbl...
> Thanks Tibor, the problem has ironed its self our. THe DBA
> who refered the question to me assured me that he was
> selecting the correct backups within the back up set, alas
> he wasn't...thanks for your help
> Mat
> >--Original Message--
> >I see. I'm afraid that we can't be of much help here if
> you don't post the output from these
> >statements and the exact restore command you try to
> execute. Basically, we don't have anything to go
> >on.
> >
> >You might want to open a case with MS support if you feel
> that you have constructed the statement
> >correctly and that SQL Server behaves in some erratic way.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"mat" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:751601c4844c$e2127d60$a501280a@.phx.gbl...
> >> Cheers
> >> We have already done that and they match
> >> >--Original Message--
> >> >Thanks. It seems that the logical filenames for the
> >> database doesn't match the filenames you
> >> >specified in the RESTORE command. I suggest you use
> >> RESTORE HEADERONLY and RESTORE FILELISTONLY to
> >> >verify the logical filenames etc, and based on that
> >> construct your RESTORE command.
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> ><anonymous@.discussions.microsoft.com> wrote in message
> >> >news:782b01c48449$d25b3bd0$a401280a@.phx.gbl...
> >> >> restore database x
> >> >> FROM TAPE = '\\.\tape0'
> >> >> with
> >> >> MOVE datafile TO location,
> >> >> MOVE logfile TO location,
> >> >> nounload
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >Can you post the RESTORE command you are executing?
> >> >> >
> >> >> >--
> >> >> >Tibor Karaszi, SQL Server MVP
> >> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >> >http://www.solidqualitylearning.com/
> >> >> >
> >> >> >
> >> >> >"Mat" <anonymous@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> >news:041501c48447$0b4719d0$a301280a@.phx.gbl...
> >> >> >> I'm trying to restore a data base to a different
> >> server
> >> >> >> and ame getting this error message
> >> >> >>
> >> >> >> 'Logical file x is not part of database x'
> >> >> >> can any one explain
> >> >> >>
> >> >> >> Cheers
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

restore a sql server 2005 x64 db backup to sql server 2000 32-bit

When I attempt to restore a db backup from sql server 2005 x64 on my production server to a laptop that has sql server 2000 32-bit, I get a message like "Too many files". And when I go to the tab that let's you change the paths to the db files, the two path fields are blank, when normally they would show some paths.

Is this not possible to do?You cannot take a backup on sql2k5 and restore it to sql2k instance. The metadata has changed significantly in sql2k5 for sql2k to understand.