1

I've developed an Android app that uses an online SQL Server database (hosting provider is: a2hosting.com).

In addition, there is a native Windows app that everyday at 12am backup some tables in a .bak file in E:\ location (USB FLASH DRIVE location).

I want to make a scheduled cmd batch file to update the server database with the local database.

I am trying to use the restore command to do that, because I want to replace the previous online database with my new local database.

What I have try to do:

restoreTOdb.bat file:

sqlcmd -S (myservername) -U (myusername) -P (mypassword) -i db.sql

pause

db.sql file:

USE master;
GO

RESTORE DATABASE (mydbname) 
FROM DISK = 'E:\filename.bak' 
WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5;
GO

The connection to my SQL Server database is successful

This is the error that appears in cmd:

Changed database context to 'master'.

Msg 3201, Level 16, State 2, Server (server-name), Line 1
Cannot open backup device 'E:\filename.bak'.

Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Server (server-name), Line 1
RESTORE DATABASE is terminating abnormally.

C:\Users\Manos\Desktop\DB CONNECTION>pause

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shaidor
  • 11
  • 2
  • I noticed you are using the E drive, is that a removable device? If so than its only mounted for your windows shell and therefore the MsSQL server that's trying to load it can't see it. Copy it to a physical hard drive, like C, that the MsSQL server can see and It will probably work! It is a non-intuitive behavior of windows, especially for those used to posix OS servers. – Ralph Ritoch Sep 09 '18 at 15:19
  • In case I wasn't clear it needs to be copied to the C drive of the machine running the MsSQL server. – Ralph Ritoch Sep 09 '18 at 15:27
  • @RalphRitoch Thank you for your answer, i tried in D:, C: and E: drive every time the same error. – Shaidor Sep 09 '18 at 15:30
  • did you copy it to the C/D of the server, such as via sftp or some other file transfer method? The remote server can't see your local drives, normally. – Ralph Ritoch Sep 09 '18 at 15:35
  • If you want to restore a `.bak` onto a **remote** server machine, the `*.bak` file **must be** on that **remote machine's E:** drive...... the remote SQL Server cannot reach down and grab a file off your local E:\ drive (**thank goodness** it can't do that!!) – marc_s Sep 09 '18 at 16:01
  • @RalphRitoch ah i didn't know that. Can you explain me, how to use the sftp command? The only command that i know is ftp but i had no success with this script so i abandoned it. – Shaidor Sep 09 '18 at 16:01
  • See https://stackoverflow.com/questions/16721891/single-line-sftp-from-terminal – Ralph Ritoch Sep 09 '18 at 16:03
  • @marc_s thank you very much for your explanation. I will try to send my .bak file first in my online file server and then i will try to use it. – Shaidor Sep 09 '18 at 16:04
  • @RalphRitoch Really thank you for your help, i will study this command. I didn't know about it. – Shaidor Sep 09 '18 at 16:06

0 Answers0