CODEWITHSUNDEEP
sqlsql-server-2008-r2
Yunus emre
Yunus emre

Reputation: 21

How to restore .bak file in SQL Server 2008 R2

I have a code blog but a problem was occurred. In here my backup file is ISBANK.bak and database name is new database name. It doesn't contain anything. I want to add .bak file's content to a new database..

RESTORE DATABASE [DBName]
FROM DISK = 'D:\ISBANK.bak'
WITH REPLACE,
MOVE 'DBName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008RR2\MSSQL\DATA\DBName.mdf',
MOVE 'DBName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008RR2\MSSQL\DATA\DBName.ldf'

I am getting these errors:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSRV2008\MSSQL\DATA\OBERNET_DENEME.mdf" failed with the operating system error 3 (failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 1
File 'OCEAN' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSRV2008\MSSQL\DATA\OBERNET_DENEME.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSRV2008\MSSQL\DATA\OBERNET_DENEME.ldf" failed with the operating system error 3 (failed to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Line 1
File 'OCEAN_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLSRV2008\MSSQL\DATA\OBERNET_DENEME.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Upvotes: 2

Views: 36840

Answers (3)

Jack D
Jack D

Reputation: 173

No Doubt, backup, and restore is the major task for any DBA. As per the official website – Error “RESTORE DATABASE is terminating abnormally” The fix for this issue was first released in Cumulative Update 6.

You can follow the steps to restore the .bak file in SQL Server 2008 R2.

First & most importantly, you must have CREATE DATABASE permissions to execute RESTORE.

  • Step 1 – Open your SSMS and navigate to the database.
  • Step 2 – Right click on database > click on restore database.
  • Step 3 - Add the destination for restore & Source for restore.
  • Step 4 - Choose the backup file that you need to restore
  • Step 5 - In the left pane, click on Overwrite the existing database (WITH REPLACE)

After completion of the restoration process, you can work on your data.

Upvotes: 0

Dinesh vishe
Dinesh vishe

Reputation: 3598

you have use default path for database restoration.

This will useful to you.

enter image description here

Upvotes: 0

TheTechGuy
TheTechGuy

Reputation: 17354

Edit

Remove WITH REPLACE

RESTORE DATABASE my_new_database FROM disk = 'E:\path_to_bak_file\database_name_634182143083281212.bak'
WITH
   MOVE 'the logical name from previous operation check row 1' TO 'E:\path_to_sql2008_file_onyour_machine\my_new_database.mdf',
   MOVE 'the logical name from previous operation check row 2' TO 'E:\path_to_sql2008_file_onyour_machine\my_new_database_log.ldf'
GO

You can also use the Restore utility available in SQL Server Management Studio (SSMS). This is a step by step process which lets yous select the .bak file and restore it as new database (which ever name you like for it).

Fire SQL Server Management Studio, connect to the server as admin, right click on the database node and select restore.

enter image description here

Upvotes: 3

Related Questions