Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question
3  
You should not shrink your data file. See for details: sqlskills.com/blogs/paul/… – MicSim Jan 18 at 13:32
1  
Could use some clarification here. Is this your intended goal: 1 - Copy production database to clean up data to another server 2 - Clean up the copy 3 - Move the copy back to the production server, replace old , "unclean" version with the new clean version 4 - Apply any new data changes the copy that happened to your production database while you were cleaning up your copy. – Mike Fal Jan 18 at 17:22
2  
To elaborate on what MicSim said, shrinking will heavily fragment the database. This will destroy performance. As for your process. Can you do this in a maintenance window? Theres a lot of risk doing it this way. Even though you could take and restore transaction log backups and restore onto the this new database. You'll run into issues if the backups chain gets broken. I'd definitely avoid this approach. – Brandon leach Jan 18 at 17:28
4  
No, you cannot do this, stop trying. Suggest you revise your question with some detail on why you want to delete this data (what problem does it solve?) and why you can't do the cleanup work on the operational database. – Mark Storey-Smith Jan 18 at 19:00
1  
you should think about table partitioning – ElDoroCapitano Jan 19 at 13:00

migrated from stackoverflow.com Jan 18 at 17:09

2 Answers

up vote 1 down vote

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.

share|improve this answer
up vote 0 down vote

If you have never taken a FULL backup of your new small
database, log backup / restore of that database will not available. but!,

you can't restore you log backup from your old big database to the new small databas.

i guess you should try to get to use some of the sql enterprise features like partitioning to work better with your database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.