0

I have opened SSMS today, and went to open my database ADMINISTRATOR (in the picture) but this option is unavailable:

enter image description here

What's this problem, how can I cancel this restore?

I have just added a new database, I didn't perform any restore operation for this database.

0
3

When you shutdown/restart your computer/server, the service for the SQL Server Express instance, will be triggered to stop too. While the service is shutting down the SQL Server Database Engine will try to shut down the databases gracefully, in order to ensure they can be restarted in a timely fashion when the system comes back up.

If the system was forced to shutdown/restart before the database was able to be brought to a consistent state, then the recovery will be performed automatically, when the instance is brought back up. The SQL Server Database Engine will find the corespondeing *.mdf and *.ndf file(s) and together with the existing *.ldf file try and bring the database back online.

If for some reason the database was in the middle of (e.g.) a database reindex task or reorganize then there will be a lot of outstanding transactions to either roll-forward (COMMIT) or roll-back (ROLLBACK). Until this procedure has finished, the database will be marked as RECOVERING.

If for some reason the database is unable to brought to a consistent state, then the database will switch to the RECOVERY PENDING... state.

Reference: Database States (Microsoft | SQL Docs)

Seeing as your database is neither in the RECOVERING nor in the RECOVERY PEDNING state, then we can conclude that an actual RESTORE DATABASE ... has been triggered, which results in the database displaying the state RESTORING... which should be identical to the French expression RESTAURATION....

You can double-check the state of the database by issuing the command:

SELECT sdb.name, sdb.state_desc 
FROM sys.databases AS sdb 
WHERE sdb.name = 'ADMINISTRATEUR';

Now if for some reason the database does not display the same state in the SELECT statement, then you might just have a simple Refresh issue.

Solution: Right-click the Bases de données in SSMS and click on Refresh

If the database state returned from the query is the same, then have a look at the ERRORLOG file of your SQL Server instance. There should be an entry, when the RESTORE started.

If not have a look at the restore history in the msdb database:

SELECT restore_date,
       destination_database_name AS NewDBName,
       destination_phys_name AS DestinationPath,
       database_name AS OrigDBName,
       server_name AS ServreName,
       physical_device_name AS PhysicalSourceName,
       rh.[user_name] AS UserName,
       'EOR' AS EOR
FROM   msdb.dbo.restorehistory rh
       JOIN msdb.dbo.restorefile rf
            ON  rh.restore_history_id = rf.restore_history_id
       JOIN msdb.dbo.backupfile bf
            ON  bf.backup_set_id = rh.backup_set_id
       JOIN msdb.dbo.backupset bs
            ON  bs.backup_set_id = rh.backup_set_id
       JOIN msdb.dbo.backupmediafamily bmf
            ON  bmf.media_set_id = rh.backup_set_id
            
ORDER BY 2,1

This will show you when the restore started and (if lucky) the user that started the restore.

Solution: End the restore of the database by issuing the command pointed out by Dan Guzman in his comment:

RESTORE DATABASE ADMINISTRATEUR WITH RECOVERY;

It could be that a previous RESTORE DATABASE .... WITH NO_REECOVERY was issued, which will not finalize the RESTORE. Issuing the command stops the ability to further RESTORE DATABASE ... with additional Transaction Log backups.

4
  • I think the cause of this problem is yesterday the system was forced to shutdown, which results today in the database displaying the state RESTORING ... after executing the restore command RESTORE DATABASE ADMINISTRATEUR WITH RECOVERY; is resolved my problem – developer May 4 at 11:29
  • A database doesn't enter the restoring state based on a shutdown. Somebody or something executed a restore command. I understand that this might not feel important at this point, but the distinction is IMO important. I.e., the knowledge that a restore was attempted by something or somebody. – Tibor Karaszi May 4 at 12:17
  • @TiborKaraszi That is true and was never suggested in my answer. Somehow, OPs database was restored, but that could be determined with the execution of the statement I provided or by consulting the ERRORLOG. – John K. N. May 4 at 12:28
  • 1
    @John, I wasn't referring to your answer, but rather developer's comment immediately above my first comment - where an incorrect assumption was made. I just wanted to emphasize to developer that a restore was performed. Sorry for being unclear... :-) – Tibor Karaszi May 4 at 12:44
1

It is not normal for a newly created database to be recovering status. Try executing the command:

RESTORE DATABASE ADMINISTRATEUR WITH RECOVERY;

...from a query window in the context of the master database.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.