Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Restoring SQL backup fails - Access is denied

Solved. See my answer (but first see my second edit to the question).

I'm trying to restore a backup for a database from one computer on another - thereby copying the db, but I get this message:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server...'. (Microsoft.SqlServer.SmoExtended)

Why is this? I can create new databases, so why not restore? Is it because it's from another computer? (I read that that's actually a usual way to copy a db so this shouldn't be the problem.)

I don't have much experience with this so don't rule out any obvious explanation.

EDIT :

I can 'restore' it using the administrator user account to the administrator's instance of SQL Server (I have two - one for the administrator, and one for the regular account.) but can't do it from either account to the regular user's instance of SQL Server.

EDIT 2 :

It seems that there are already existing files with the backup's files names (even though I changed the existing db's name). I'm working on that now (Trying, still unsuccessfully, to restore to different file names).

like image 219
ispiro Avatar asked May 21 '13 18:05

ispiro


People also ask

What permissions are required for restore SQL database?

Permissions. If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to successfully restore the database. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner ( dbo ) of the database.

How do I restore a SQL backup file?

Restore a backup Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Right-click the Databases node in Object Explorer and select Restore Database.... Select Device:, and then select the ellipses (...) to locate your backup file. Select Add and navigate to where your .


1 Answers

The solution was to make sure that the database was created in the MSSQL11.MSSQLSERVER folder (as opposed to the MSSQL10_50.SQLEXPRESS folder). Then SSMS succeeded in 'restoring' the database.

like image 194
ispiro Avatar answered Oct 01 '22 08:10

ispiro