question

DushyantPatel-2244 avatar image
0 Votes"
DushyantPatel-2244 asked ·

Restore database in restoring stage

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-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

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?




·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ·

Hi @DushyantPatel-2244,

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.



· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This isn't a permissions problem, so no need to check those things. Permission problems would give OS return code 5, and in this case we got return code 2, which states that the file doesn't exist.

Also, SQL Server do not need permission on the backup file, regardless of whether this is a scheduled job or not. Agent permissions are irrelevant here. It is the database engine that accesses the database file, Agent never touches the file.

0 Votes 0 ·