0

Using VB.NET, I want to restore a SQL Server database from a .BAK file without the "log file".

I tried using:

Restore database dbName 
from disk = '" & path & "'"

but it didn't work, exception that refers to 

"not finding _log file"

more specifically:

Cannot open backup device 'D:\AlomohasebSQL.bak'.  
Operating system error 2(‏‏Invalid path).  
Msg 3013, Level 16, State 1, Line 1  
RESTORE DATABASE is terminating abnormally.
Improve this question

2 Answers 2

Reset to default
1

From the more complete error message:

Cannot open backup device 'D:\AlomohasebSQL.bak'. Operating system error 2 (‏‏Invalid path). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

Do you know the exact SQL restore statement you are performing? No matter what front-end language you are using, it should be executing something like this against the SQL Server:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH (various options here)

In your case, I think the error message indicates that the backup file location ("D:\AlomohasebSQL.bak" in your error above) is incorrect. Obviously, make sure this is a valid drive, path and filename.

IMPORTANT NOTE: This path needs to be visible to the SQL Server, not to wherever you are running this app. (Thanks @ColinMackay for bringing this up). In a related note, check to see that the SQL Server service account has the proper permissions to read this file at that location.

There is a (smaller) possibility that it doesn't know where to place the database files after it restores them. SQL does have a "default" path, but this is sometimes out of date. If necessary you can specify the path to restore the files to:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH MOVE 'primarydatafilename' To 'D:\DB\data.mdf', 
     MOVE 'logfilename' To 'D:\DB\log.ldf'

You'd obviously need to know the files that are part of that database. If you don't, just use the SSMS restore wizard. There are ways to find that via the RESTORE command, let me know if you need that.

Improve this answer
1
  • @BradC, Body thank you so so so so much, Finally, this is exactly what I want, It seems that I have to extract the ldf and mdf from the bak and move them to the original path
    – Muhammad Radwan
    May 27, 2017 at 3:55
-3

In SQL, the LOG_TRANSACTION file is an undissolvable part of database - once you have defined that in your backup routine.

To get the restore without the LOG, you must define the backup as SIMPLE-FULL one, like below:

The Task-Backup screen

BUT, to get success on this, the database properties must also define that characteristics:

The Database Properties screen

Improve this answer
1
  • 3
    This answer is incorrect. ALL SQL server databases need a log file, no matter what recovery mode you are in.
    – BradC
    May 26, 2017 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.