5

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.

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.

Improve this question
0

3 Answers 3

Reset to default
5

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
3

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
0
1

You need the additional command:

RESTORE DATABASE test
WITH RECOVERY;

It appears that RECOVERY is ignored in the RESTORE LOG command, and it needs an additional command to confirm that the restore has been finished.

Answer originally left by the asker in an edit to the question.

Improve this answer