Cascade delete in an sql table relation may sometime lead to serious data losses. Should this occur, you can make use of the log file (LDF) to review the DELETE statements that took place and hopefully manage to recover your lost data.
When you realize that an accidental delete has occurred, stop using the database. If you can, stop the sql server instance and copy the mdf and ldf files of the affected database. In my case, I was called a couple hours later on but no harm was done as there was no backup process and the ldf file was intact.
You will need to parse the ldf entries to detect the deleted entries. I found a blog post which offers a stored procedure to do that, but unfortunately this didn't work for me as I was getting an error on the compatibility level (which was wrong). This is why I ended up using SysTools SQL Log Analyzer which as a demo can show you the log file entries. If you want to export the entries, you will have to buy the product.
This tool works with offline files. This is why I mentioned above to copy the mdf and ldf files. It reads the log and shows the INSERT,UPDATE and DELETE statements on each and every table you have. The beauty of this tool is that is also show the deleted row.
When the analysis of the ldf file finished, I selected all entries in the tree on the left and selected export. Then I used the "Sql server database" option and exported the deleted only rows to a new database which was created by the tool. Note that I also specified a data filter in order to minimize the number of data.
After a couple of hours (depending on the size of lost data) you will end up with a new database that has similar tables to the original one and each table will contain the deleted row.
After that, head to the bulk insert msdn article that mentions how to export and import the data using the bcp command. The process is fairly simple as you have to issue the following commands for each recovered table in the newly created RecoveredDatabase:
bcp RecoveredDatabase.dbo.MyTable out MyTable-n.Dat -n -T bcp RecoveredDatabase.dbo.MyTable format nul -n -x -f MyTable-f-n-x.Xml -T
This can be easily scripted into a bat file using Excel. You just have to copy the table names from the "Object Explorer Details" window while having selected the Tables folder in the "Object Explorer" of the Sql server management studio. Then do some string manipulation and you will be able to get the complete list of commands in no time.
Having exported your data, you will want to bulk insert them using the identity insert option described in the msdn article above. To do that, you will have to copy the exported files in C:\RecoveredData\ folder on the machine where you have the original database attached (hopefully you have already started the instance if you did close it down as I mentioned above) and then you will have to issue the following command for each exported table:
bcp OriginalDatabase.dbo.MyTable in C:\RecoveredData\MyTable-n.Dat -f C:\RecoveredData\MyTable-f-n-x.Xml -E -T
Using the same excel file mentioned above, ordering the tables in a way that parent tables get to be inserted first and then the dependent child tables, you will be able to generate the complete list of bcp insert commands.
Hopefully you are as lucky as my friend was and you will be able to restore all your records and then have some time to reflect on your backup policy and perhaps whether you do need the Cascade delete that caused all this.
When you realize that an accidental delete has occurred, stop using the database. If you can, stop the sql server instance and copy the mdf and ldf files of the affected database. In my case, I was called a couple hours later on but no harm was done as there was no backup process and the ldf file was intact.
You will need to parse the ldf entries to detect the deleted entries. I found a blog post which offers a stored procedure to do that, but unfortunately this didn't work for me as I was getting an error on the compatibility level (which was wrong). This is why I ended up using SysTools SQL Log Analyzer which as a demo can show you the log file entries. If you want to export the entries, you will have to buy the product.
This tool works with offline files. This is why I mentioned above to copy the mdf and ldf files. It reads the log and shows the INSERT,UPDATE and DELETE statements on each and every table you have. The beauty of this tool is that is also show the deleted row.
When the analysis of the ldf file finished, I selected all entries in the tree on the left and selected export. Then I used the "Sql server database" option and exported the deleted only rows to a new database which was created by the tool. Note that I also specified a data filter in order to minimize the number of data.
After a couple of hours (depending on the size of lost data) you will end up with a new database that has similar tables to the original one and each table will contain the deleted row.
After that, head to the bulk insert msdn article that mentions how to export and import the data using the bcp command. The process is fairly simple as you have to issue the following commands for each recovered table in the newly created RecoveredDatabase:
bcp RecoveredDatabase.dbo.MyTable out MyTable-n.Dat -n -T bcp RecoveredDatabase.dbo.MyTable format nul -n -x -f MyTable-f-n-x.Xml -T
This can be easily scripted into a bat file using Excel. You just have to copy the table names from the "Object Explorer Details" window while having selected the Tables folder in the "Object Explorer" of the Sql server management studio. Then do some string manipulation and you will be able to get the complete list of commands in no time.
Having exported your data, you will want to bulk insert them using the identity insert option described in the msdn article above. To do that, you will have to copy the exported files in C:\RecoveredData\ folder on the machine where you have the original database attached (hopefully you have already started the instance if you did close it down as I mentioned above) and then you will have to issue the following command for each exported table:
bcp OriginalDatabase.dbo.MyTable in C:\RecoveredData\MyTable-n.Dat -f C:\RecoveredData\MyTable-f-n-x.Xml -E -T
Using the same excel file mentioned above, ordering the tables in a way that parent tables get to be inserted first and then the dependent child tables, you will be able to generate the complete list of bcp insert commands.
Hopefully you are as lucky as my friend was and you will be able to restore all your records and then have some time to reflect on your backup policy and perhaps whether you do need the Cascade delete that caused all this.
No comments:
Post a Comment