2

I have executed these two commands:

USE master
GO

RESTORE DATABASE test
FROM Kingston
WITH NORECOVERY
GO

USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;
GO

But now I get this error while accessing the database with USE test GO:

Database 'test' cannot be opened. It is in the middle of a restore.

When it clearly isn't. Any suggestions how to fix it? So far found out that RECOVERY helps, but as you can see it doesn't.

Edit

Changing NORECOVERY to RECOVERY doesn't help, because then I'm unable to execute 2nd command (throws error), and I need that log at specific moment.

Edit

I have found the solution. You need additional command:

RESTORE DATABASE test
WITH RECOVERY
GO

It appears that RECOVERY is ignored in RESTORE LOG command, and it needs additional command to confirm that restoring has been finished. I appreciate the help though.

  • Have you had a look at his question and answer? stackoverflow.com/questions/520967/… Could the solution be to change the WITH NORECOVERY to WITH RECOVERY? – Kevin Brydon May 13 at 12:23
  • @Larnu 2nd command is with recovery. When using recovery in 1st im unable to recover log at specific moment. – AdmiralGiraffe May 13 at 12:25
  • @KevinBrydon changing norecovery to recovery gives new error while executing 2nd command. The log or differential backup cannot be restored because no files are ready to rollforward. RESTORE LOG is terminating abnormally. – AdmiralGiraffe May 13 at 12:26
  • The question, to me, implied you're getting the error at the RESTORE LOG test statement, are you saying both commands run successfully and then you are trying to do something else you haven't told us? What is it you're doing to get said error then? – Larnu May 13 at 12:26
  • 1
    @AdmiralGiraffe you should probably put the "I'm trying to run a SELECT statement etc' in the question – Kevin Brydon May 13 at 12:31
3

This

USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;

Will not perform recovery if the STOPAT time is outside the range of times in the log backup. This allows you to specify the STOPAT for multiple log backups, and the database will only run recovery when you've applied the log backup containing the target time.

From the docs:

The RECOVERY and STOPAT options. If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.

Restore a SQL Server Database to a Point in Time (Full Recovery Model)

| improve this answer | |
0

This error occurs because you used NORECOVERY mode for restoration and it does not allow the use of the database. Now you should use WITH RECOVERY MODE to restore the database so that you can easily access the data.

To rectify this common error, you need to use the WITH RECOVERY option. Follow these steps to do the same:

- T-SQL Script For Restoring Database WITH RECOVERY :

RESTORE DATABASE Databasename FROM DISK = 4'C:\databasename.BAK'

WITH RECOVERY
  • Recover a Database from the ‘Restoring’ State

If the database is in the restoring state and unavailable to users, run the command to make it accessible to users.

RESTORE DATABASE Databasename WITH RECOVERY
  • Restore Multiple Backups using WITH RECOVERY option

The user can use the NORECOVERY option to restore the database in case if the user has multiple backups except in the last. But for the last backup, the user must use WITH RECOVERY option to restore all transaction logs and put the database online.

RESTORE DATABASE databasename FROM DISK = 'C:\Databasename.BAK'

WITH NORECOVERY

GO

RESTORE LOG databasename FROM DISK = 'C:\Databasename.TRN'

WITH RECOVERY

GO
| improve this answer | |
  • Last option is literally what i'm doing and it doesn't work. – AdmiralGiraffe May 13 at 12:33
  • @AdmiralGiraffe did you tried to store using SSMS? – Sandra Guilep Zouaoui Zandeh May 13 at 12:35
  • It does work via SSMS interface, but i would also like to understand how it works via TSQL. – AdmiralGiraffe May 13 at 12:36
  • The code you're writing is TSQL. If it's not working try to use kernel for SQL database recovery – Sandra Guilep Zouaoui Zandeh May 13 at 12:43

Your Answer

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