Sql-server – Database msdb stuck in recovery

msdbrecoverysql server

I encountered a problem while testing recovering backups (Veritas BackUp Exec 2015, just for you to know).

Main Problem is: I couldnt restore the databases – looking on the target Server where I want to restore the DBs, SSMS tells me, 'msdb' database is being recovered.

I've already tried:

RESTORE DATABASE MSDB WITH NORECOVERY | ...WITH RECOVERY

The command was executed (no Errors), but nothing happened (even after restarting SQL Services & a Server restart).

I tried to set it offline, but running

ALTER DATABASE MSDB SET OFFLINE

tells me, that the msdb-Database can't be set as offline.

I need a way to get the msdb database out of recovery. Unless it will be recovered anyway, the current data is useless for me. Is there a way to just force it out of recovery (even if its damaged after executing the command)?

SELECT [state_desc] FROM [sys].[databases] WHERE [name] = N'msdb';

returns "RESTORING".

Best Answer

Converting my comment to answer :

restore database msdb with replace, recovery

Note : if the file location is same, then you dont need to use with move in the restore clause.

Related Solutions

Sql-server – What are the names of SQL Server database statuses 24, 1024 and 65544

The others are combinations of the documented flags, e.g. 24 is 16 and 8.

This is a method for simulating optional parameters and is used in e.g. C iirc, the numbers 1, 2, 4, 8 and so on are structured like that because they correspond to binary values that combined in any unique way create a unique number. The function that accepts them then use bitmasking to extract each individual number. E.g. if you send the value 6 to a function, then we know that this is a combination of 4 and 2, to find this out using a bit mask we would do:

    0110 // 6
AND 0010 // 2
 == 0010

    0110 // 6
AND 0100 // 4
 == 0100

Both statements would be evaluated to true, thereby we have found out the individual values.

Note: In the real world, we would iterate through all values from 1, 2, 4, 8, 16 and so on to the maximum value of an integer (or whatever datatype the parameter was set to).

Sql-server – SQL Server shows database in recovery

_{Community wiki answer originally left as an edit to the question by its author:}

The problem was that the database property AutoClose was set on.

The solution was to set AutoClose to false.

Best Answer

Related Solutions

Sql-server – What are the names of SQL Server database statuses 24, 1024 and 65544

Sql-server – SQL Server shows database in recovery

Related Question