Sql-server – Backup and restore database – occured system error 5

sql serversql-server-2008-r2

In my work I made backup of database and I receive file .bak. Now I would like restore this database in my machine. But I get the following error:

Cannot open backup device 'C:\Program Files…\MSSQL10_50.SQLEXPRESS…\file.bak'.

Operating
system error 5(failed to retrieve text for this error. Reason: 15105).

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server,
Error: 3201)

Of course I searched the Internet, for example:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/805661f1-cf23-4230-ae31-d9cc16505359/cant-restore-backup-from-2005-to-2008r2?forum=sqlexpress

But all users have permissions to all folders so I'm really confused. What is the problem?

Best Answer

Sounds like a permissions error is being masked. You have to remember that the user that needs access to the folder where the backup resides (and where you restore the backup, if using WITH MOVE explicitly) is the SQL Server service account, not regular users.

If the folder listed in the error message does not represent the same instance where you're trying to restore, it's unlikely the service has access to the folder. Try moving the .bak file to the current instance's data or log folder, where SQL Server will certainly have adequate permissions.

Related Solutions

Sql-server – Restore a Database to a New database (T-SQL)

I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.

If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.

One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.

Sql-server – Backup SQL Server To NAS

Your syntax is wrong for adding a backup device.

When you create a backup device in SQL Server you are actually creating the "bak" file as a media set.

Along with the suggestion from Bob that you should be using the full UNC path, your syntax should be changed to something like this:

IF  EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N'NAS_YEDEK_DB')
EXEC master.dbo.sp_dropdevice @logicalname = N'NAS_YEDEK_DB'
GO

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'NAS_YEDEK_DB', @physicalname = N'\\10.1.1.x\yedek_db\YEDEK_DB_backup.bak'
GO

Best Answer

Related Solutions

Sql-server – Restore a Database to a New database (T-SQL)

Sql-server – Backup SQL Server To NAS

Related Question