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

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.

Answer 1

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

Answer 2

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.