SQL Server Logs – How to Insert Fail Logs

You should look in to partitioning.

http://technet.microsoft.com/en-us/library/dd578580%28SQL.100%29.aspx

The cool thing about partitioning is that you have just one table name (as opposed to the multiple table approach) so your insert statements remain static. It works with every application - it's completely transparent to queries. You don't have to worry about what happens if you end up with different indexes or statistics on each of the tables, either.

You create a partition function that decides how to break up the table into multiple tables behind the scene. The function can only take one input parameter/field, and in your case, it would be a date field. The function can break up the table by date, week, month, or year - in your case, you'd want date, 24-hour period.

Then build a SQL Server Agent job that uses T-SQL to swap out the last partition every day. The delete becomes a metadata operation, and it's blazing fast. Swap the partition, then drop the old one out.

If you've ever taken a backup, and your recovery model is BULK LOGGED or FULL, then you might have some luck. Unfortunately if never took a backup before you had your problem, then you are probably bang out of luck (as Martin Smith pointed out in his comments below).

You can check whether you've ever taken a backup by looking at:

SELECT db_name(database_id) as 'database', last_log_backup_lsn 
FROM sys.database_recovery_status

If the database you've lost data from has a NULL for last_log_backup_lsn, then the database is still in auto-truncate mode and your changes have possibly be lost forever.

If this is a server that you can take offline, I'd suggest shutting down the server and taking a copy or two of the .mdf/.ldf files first, just in case you can get something useful out of them.

There are third party tools like Apex SQL Log or Toad for SQL Server which will allow you to interrogate the .ldf files directly and hopefully see what happened to the data, plus they can usually interrogate transaction log backup files as well. Unfortunately they're not cheap, but you maybe able to get the functionality that you need from a trial version (I don't use either so I can't be sure about this).

If you find another tool that allows you to look at the transaction log backups, but not .ldf files, you should be able to:

1. Take a full backup
2. Take your first a transaction log backup (probably uncompressed to maximise the chance that tools will work with it)

Then use the backup from step 2.

Unfortunately, your mileage may vary, so I'd suggest doing what you can with trial versions of tools before spending lots of money on a tool that might not get your data back.