Sql-server – Restore SQL Server database

restoresql serversql server 2014

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.

Best Answer

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.

Related Solutions

Sql-server – SQL Server 2008 Restore

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.

Sql-server – SQL Server 2008 Database Restore Error – Cannot open backup device 15105

OS Error code 3 is ERROR_PATH_NOT_FOUND: The system cannot find the path specified. With a path starting with Z: my pshyhic powers tell me it is a mapped drive. Drive mapping is session scoped and I'm convinced your service session does not see Z:. Mapping a drive to be seen by a service is, for all practical purposes, impossible.

Use a valid path. Use UNC names like \\server\share. Consider the security implication of impersonation and delegation when accessing remote shares.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 Restore

Sql-server – SQL Server 2008 Database Restore Error – Cannot open backup device 15105

Related Question