How to recover database in SQL Server

by

In most cases, the db would get online immediately after executing the below query.

use master
restore database << dbname >> with recovery

If you ended up with the below error:

Msg 4333, Level 16, State 1, Line 3 The database cannot be recovered because the log was not restored. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

Execute the below query to restore the DB from the healthy backup.

restore database << dbname >> from disk ='<filepath>\<backup filename>'

Sometimes, it might give you an error message similar to below mentioned error when the DB is in AG or removed abruptly from the AG:

Msg 3148, Level 16, State 3, Line 2 This RESTORE statement is invalid in the current context. The ‘Recover Data Only’ option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline stafte filegroups cannot be specified. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
ALTER DATABASE dbname SET EMERGENCY;
GO
ALTER DATABASE dbname set single_user
GO
DBCC CHECKDB (dbname, REPAIR_REBUILD) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE dbname set multi_user
GO

If the above fails, we can use the below query to recover the Db. However, We would not recommend to use this method for Production environment, probably, we need to restore the database from a valid backup until the point in time recovery.

ALTER DATABASE dbname SET EMERGENCY;
GO
ALTER DATABASE dbname set single_user
GO
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE dbname set multi_use
GO

Caution: Using REPAIR_ALLOW_DATA_LOSS in SQL Server

REPAIR_ALLOW_DATA_LOSS in DBCC CHECKDB can fix severe corruption, but it may permanently delete corrupted data. This option poses a high risk of data loss, so always back up your database before proceeding. It’s recommended to try safer alternatives like REPAIR_REBUILD first or restore from a backup if possible. Use REPAIR_ALLOW_DATA_LOSS only as a last resort when no other option is available.

Leave a Comment