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.

migrated from stackoverflow.com May 26 '17 at 13:13

This question came from our site for professional and enthusiast programmers.

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.

  • @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 '17 at 3:55
  • @MuhammadRadwan You can accept the answer by clicking the check mark next to it. – Paul White May 31 '17 at 15:45
-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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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