DB restore stuck

[Former Member]

Dear Experts,

We have an ECC 6.0 with MSS Sql Server.

After new installation we started recovery with "RESTORE WITH NORECOVERY" option using MSS Management Studio.

But as restore is not progressing I checked in ERRORLOG and SQLAGENT.OUT files and found

2010-02-16 13:20:01.68 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
2010-02-16 13:40:00.82 Logon       Error: 18456, Severity: 14, State: 16.
2010-02-16 13:40:00.82 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
2010-02-16 14:00:00.90 Logon       Error: 18456, Severity: 14, State: 16.
2010-02-16 14:00:00.90 Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
2010-02-16 14:06:10.96 Logon       Error: 18456, Severity: 14, State: 16.

Then I stopped restore process and try to grant system user required authorization.

Now when I try to start restore once again I found that the following message

The database 'TD1' is marked RESTORING and is in a state that does not allow recovery to be run.

Restore is stuck.

Please let me know how to resolve the issue.

Regards,

Paertha

[Former Member]

This message was moderated.

[Former Member]

Issue resolved

[Former Member]

Dear Expert,

Hard luck if I execute

RESTORE DATABASE TD1 WITH RECOVERY;

Results

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

If I try to recover Log then it is showing last restore was not successful.

And if I try to do the RESTORE WITH RECOVERY option using MSS Management Studio , then results

2010-02-16 14:11:28 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2010-02-16 14:20:02 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000] 
2010-02-16 14:20:02 - ! [298] SQLServer Error: 4060, Cannot open database "TD1" requested by the login. The login failed. [SQLSTATE 42000] 
2010-02-16 14:40:00 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000] 
2010-02-16 14:40:00 - ! [298] SQLServer Error: 4060, Cannot open database "TD1" requested by the login. The login failed. [SQLSTATE 42000] 
2010-02-16 15:00:00 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000] 
2010-02-16 15:00:00 - ! [298] SQLServer Error: 4060, Cannot open database "TD1" requested by the login. The login failed. [SQLSTATE 42000] 
2010-02-16 15:20:00 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000] 
2010-02-16 15:20:00 - ! [298] SQLServer Error: 4060, Cannot open database "TD1" requested by the login. The login failed. [SQLSTATE 42000] 
2010-02-16 15:40:00 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000] 
2010-02-16 15:40:00 - ! [298] SQLServer Error: 4060, Cannot open database "TD1" requested by the login. The login failed. [SQLSTATE 42000] 
2010-02-16 16:00:00 - ! [298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000] 
2010-02-16 16:00:00 - ! [298] SQLServer Error: 4060, Cannot open database "TD1" requested by the login. The login failed. [SQLSTATE 42000]

Please help me.

Reagrsd,

partha

[Former Member]

Hi,

Please make sure that you are using "Mixed mode" (Windows and SQL) Authentication..

Log is clearly indicating that Operating system user is unable to login to DB.

Also check the Operating group membership for SQL DBA groups.

Also try the below thing

1. start the SQL Enterprise Manager.

2. Go to \Servername\Security\Logins

3. Grant access to the BUILTIN\Administrators group

Regards.

Rajesh Narkhede

[former_member524429]

Hi,

Have tried to restart the whole DB Restore process from the scratch? You can detach the database, remove the MDF files, and start the restore from scratch.

Regards,

Bhavik G. Shroff

[Former Member]

Dear Bhabik,

Can you kindly elaborate the procedure you mentioned. Actually I am not very proficient in MSSL DBA.

Thank you ver y much.

Regards,

Partha

[former_member524429]

Hi,

If you perform RESTORE with the option" WITH NORECOVERY", the database will be left in Restoring state and It will be waiting for more transaction logs to be restored. As its already well described there while choosing the Restore Options.

If required, Change the restore mode by executing following command and re-perform recovery.

RESTORE DATABASE TD1 WITH RECOVERY

Regards,

Bhavik G. Shroff