How to design a single backup table for soft-deletion?

Question

I researched about what to choose between soft-delete and hard-delete of rows in a database. I read all the suggestions given on following links:

• What is the best way to implement soft deletion?
• Physical vs. logical / soft delete of database record?
• Are soft deletes a good idea?
• http://blog.sqlauthority.com/2010/09/03/sql-server-soft-delete-isdelete-column-your-opinion/

and came to conclusion that Soft Deletion is the only way to delete data and keep it at the same time if that old data becomes important in future for reasons like audits, recovery etc. And it can be more efficiently implemented if table rows are moved to a backup table that contains same type of columns and some extra information like Who deleted the row? or When was row deleted?.

I don't know what will be the future of my product and my clients not demanding now but may demand old data in future. But to implement Backup Table method of soft-deletion, I have to create clone tables for each table in my database. But there are lots of tables and more tables coming in future. Or if I change the design of one table (like changing data-types or adding columns), I have to change design for its backup table too.

So, my question is that is there any way I can create a single table that can contain the rows of other tables that are deleted? (like a RecycleBin table or something like that).

And I am open to other methods too that can achieve this with performance.

I am using SQL Server 2008 and 2012.

UPDATE

Ok I am suggesting this solution by combining everything you guys suggested:

1. Original table renamed to A_Main from A
2. Audit table is A_Audit where deletion metadata is stored. Example: who deleted, when deleted etc with an ID foreign key from A_Main.
3. A view with the old original table name A is created so that to avoid modification in all queries and stored procedures. This view will select only those rows from A_Main where A_Audit doesn't contain their ID.
4. Now the operations will be done on this view.

Is this a good way?

Answer 1

Treating this question as academic:

So, my question is that is there any way I can create a single table that can contain the rows of other tables that are deleted? (like a RecycleBin table or something like that).

Only if all of the tables that you want to archive have the same column structure. Otherwise, you need to create a backup table for each of the tables that you want to implement this method of soft-deletion for. Then you either put a trigger on the table to populate the backup table when a row is deleted from the original table, or control deletions through a stored procedure that populates the back up table and deletes from the original table.