T-SQL Database Restoration Error

0
votes

I am getting below error during restoration of my backup data in SQL2008R2

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

I have tried below mentioned suggestion got from Google and other web site.

  1. user rights of login user (given administrator privilege to sql login user on harddisk drive)
  2. check database path
  3. patch updated as suggested from the Microsoft site.
  4. install SQL2014 on another systems and try same thing but still getting same error. 5.check my database which is i have taken backup.
  5. restore this backup file from the graphical mode and it is restoring successful without any error.

Please help me i have to write a C# program for restore backup database in SQL.

1
c#sqlsql-serversql-server-2008-r2
"The system cannot find the file specified." Do you actually have a file called 123.bak in C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\? - Zohar Peled
C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\123.bak this path is wrong please, provide correct path - Hitesh Kansagara
That path implies that the database you're restoring is from SQL Server 2014. You can't restore a backup from a new version (thus you can't restore a 2014 database on a 2008R2 instance). That isn't, however, the reason for your error, which is likely that the file doesn't exist. I don't, however, suggest giving the SQL Service account administrative powers on your PC. - Larnu
Be very clear - the server executes the restore, so the path needs to be correct from the perspective of the server. Many people assume that they can give a path from the perspective of their client tool (such as SSMS or SQLCMD) and don't realise that that'll only work if the server and the client are on the same machine. - Damien_The_Unbeliever
Open command prompt and type dir and then paste the full name of the backup file, including path. If it exists - and you have access to it - you will see its properties in the dir output. If either is false, there will be an error. - Roger Wolf

1 Answers

-2
votes

Try moving the backup file (*.bak) to your backup folder. My backup location :

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup

And then try to Restore the Database. Hope this helps. Thanks