Restore SQL Server 2012 DB with redirection fails

[mastaap]

Hello,

For testing I try to restore a SQL 2012 std. DB with redirection to a SQL 2012 express.

Every time it get the following error:

The database cannot be recovered because the log was not restored. V-79-57344-38051 - An error occurred while attempting to recover the database.

The files are recovered, the DB is also there but in the restoring state.

Thanks for your help.

Regards,

Matthias

[VJware]

If the dB is in "restoring" state, then either it is waiting for additional (log) restores or there is a possible corruption...

EDIT*

I don't think you can redirect from standard to express...Try restoring to another standard sql setup.

[mastaap]

I have tested this with different DB's from different backup sets.

I choose the latest backup set and I don’t mark the "leave the database non-operational for additional transaction logs"

When I restore to original location, it works.

EDIT*

OK I will test also to std.

[mastaap]

Now I have run a test to a SQL 2012 std. no success.

Same error.

But in the Log i found the following:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL\MSSQL\DATA\[DBName].mdf for file number 1.  OS error: 5(Access is denied.).

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL\MSSQL\DATA\[DBName]_log.LDF for file number 2.  OS error: 5(Access is denied.)

I checked the file permission, and see an account with the SID. I think this is the service account from the source SQL Server.

When I set permissions for the destination SQL Server account, nothing happen, because the DB is even in restoring state.

Any ideas?

[VJware]

Create a new, blank dB at the target server. Target the backup job to this dB, else if preferring to use the same database, force it out of recovering mode.

Ensure the Backup Exec logon account has appropriate SQL permissions such as sysadmin, db_datareader, db_datawriter etc on the target ssql server/instance.

You might need to enable named pipes/tcp/ip as well using cliconfg.

[mastaap · Accepted solution]

Now I have found the problem.

I used for the SQL logon account the SA user. It seems this is not supported.

Now I used the Backup Exec logon account and set the permissions on the SQL server.

Now the restore works fine!

Thanks for your help.

Regards,

Matthias