February 17, 2015 at 3:01 am
Guys,
I have a database that we were doing some performance testing on for migrating to the next version of our product. I backed it up and restored it to a new database but when I ran the migration scripts I ran them against the original database not the copy so I restored the original from the backup. Unknown to me, another user had been in an also made changes to the original database which are now lost as a result of the restore. The database was in simple logging mode and no other changes have been made to the database since the restore.
Is there any way to roll the database forwards to recover the changes. The changes I made were relatively small (some ALTER COLUMN scripts) and are scripted whereas the other changes are more significant and are not scripted.
The database says that the transaction logs are shipped every night and there were 3 overnights between the database changes and the restore.
February 17, 2015 at 3:13 am
No.
When you restored, you overwrote the old DB. Anything in there is gone, unless there was a full or differential backup taken between the times the user made the changes and the time the DB was restored.
If the DB is in simple recovery, there's no way you're shipping logs nightly. Log shipping requires full or bulk-logged recovery model.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2015 at 3:14 am
Thanks Gail, Not the answer I wanted, but the one I expected. Serves him right for developing in a test database 🙂
February 17, 2015 at 5:20 am
Start using source control for database development in the same way as app dev. That's the best way to get things done and you can easily recover from problems like this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2015 at 6:24 am
Preaching to the converted mate!
Currently have 40+ customers all with modified databases that do not match the release standard and the test database may or may not match the production;
Retro fitting SCC is really hard work, especially as no one here can grasp the concept of the benefits it brings.
February 17, 2015 at 6:39 am
After a few more incidents where things get 'lost' because someone was being an idiot, even the idiots will usually figure it out.
February 17, 2015 at 6:53 am
Yeah, I'd think the person that just lost their work might be on board now.
It can be difficult to get across the idea of treating a database like code, but many of the benefits are the same as the code. Enlist the developers. Ask them why they put app code into source control. You have the same answers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2015 at 7:10 am
aaron.reese (2/17/2015)
Preaching to the converted mate!Currently have 40+ customers all with modified databases that do not match the release standard and the test database may or may not match the production;
Retro fitting SCC is really hard work, especially as no one here can grasp the concept of the benefits it brings.
Even if the developer isn't using source control, she should at least have a local copy of the .sql scripts on her PC, unless she's making DDL changes to the database in place using Object Explorer in SSMS or VS.
Also, perhaps you could leverage a schema comparison tool to script out differences between and test and (production ?) servers?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 17, 2015 at 7:21 am
This would be more of a solution going forward, but we can also use DDL Triggers to audit all database schema changes. I've personally never done this, but as the DBA in the development and QA environment, it might be a good idea, if for no other reason that to keep an eye on what other developers are doing.
For a production DBA it also provides a means to see what exactly changed for a specific timeframe, just in case a deployment goes horribly wrong and you want to reverse the changes rather than perform a restore, or to keep an eye out for DDL modifications that might come in through the back door.
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply