restore database using OSQL

Dear Sir/Madam,

Would you please help me to create a batch file to restore a database from a file located on a remote path to my MSDE installed on my workstation.

the original database location was on the D drive on the server

but when i want to restore it to my MSDE it will be in a different path which is C

I want to create a command batch file by just double clicking on it, it will restore the database. (i want a forced restoration).

Please proivde me with your help.

Regards,

HotBirdLook up RESTORE DATABASE in BOL. It's all there.|||Please check this script is it runnable and right

OSQL -U sa - P xxxx RESTORE DATABASE mydb FROM DISK = '\\195.xxx.xxx.xxx\dolphindata\30704'
WITH REPLACE, MOVE 'mydb_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Data.MDF',MOVE 'mydb_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_LOG.LDF' QUIT

I put this script in a file called "restore.bat"

Please advise me

Thanks in advance
HotBird|||Surround your RESTORE...by quotation marks and loose QUIT:

OSQL -U sa - P xxxx "RESTORE DATABASE mydb FROM DISK = '\\195.xxx.xxx.xxx\dolphindata\30704'
WITH REPLACE, MOVE 'mydb_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_Data.MDF',MOVE 'mydb_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mydb_LOG.LDF'"

Restore backup to remote hosted server

Sorry if this is elementary, but I have searched and cannot find an answer.

I have a backup file of a sql server 2000 database. I want to restore it to a remote hosted sql server 2005.

Using sql server management studio, it seems to me that I cannot access the files stored local on my computer.

Does anybody have a solution?

What is the best way to deploy my backup file to the remote server?

Thanks for any suggestions!

I'm afraid you have to copy the backup file to the local disk on the SQL2005 server.

Restore a remote backup

I want to restore a huge database into my workstation.
The size of the backup file is more than 6 GB and I don't have enough
space on my HD for both the database and the backup file.
So I put the file in a shared folder on a pc connected through a switch
to my pc.

My wkst uses w2k pro sp4, the other PC win xp home SP1. MSDE 2000.
The share is visible and RW for the administrator of my wkst.
The 2 pc are in the same workgroup and are not part of a domain.

I tried to restore using this code:

RESTORE DATABASE Mydb
FROM DISK='\\unc\path\tofile.bak'
WITH ...

and get the error 3201 and in the log:
"BackupDiskFile::OpenMedia: the backup peripheral
'\\unc\path\tofile.bak' could not open. Error in O.S. = 5(Access
Denied.). " (I'm translating form italian)

I read that the problem can arise from the fact that the restoration is
executed by a sql server "user" that has not the same permissions as the
administrator. I tried to assign to the SQLSERVERAGENT service the user
administrator with no avail.

And executing:

xp_cmdshell 'dir \\unc\path\tofile.bak'

gives the error "access denied".

Is there a solution to this problem?
Otherwise how can I restore a database whose backup has almost the same
size as the free space on the disk?

thank you

maxx

--

NOSPAM: Rimuovere i trattini dallo username!
Cut hyphens from my username!emmexx <emme-x-x@.tiscalinet.it> wrote in message news:<bqkcd5$23356c$1@.ID-97197.news.uni-berlin.de>...
> I want to restore a huge database into my workstation.
> The size of the backup file is more than 6 GB and I don't have enough
> space on my HD for both the database and the backup file.
> So I put the file in a shared folder on a pc connected through a switch
> to my pc.
> My wkst uses w2k pro sp4, the other PC win xp home SP1. MSDE 2000.
> The share is visible and RW for the administrator of my wkst.
> The 2 pc are in the same workgroup and are not part of a domain.
> I tried to restore using this code:
> RESTORE DATABASE Mydb
> FROM DISK='\\unc\path\tofile.bak'
> WITH ...
> and get the error 3201 and in the log:
> "BackupDiskFile::OpenMedia: the backup peripheral
> '\\unc\path\tofile.bak' could not open. Error in O.S. = 5(Access
> Denied.). " (I'm translating form italian)
> I read that the problem can arise from the fact that the restoration is
> executed by a sql server "user" that has not the same permissions as the
> administrator. I tried to assign to the SQLSERVERAGENT service the user
> administrator with no avail.
> And executing:
> xp_cmdshell 'dir \\unc\path\tofile.bak'
> gives the error "access denied".
> Is there a solution to this problem?
> Otherwise how can I restore a database whose backup has almost the same
> size as the free space on the disk?
> thank you
> maxx

The SQL Server service account must have access to the network share,
so it must be a 'real' account, not LocalSystem. Since you have a
workgroup, you would need to create an account on both PCs with the
same name and password - this should allow you to access the share.

Simon|||Il 04/12/2003 14.04, Simon Hayes scrisse:
> emmexx <emme-x-x@.tiscalinet.it> wrote in message news:<bqkcd5$23356c$1@.ID-97197.news.uni-berlin.de>...
>
> The SQL Server service account must have access to the network share,
> so it must be a 'real' account, not LocalSystem. Since you have a
> workgroup, you would need to create an account on both PCs with the
> same name and password - this should allow you to access the share.

Already tried this but it did not work.
Anyway a shared folder on xp Home seems to be accessible by any remote
account.
XP Home seems a little bit weird about users...

Thank you
maxx

--

NOSPAM: Rimuovere i trattini dallo username!
Cut hyphens from my username!

RESTORE 64-bit database via 32-bit GUI...

Hello all,
I'm trying to use the 32-bit GUI, on a remote server, to restore a database
on one of our 64-bit servers. The GUI returns a prompt that the restore was
successful but the GUI indicates that the database is in a Loading mode. I
tried to issue a recovery command but it fails saying that some of the files
were partially restored.
I figured that there is a problem using the 32-bit client utility so I used
OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
compatible 64-bit utility. By doing so, I was able to restore the database
successfully.
So my question is, is not being able to restore a 64-bit database via a
32-bit GUI, on a remote server, a known issue?
Thanks...Hi
On the 32 bit client, have you installed a minimum of sp3a?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
> Hello all,
> I'm trying to use the 32-bit GUI, on a remote server, to restore a
> database
> on one of our 64-bit servers. The GUI returns a prompt that the restore
> was
> successful but the GUI indicates that the database is in a Loading mode.
> I
> tried to issue a recovery command but it fails saying that some of the
> files
> were partially restored.
> I figured that there is a problem using the 32-bit client utility so I
> used
> OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
> compatible 64-bit utility. By doing so, I was able to restore the
> database
> successfully.
> So my question is, is not being able to restore a 64-bit database via a
> 32-bit GUI, on a remote server, a known issue?
> Thanks...
>|||Yeah, it's 8.00.760 (SP3)...
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> On the 32 bit client, have you installed a minimum of sp3a?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
> > Hello all,
> >
> > I'm trying to use the 32-bit GUI, on a remote server, to restore a
> > database
> > on one of our 64-bit servers. The GUI returns a prompt that the restore
> > was
> > successful but the GUI indicates that the database is in a Loading mode.
> > I
> > tried to issue a recovery command but it fails saying that some of the
> > files
> > were partially restored.
> >
> > I figured that there is a problem using the 32-bit client utility so I
> > used
> > OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
> > compatible 64-bit utility. By doing so, I was able to restore the
> > database
> > successfully.
> >
> > So my question is, is not being able to restore a 64-bit database via a
> > 32-bit GUI, on a remote server, a known issue?
> >
> > Thanks...
> >
>
>

RESTORE 64-bit database via 32-bit GUI...

Hello all,
I'm trying to use the 32-bit GUI, on a remote server, to restore a database
on one of our 64-bit servers. The GUI returns a prompt that the restore was
successful but the GUI indicates that the database is in a Loading mode. I
tried to issue a recovery command but it fails saying that some of the files
were partially restored.
I figured that there is a problem using the 32-bit client utility so I used
OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
compatible 64-bit utility. By doing so, I was able to restore the database
successfully.
So my question is, is not being able to restore a 64-bit database via a
32-bit GUI, on a remote server, a known issue?
Thanks...
Hi
On the 32 bit client, have you installed a minimum of sp3a?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
> Hello all,
> I'm trying to use the 32-bit GUI, on a remote server, to restore a
> database
> on one of our 64-bit servers. The GUI returns a prompt that the restore
> was
> successful but the GUI indicates that the database is in a Loading mode.
> I
> tried to issue a recovery command but it fails saying that some of the
> files
> were partially restored.
> I figured that there is a problem using the 32-bit client utility so I
> used
> OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
> compatible 64-bit utility. By doing so, I was able to restore the
> database
> successfully.
> So my question is, is not being able to restore a 64-bit database via a
> 32-bit GUI, on a remote server, a known issue?
> Thanks...
>
|||Yeah, it's 8.00.760 (SP3)...
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> On the 32 bit client, have you installed a minimum of sp3a?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
>
>

RESTORE 64-bit database via 32-bit GUI...

Hello all,
I'm trying to use the 32-bit GUI, on a remote server, to restore a database
on one of our 64-bit servers. The GUI returns a prompt that the restore was
successful but the GUI indicates that the database is in a Loading mode. I
tried to issue a recovery command but it fails saying that some of the files
were partially restored.
I figured that there is a problem using the 32-bit client utility so I used
OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
compatible 64-bit utility. By doing so, I was able to restore the database
successfully.
So my question is, is not being able to restore a 64-bit database via a
32-bit GUI, on a remote server, a known issue?
Thanks...Hi
On the 32 bit client, have you installed a minimum of sp3a?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
> Hello all,
> I'm trying to use the 32-bit GUI, on a remote server, to restore a
> database
> on one of our 64-bit servers. The GUI returns a prompt that the restore
> was
> successful but the GUI indicates that the database is in a Loading mode.
> I
> tried to issue a recovery command but it fails saying that some of the
> files
> were partially restored.
> I figured that there is a problem using the 32-bit client utility so I
> used
> OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
> compatible 64-bit utility. By doing so, I was able to restore the
> database
> successfully.
> So my question is, is not being able to restore a 64-bit database via a
> 32-bit GUI, on a remote server, a known issue?
> Thanks...
>|||Yeah, it's 8.00.760 (SP3)...
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> On the 32 bit client, have you installed a minimum of sp3a?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
> > Hello all,
> >
> > I'm trying to use the 32-bit GUI, on a remote server, to restore a
> > database
> > on one of our 64-bit servers. The GUI returns a prompt that the restore
> > was
> > successful but the GUI indicates that the database is in a Loading mode.
> > I
> > tried to issue a recovery command but it fails saying that some of the
> > files
> > were partially restored.
> >
> > I figured that there is a problem using the 32-bit client utility so I
> > used
> > OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
> > compatible 64-bit utility. By doing so, I was able to restore the
> > database
> > successfully.
> >
> > So my question is, is not being able to restore a 64-bit database via a
> > 32-bit GUI, on a remote server, a known issue?
> >
> > Thanks...
> >
>
>

RESTORE 64-bit database via 32-bit GUI...

Hello all,
I'm trying to use the 32-bit GUI, on a remote server, to restore a database
on one of our 64-bit servers. The GUI returns a prompt that the restore was
successful but the GUI indicates that the database is in a Loading mode. I
tried to issue a recovery command but it fails saying that some of the files
were partially restored.
I figured that there is a problem using the 32-bit client utility so I used
OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
compatible 64-bit utility. By doing so, I was able to restore the database
successfully.
So my question is, is not being able to restore a 64-bit database via a
32-bit GUI, on a remote server, a known issue?
Thanks...Hi
On the 32 bit client, have you installed a minimum of sp3a?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
> Hello all,
> I'm trying to use the 32-bit GUI, on a remote server, to restore a
> database
> on one of our 64-bit servers. The GUI returns a prompt that the restore
> was
> successful but the GUI indicates that the database is in a Loading mode.
> I
> tried to issue a recovery command but it fails saying that some of the
> files
> were partially restored.
> I figured that there is a problem using the 32-bit client utility so I
> used
> OSQL, via SQL Server Agent, on the 64-bit to ensure that I was using a
> compatible 64-bit utility. By doing so, I was able to restore the
> database
> successfully.
> So my question is, is not being able to restore a 64-bit database via a
> 32-bit GUI, on a remote server, a known issue?
> Thanks...
>|||Yeah, it's 8.00.760 (SP3)...
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> On the 32 bit client, have you installed a minimum of sp3a?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:8C8842C1-37B4-4AFB-BCB4-A63FD58DC323@.microsoft.com...
>
>