I have the next scenario in SQL Server 2008: one big database (production DB). I want to backup this db then restore it on another server.
Then I do some delete operation from some big tables. After delete I want to shrink the database.
Then detach shrinked db, copy it on first (production) server, detach the big database and then attach the small one.
Now comes my problem: on the big (production database) the activity continued, and new records was inserted, updates done, deletes etc.
Will I be able to restore those operations from the transaction log on the new created small database? Is there a checkpoint before the first backup action? Will there be conflicts between the small DB transaction log and the big one's?
Any help (ideas, articles) would be greatly appreciated.
Thank you.
Best Answer
No. After you restored the database in your development environment it had become a different database. Re-attaching it back to production will replace the production database and all transaction that occurred after your initial detach will be lost. Doing what you describe usually require very complex solutions, involving setting up replication from production to staging and contiguously replicating transactions in production to the staging server. Needless to say, developing/testing such a complex solution is only worth it for a ... worthy goal. Shrinking your database is not such a worthy goal.
Handling situation of runaway data that was never designed for delete (which is 99% of all projects, since the need for efficient delete of data is never evident during development, when the tables are empty) is quite hard actually. Partitioning is the best solution, by far, but is a huge undertaking with serious impact on the application. Other than that, deleting old data in small batches is the next best thing, provided an appropriate supporting index is provisioned.