-
Title
Restore large database RECOVERY -
Description
Large database restore (Full + Diff + mutliple tlogs) with RECOVERY fails.
Errors may include:
The database 'database_name' is marked RESTORING and is in a state that does not allow recovery to be run.Query execution failed.
Message: Database 'database_name' cannot be opened. It is in the middle of a restore..SQL Server has returned a failure message to LiteSpeed for SQL Server which has prevented the operation from succeeding.
The following message is not a LiteSpeed for SQL Server message. Please refer to SQL Server books online or Microsoft technical support for a solution:
RESTORE DATABASE is terminating abnormally.
Exclusive access could not be obtained because the database is in use. -
Cause
Database has not completed the final restore when SQL Server attempts to bring into Full Recovery.
Note: This is a SQL Server error, not LiteSpeed. -
Resolution
WORKAROUND:
If we script them all together, we could insert the WAIT. Since the last command is truly just SQL Server (not LiteSpeed), we could use RESTORE DATABASE and call it directly to change the status of the database.xp_restore_log @database = ‘database_name’,
@filename = '\\backups\Database_name\logs\Database_name_log2.lstrn',
@with = 'norecovery'
go
WAITFOR DELAY '00:05:00'
RESTORE DATABASE ‘database_name’
WITH RECOVERY