SQL Server - Is there any way to recover a corrupted database without backup?

Question

I am simulating a database corruption for my own practice. What I did was editing the .LDF file and messing it up and when I run SQL server, the database is in "Recovery Pending" mode, meaning it has been corrupted.

Without any backup to be restored or without replacing the backup .LDF file, is there any way I could recover my database?

Note: I am not interested in using any third party tools/software.

Would appreciate any suggestion.

score 2 · Accepted Answer · answered Oct 16 '18 at 08:43

2

Try to use these commands on the master database. It will reset the state of the database so you can work with it. This will put it into SINGLE USER MODE and ROLLBACK pending changes. After repair it will allow MULTIUSER AGAIN.

EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

answered Oct 16 '18 at 08:43

MartinS

751
3
12
27

What is purpose of this? – Daredevil Oct 16 '18 at 09:21
Sorry i don't understand your question. The database is in wrong state because there are some pending transactions to be commited - they can't be commited because of LDF corruption. In order to work with database you need to set it to SINGLE USER mode so nobody can work with it, just you. This will also rollback pending transactions. Then you can use DBCC CHECKDB. This tool checks database for errors. If there si any data corruption, data will be lost. The you can set db to MULTI USER again so anyone can connect. – MartinS Oct 16 '18 at 09:54

score 1 · Answer 2 · edited Jun 03 '21 at 10:40

You can also bring the database back online without the .ldf file (as its corrupted or even without having the .ldf file at all). Based on your question I am assuming that you had a clead DB shutdown before you have corrupted the log (if not there is another way, just drop an update request in comment if needed). To check that just run:

SELECT [name], [state_desc], [is_cleanly_shutdown] FROM sys.databases;

Then if [is_cleanly_shutdown] = 1 and you had just a single log file for your database (mostly this is the case):

CREATE DATABASE [Your_DB_Name] ON
(FILENAME = N'D:\full_file_path\db.mdf')
FOR ATTACH;

If you had multiple log files then run:

CREATE DATABASE [DB_Name] ON
(FILENAME = N'D:\full_file_path\db.mdf')
FOR ATTACH_REBUILD_LOG;

In both scenarios you will create a new log file (just single log file) which will be just 0.5MB is size so you have to modify it for your needs:

ALTER DATABASE [Your_DB_Name] MODIFY FILE
(
 NAME = N'DB_Name_log'
,SIZE = 100 {KB | MB | GB | TB | UNLIMITED }
,FILEGROWTH = 20 {KB | MB | GB | TB | % })
);

Update:

As stated here

You need a third-party tool to do this. The SQL recovery tool recovers Tables, Keys, Indexes, Views, Triggers, Stored Procedures, Rules, User Defined Functions, and more. In addition, the MS SQL database recovery tool supports recovery of XML indexes and data types, column set property, sparse columns, and file stream data types.

SQL Server - Is there any way to recover a corrupted database without backup?

2 Answers2