Restore database in restoring stage

Dushyant Patel 1 Reputation point
2021-03-02T05:14:56.583+00:00

I been restoring database from our Production server every morning from last 3 years. Suddenly database in restoring stage since this morning. I've been try to bring online but there no success. Also I drop the database at destination and try manually restoring DB but still giving me a below error.

I've make sure .BAK file is there on source server or production server.

Any idea what's causing this?

RESTORING BACKUP FILE FROM \h4\E$\Database_Backups\X01\X01_backup_2021_03_01_020001_4627658.bak
Msg 3203, Level 16, State 1, Line 37
Read on "\64\E$\Database_Backups\X01\X01_backup_2021_03_01_020001_4627658.bak" failed: 2(The system cannot find the file specified.)
Msg 3013, Level 16, State 1, Line 37
RESTORE DATABASE is terminating abnormally.
Msg 5052, Level 16, State 1, Line 46
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 46
ALTER DATABASE statement failed.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,811 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,001 Reputation points
    2021-03-02T07:06:19.817+00:00

    ALTER DATABASE is not permitted while a database is in the Restoring state.

    You can stop the restore process using WITH RECOVERY parameter like 

    RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;

    see RESTORE Statements (Transact-SQL)

    RESTORING BACKUP FILE FROM \h4\E$\Database_Backups

    You access the backup file over an admin share E$? Has this really ever worked?

    0 comments
  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-03-03T06:19:16.797+00:00

    Hi @Dushyant Patel ,

    > RESTORING BACKUP FILE FROM \h4\E$\Database_Backups\X01\X01_backup_2021_03_01_020001_4627658.bak
    >Msg 3203, Level 16, State 1, Line 37
    >Read on "\64\E$\Database_Backups\X01\X01_backup_2021_03_01_020001_4627658.bak" failed: 2(The system cannot find the file specified.)

    Please make sure the .bak file location is correct. Or this is just a mistake when you post the issue. Make sure the share folder is existed.

    Did your SQL server service account has full Control permissions to the shared folder? Did this is a restore job? If so, the SQL server agent service account also need to have read and write permission to this shared folder.

    If it is still not work, please check your SQL server error log to find more related message around the error. And share us the information.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.