How to restore a database from a .bak file?

0
votes

My .bak file is the other the local server.

I tried too many different script but unable to successes.

I tried syntax is

RESTORE FILELISTONLY FROM DISK='D:\ERPNewtesting-12022014.bak'

Get the Logicalname from above query and use in below query

RESTORE DATABASE Test FROM DISK='ERPNewtesting-12022014.bak'
WITH 
   MOVE 'ERPAgrotechBeta' TO 'D:\Pragnesh\Test.mdf',
   MOVE 'ERPAgrotechBeta_log' TO 'D:\Pragnesh\Test_log.ldf',
   REPLACE,
STATS=10

After executing above query below error is generate.

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\ERPNewtesting-12022014.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Please help me for this manner

4
sql-serverrestore
.bak file is in the local machine – Pragnesh Khalas
What about reading the message? Operating system error 2(The system cannot find the file specified.) – usr
this is the error message which is return from the sql server – Pragnesh Khalas

4 Answers

3
votes

It sounds like you have backed up a db on one server, and you want to restore it to another server. Is this correct?

Your backup file D:\ERPNewtesting-12022014.bak is on server A, right? It needs to be accessible to the account running the SQL Server service on server B. A few quick options come to mind:

  1. Create a share on server A, move the backup file into the corresponding shared folder.
  2. Move the backup file to an existing UNC path.
  3. Move the backup file to server B.

Then proceed with the restore. If you chose option 1 or 2, it will be something like this:

RESTORE DATABASE Test 
FROM DISK='\\SomeServer\Shared Folder\ERPNewtesting-12022014.bak'
WITH 
    MOVE 'ERPAgrotechBeta' TO 'D:\Pragnesh\Test.mdf',
    MOVE 'ERPAgrotechBeta_log' TO 'D:\Pragnesh\Test_log.ldf',
REPLACE,
STATS=10

If you chose option 3, it will look something like this:

RESTORE DATABASE Test 
FROM DISK='D:\Path on Server B\ERPNewtesting-12022014.bak'
WITH 
    MOVE 'ERPAgrotechBeta' TO 'D:\Pragnesh\Test.mdf',
    MOVE 'ERPAgrotechBeta_log' TO 'D:\Pragnesh\Test_log.ldf',
REPLACE,
STATS=10
2
votes

FYI: From SqlServer management studio there's an option to restore a database from the UI. It allows you to browse to find the source bak file. The browse window opened is on the database server machine NOT the machine where you are running management studio.

1
votes

You're only specifying a "relative" path for the .bak file here:

RESTORE DATABASE Test FROM DISK='ERPNewtesting-12022014.bak'

and quite obviously, from the error message, the file isn't there where this points to.

Cannot open backup device 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\ERPNewtesting-12022014.bak'. Operating system error 2 (The system cannot find the file specified.)

You'll need to specify the full, complete path to the .bak file here:

RESTORE DATABASE Test FROM DISK='D:\ERPNewtesting-12022014.bak'
                                *****

If you would actually try and specify the complete path here so that SQL Server can find it's source for the restore - I'm always positive it'll work......

0
votes

Its mainly due to the rights issue :-

Follow below 2 steps and it will resolve :-

  1. Let the Server take backup on default directory and copy the file in your desired folder. I did that.

  2. Give same kind of permissions to your desired folder as Backup directory has.

OR

U can add the service account into local admin group of the machine where backup is taken