This is not a situation indicative of disaster recovery, but it represents a potential for destruction of your production system. If there is missing data, you need to find out why. Places to check are:

1. A trigger on the table or another table that delete data.
2. A stored procedure that deletes data from that table
3. An unrestricted delete from the application
4. An improper delete from the application
5. User error

If none of these will locate why the data is missing, then you have a security problem. If it is a security problem, then you need to investigate your security policy and strengthen it.

You should also begin to perform auditing in the database. The first level of auditing is simply by dumping an sp_who to a table on a periodic basis such as every minute. The second level of auditing is to add audit tables for each of the critical tables in the database. These audit tables utilize triggers to maintain all changes made to a table along with the time it was changed and the user performing the change. The final stage of auditing is by adding audit tables to every table in your database.

Along with this, you should also have login auditing turned on that will audit failed logins. Password expiration should be required and enforced. Currently the only native password expiration available is by using integrated security and relying on the NT password expiration. You can also setup a home grown system within the database, but this must be coupled with the application and other internal checking mechanisms. The sa password should be suitably long and contain a combination of upper/lowercase letters and numbers. The minimum password for an sa account should be 8 characters with at least two of them being numbers. Neither of the numbers should be the first or last character in the password unless using more than two numbers.

Finally, you can use a product such as Platinum's Log Analyzer to locate the transactions that removed the data. It will tell you when the transactions occurred, who performed them, and give you the option to generate SQL statements to put the data back in.

Inadvertently Deleted Data

There are three choices here.

1. Have the user input the data again
2. Restore the most recent backup to another server, create a view over the table that returns only the missing rows, BCP out of the view, and BCP into the production server.
3. Use a tool like Platinum's Log Analyzer to reverse engineer the transaction log

6.5 Disaster Recovery Plan 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18