1

I am trying to restore several databases and I have no clue why some of them fail with the following error:

Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '1244(The operation being requested was not performed because the user has not been authenticated.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'K:\Logs\MyDatabase.ldf'.

Msg 3156, Level 16, State 8, Line 1
File 'MyDatabase_log' cannot be restored to 'K:\Logs\MyDatabase.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.

This is the SQL statement that I'm using

RESTORE DATABASE [MyDatabase]
FROM DISK = 'K:\Backups\MyDatabase_backup_2014_10_14_055006_4726051.bak'
WITH REPLACE,
MOVE 'MyDatabase_data' TO 'K:\Data\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'K:\Logs\MyDatabase.ldf',
STATS = 10

As I said, some of them will work perfectly fine, while others will give me an error similar to the above. Unfortunately I can't find a pattern for the ones that fail / succeed.

If there was some sort of authentication issue, why does my SQL work on some but not on others? What am I missing?

LE: I had to give this user the sysadmin role for a completely unrelated issue. This may have also fixed this. I'll investigate and come back.

1

As part of a different issue I had to give my login the sysadmin role. I haven't seen the problem I described above since then.

Yes, it may work now, but I am not happy with this solution, partially because I don't quite understand what was going on.

Later edit: As pointed out by Thomas in a comment, here's what's going on:

If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

1

I received this error because the log file (.ldf) existed in the folder that I was restoring to.

Deleting the orphan file resolved the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.