Number of times, while working as a DBA many accidents happen. Most common of them is when a database is restored without checking if a backup is available or not. As a thumb rule – a DBA must take backups of everything before performing any action. At any point of time, I must be in position of taking things back to the previous state. I know its difficult to achieve this all times.
Lets talk about the scenario when accidentally you start restoring a database and later you realize that it wasn’t required. Now, you start to look for the backup of victim database so that you can at least bring the database to the state where it was at the time of taking last backup. And UNFORTUNATELY – the backup isn’t available. Now this is a road block and panic situation. Isnt it? I am trying to list some steps that can be taken. Remember, all situations are different and its hard to generalize any solution. However, try these things
Keep communicating to your stakeholders about the situation. Remember, this is a critical situation and communication is the key. Get consent from you stakeholders that there will be data loss for sure.
- Look for any other available backup of victim database.
- You may check if you have received any request in past which asked you to keep the backup at safe location
- You may check in your DR Server/Network if the backup is available.
- You may check in your other environment if you are copying or restoring or shipping this database.
If nothing works, there is no harm trying this solution. I have not done this personally as of now but this works in theory. However, I am going to try this for sure soon and will keep you all posted.
- Detach the database from SQL server instance
- Copy the corrupt data and transaction log files to a different location
- Make sure to delete the original corrupt files
- Create a new database with the exact same file layout, names and file sizes as the detached corrupt database
- Stop the SOL Server instance
- Overwrite the data and transaction log files of the newly created database with the original corrupt files
- Start the SOL Server instance
Now that the corrupt databases is back online, it’s time to recover what data you can. To do so:
- Execute ALTER DATABASE your_database_name SET EMERGENCY; to put the corrupt SQL database into EMERGENCY mode
- In order to enable recovery you need to put the database into SINGLE_USER mode as well. To do so, execute ALTER DATABASE your_database_name SET SINGLE_USER;
- Execute DBCC CHECKDB (your_database_name, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
This allows that you have database online now. Depending on the situation – you should be able to see some of the data. Play around and see if this makes your little easy in this tough situation.