locked
Error when trying to restore a database RRS feed

  • Question

  • Hello All,

    I am trying to restore a database. I have saved the back up file in a different server and I have full permissions to that server.When I am trying to restore from database engine I am getting the following error

    Cannot open backup device 'E:\Backup folder\POSTEOM_Service.bak'. Operating system error 3(The system cannot find the path specified.).

    RESTORE HEADERONLY is terminating abnormally

    However, when I open sql and try to do the restore I can’t see the .bak in this folder ,If I do explore I can see them but if I try and do a restore I can’t. Our SQL Server version is 2005.

    Please help. Any suggestions would hel me.

    Thanks

    Monday, May 21, 2012 9:53 PM

Answers

  • All I mean, choose the install version on your computer, sorry, if it confused you ;) 

    you may have different version of SQL (SQL Server 2005/2008/2008 R2) installed on your computer, select the appropriate version.

    I just need to right click on SQL Server (MSSQLSERVER) and click on properties and go to security tab and then I will get to know which account is responsible in running SQL Service and that account should have read write permission to the shared back up folder..Is this right?

    Ans: yes, on backup share, the database engine account should have permission.

    An additional link: http://sudeeptaganguly.wordpress.com/2010/03/27/backup-a-database-from-one-server-and-restore-the-same-on-the-other-server/


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )


    • Edited by Sudeepta Ganguly Tuesday, May 22, 2012 2:45 PM additional info
    • Marked as answer by Maggie Luo Tuesday, May 29, 2012 2:42 AM
    Tuesday, May 22, 2012 2:33 PM
  • Thank you all. I am using SA account.That should have read write permissions by default right?...

    Also I triued copying the back up files to default location E:\Program Files\Microsoft SQL Server\<INSTANCE NAME>\MSSQL\Backup ..I am getting this error

    The media family on device 'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\POSTEOM_Service.bak' is incorrectly formed. SQL Server cannot process this media family.

    RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

    Please assist.

    Thanks

    As I said you need to make sure that the account that is running your SQL service has full access to the bak file in order to restore. SA has no direct access to resources outside of SQL such as the filesystem.

    In regards to the incorrectly formed error. Where did this bak file come from? Was the source system where the bak file created the same SQL version as your SQL installation where you are trying to restore to? You cannot restore a backup from SQL 2008 R2 to SQL 2005/SQL 2008 for example as there is no downgrade options available. Once a database is created there is an internal version number for the database and SQL cannot downgrade from that version.

    The bak file could also be corrupt - how was the bak file transferred to you?


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Maggie Luo Tuesday, May 29, 2012 2:41 AM
    Tuesday, May 22, 2012 10:26 PM

All replies

  • Hi,

    What permissions does the service account that is running the SQL Server service have to that bak file and path? Try moving the bak file to the default backup location which would be E:\Program Files\Microsoft SQL Server\<INSTANCE NAME>\MSSQL\Backup and then run the restore.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Monday, May 21, 2012 10:07 PM
  • Hi Sri,

    If you are trying to restore the backup file from different machine, then please use like below

    restore database from disk = '\\remotemachinename\Backup folder\POSTEOM_Service.bak'

    "Backup folder" should be shared and full permissions to sql server running account and urs account to ;)

    I m sure it works :)

    Thanks,

    restore database from disk = '\\satish\Backup folder\POSTEOM_Service.bak'

    satish -- remote machine name

    Backup folder -- shared folder


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    • Proposed as answer by Maggie Luo Tuesday, May 22, 2012 9:52 AM
    Tuesday, May 22, 2012 4:05 AM
  • Hello Sri,

    Make sure the Database Engine Service Account has Read-Write permission on the given share ('\\ServerName).

    Let us know, if this helps :)


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Tuesday, May 22, 2012 7:00 AM
  • Thank you all. I am using SA account.That should have read write permissions by default right?...

    Also I triued copying the back up files to default location E:\Program Files\Microsoft SQL Server\<INSTANCE NAME>\MSSQL\Backup ..I am getting this error

    The media family on device 'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\POSTEOM_Service.bak' is incorrectly formed. SQL Server cannot process this media family.

    RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

    Please assist.

    Thanks

    Tuesday, May 22, 2012 1:48 PM
  • No, the SQL Server Database Engine Service Account should have read and write permission on the backup share.

    Click on Start Menu, go to All Programs --> Microsoft SQL Server 2008 R2 --> Configuration Tools --> SQL Server Configuration Manager. (Open the available SQL Server folder on your machine) . As shown below, the account running the SQL server service should have read & write permission on your backup share.

    Hope, this may help :)


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Tuesday, May 22, 2012 2:01 PM
  • Thank you Sudeepta. This one I need to check in my local right?...not in remote..because I am trying to restore the db from local but the back up files are in remote...I didn't get this part  Open the available SQL Server folder on your machine...What does that mean?

    I just need to right click on SQL Server (MSSQLSERVER) and click on properties and go to security tab and then I will get to know which account is responsible in running SQL Service and that account should have read write permission to the shared back up folder..Is this right?

    Please correct me..

    Thank You,

    Tuesday, May 22, 2012 2:10 PM
  • All I mean, choose the install version on your computer, sorry, if it confused you ;) 

    you may have different version of SQL (SQL Server 2005/2008/2008 R2) installed on your computer, select the appropriate version.

    I just need to right click on SQL Server (MSSQLSERVER) and click on properties and go to security tab and then I will get to know which account is responsible in running SQL Service and that account should have read write permission to the shared back up folder..Is this right?

    Ans: yes, on backup share, the database engine account should have permission.

    An additional link: http://sudeeptaganguly.wordpress.com/2010/03/27/backup-a-database-from-one-server-and-restore-the-same-on-the-other-server/


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )


    • Edited by Sudeepta Ganguly Tuesday, May 22, 2012 2:45 PM additional info
    • Marked as answer by Maggie Luo Tuesday, May 29, 2012 2:42 AM
    Tuesday, May 22, 2012 2:33 PM
  • Thank you all. I am using SA account.That should have read write permissions by default right?...

    Also I triued copying the back up files to default location E:\Program Files\Microsoft SQL Server\<INSTANCE NAME>\MSSQL\Backup ..I am getting this error

    The media family on device 'E:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\POSTEOM_Service.bak' is incorrectly formed. SQL Server cannot process this media family.

    RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

    Please assist.

    Thanks

    As I said you need to make sure that the account that is running your SQL service has full access to the bak file in order to restore. SA has no direct access to resources outside of SQL such as the filesystem.

    In regards to the incorrectly formed error. Where did this bak file come from? Was the source system where the bak file created the same SQL version as your SQL installation where you are trying to restore to? You cannot restore a backup from SQL 2008 R2 to SQL 2005/SQL 2008 for example as there is no downgrade options available. Once a database is created there is an internal version number for the database and SQL cannot downgrade from that version.

    The bak file could also be corrupt - how was the bak file transferred to you?


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    • Marked as answer by Maggie Luo Tuesday, May 29, 2012 2:41 AM
    Tuesday, May 22, 2012 10:26 PM
  • please check the SQL Server versions of that backup file.

    I think u r trying to restore higher version of backup file to lower version of SQL Server

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Wednesday, May 23, 2012 11:35 AM