Search code examples
sql-serverrestoredatabase-backups

RESTORE .bak-file from c:\users\ directories on SQL Server

I have this update tool for my program. The tool updates the SQL Server database with this code (vb and sql).

Dim sql As Process = Process.Start("sqlcmd.exe", Param + " -i update.sql -o log.txt")

Param contains the name of the .bak file myprogram_update.bak among others.

update.sql starts with

RESTORE DATABASE [myprogram_tmp] 
FROM DISK = N'$(db_src)' 
WITH FILE = 1,  
MOVE N'myprogram_tmp' TO @mdf,
MOVE N'myprogram_tmp_log' TO @ldf,
NOUNLOAD, STATS = 5

@mdf and @ldf are standard paths read from the SQL Server settings.

The update tool is shipped to customers and the problem is that usually SQL Servers aren't allowed to read in user directories of windows like Desktop or Downloads. But many customers unzip the archive to these directories and then they get this error.

Could not access myprogram_update.bak / access denied.

I can't change the settings on every customers server, so is there any way to make it work for these directories? One idea of mine was to run a setup first and unzip it to program files but maybe there is a smarter solution.

Edit: the tool runs in administrator mode.

Solution

  • I've now put my program into an setup which is usually installed under an ordinary folder, like C:\Program Files (x86)\MyProgram. Here SQL-Server has access.