How much can be restored from a backup file

[seaport]

I deleted some objects (tables, views, procedures) from a database, shrunk the database, and made a full backup. If I restore the backup file, the deleted objects are certainly not in the database, but I see the restored transaction log file is pretty big. Are those deleted objects still in the transaction log and can they be somehow restored?

What if I did the full backup without shrinking the database? Can those deleted objects be restored, in some special way, from the full backup?

Seaport

 

[yelworcm]

You would have a better chance without shrinking the database. When you drop a table, the pages are marked as "unused". The data is still on them, until the page is overwritten, or released back to the operating system (by a shrink operation). In order for the transaction log to be of any use, it needs a starting point. You can think of it as a vector, or a set of instructions on a treasure map. Without knowing where to start, you could take "20 steps East" from any point in the world. Most likely, you will not be anywhere near where you wanted to be.

To use the log for recovery after the drop of the tables is still a bit iffy. You can try products like Lumigent's Log Explorer, but as a drop table updates internal pointers, I doubt they can bring the table back, either.

 

[seaport]

Thanks for the quick response.

I should have make the question clearer. I put two projects into one database just for convenience. Now I had to extract one project out of the database and give it to another person. I made a copy of the production database, restored it to a testing db, then deleted all objects not needed. Then I shrunk it, made a full backup, and sent out the backup file. I was concerned whether that guy could recover any deleted objects.

As I understand, once I shrunk the database, the data is physically removed from the database file and log. So the backup should not contain deleted data. What confused me is the big transaction log after I restored from the back, it was 170M for a 40M database. Do you think whether it contains any deleted objects?

Are there any procedures or tools to make sure that deleted objects are truly deleted?

Seaport

 

[yelworcm]

If you are not taking log backups, you may as well set the database to simple recovery.

As for procedures, you could create a brand new database, and just transfer the needed tables to that new database. No deletions necessary.

 

[seaport]

I tried the "Import Data" function, but the imported tables does not contains any constraints or indexes. Am I right? Is there a better utility to import tables and other objects from one database into another?

Seaport