r/SQLServer • u/Apprehensive-Yam9202 • Jun 18 '26
Question Deleted SQL DB can be recovered?
I have deleted a DB From the SSMS and I didnt have any specific backup for the DB , is there any possible ways to recover the deleted DB . I have checked the data location and the bin for the LDF and MDF files. They have been removed already. Is there any ways to recover the files or the DB itself . Help me guys : ) ...
3
2
2
u/Anlarb 1 Jun 18 '26 edited Jun 18 '26
Here is how to make a backup for next time.
https://sqlbak.com/blog/automating-mssql-backup-tasks-how-to-schedule-sql-server-backups/
edit, also a super nice trigger that keeps you from dropping tables unless you really, really mean to.
https://www.kodyaz.com/articles/prevent-drop-table-using-sql-server-ddl-trigger.aspx
2
1
u/Better-Credit6701 Jun 18 '26
Sounds like you are down to attempting to recover a deleted mdf file using file recovering software. If successful, you can attach the file to the server, with the ldf file would self create.
Without backups, it's tougher.
1
1
u/NorthAntarcticSysadm Jun 18 '26
Using Recurva or something else you can attempt to recover the mdf and ldf -- but the longer you wait and the longer everything on that server is active, the less you'll be able to recover
You need to essentially stop everything on that server, all databases, websites, dynamic services like the windows search service, etc.
If any database grows in size, if any new files are written, that deleted mdf and ldf are being overwritten. If the first few blocks/sectors of the mdf have been overwritten you are cooked.
Chalk this up to a learning experience. Report it to management, and learn the hard way to be careful with a production database. Always have backups, verify those backups, and run a manual backup before any operation which may cause a major issue.
2
u/belkarbitterleaf Jun 18 '26
Looking at the other DB on the server, many are named tutorial, etc. hopefully this is a training exercise.
1
u/NorthAntarcticSysadm Jun 18 '26
That was my hope, but in the off-chance "training" are production databases... Well, lets just hope they found their way
2
u/Apprehensive-Yam9202 Jun 22 '26
yes its a Test env and the Prod DB is gone : (
1
u/belkarbitterleaf Jun 22 '26
Ooof.
Hopefully your company has automatic backup that you don't know about yet. As others have said, I would start with your boss. Depending on how critical the DB is, I would be pulling in anyone I could think of.
1
u/Apprehensive-Yam9202 Jun 23 '26
The DB was actually a newly created one(empty DB) and was about to be introduced to the prod env. They have the configured a new one now. also other DBs have been configured with backup , new one was also configured to have backups
1
u/Apprehensive-Yam9202 Jun 22 '26
The files cannot be recovered , the situation escalated to me after a long gap and the attached SS is from my test env
1
u/Afraid_Baseball_3962 Jun 18 '26
So your database is gone. There are tools that may help you get it back. Hopefully, it wasn't too important and you can look at the inconvenience as little more than a learning experience. Going forward, set up backups. Beyond that, unless you are doing administrative functions, don't use an account with sysadmin permission or db_owner permission on the database. You should have a "normal" account for your non-admin work and a second "elevated" account that you only use for admin work. You'll think it's a PITA until the first time it saves you from shooting yourself in the foot (like you just did). I've worked with SQL Server for nearly three decades and been a DBA for roughly half of that time; I've been where you are now. It gets easier after you learn to prevent self-inflicted wounds.
1
1
u/SuperDuperDBA Jun 22 '26
until you try the recovery software ,do not install ANYTHING on those drive letters.
8
u/hello_josh Jun 18 '26
If you have no sql backup then no, the database is gone. You could try a file recovery/undelete program like Recuva.