September 20, 2011 at 12:13 pm
Oooops! A friend of mine deleted a table used to import order data from hand held devices into the main Point Of Sale system. The database is 2005 SP3. The deleted table doesn't affect regular order processing - only the import from the hand helds. Right now the site is up and running on normal order entry so I can't take them off line to restore the last backup and transaction logs up to the drop table.
The database is in full recovery model and there are transaction log backups done every 15 min.
Does anyone know of a 3rd party tool that can apply the transaction log backups to a single table?
My plan was to restore that last full backup to a different database, copy the missing table over and then somehow get the transactions applied to the table up to the point just before the table was dropped.
Todd Fifield
September 20, 2011 at 12:17 pm
Restore to a new DB, restore logs, copy data over.
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
September 20, 2011 at 12:23 pm
Gail,
Thanks. I've actually never restored logs to a different database than the original. Is there a write up of some kind on this?
Todd Fifield
September 20, 2011 at 12:26 pm
Nothing different.
RESTORE DATABASE <new name> FROM DISK = <backup location>
WITH MOVE <files to a new place>, NORECOVERY
RESTORE LOG <new name> FROM DISK = <Backup location>
WITH NORECOVERY
..
--repeat for rest of logs
RESTORE DATABASE <new name> WITH RECOVERY
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
September 20, 2011 at 12:30 pm
Gail,
Thanks a lot. I'm now going to try to bail my friend out of trouble.
Todd
September 20, 2011 at 2:58 pm
Well, everything worked fine thanks to Gail. However, when we were restoring the logs we stopped one short of where we really wanted to be and then did the WITH RECOVERY step. I've checked BOL and I can't seem to find out how to put the database back into a recovering state so the last log can be restored. I've actually never had to do this before.
Anyone got any ideas?
Todd Fifield
September 20, 2011 at 3:01 pm
tfifield (9/20/2011)
Well, everything worked fine thanks to Gail. However, when we were restoring the logs we stopped one short of where we really wanted to be and then did the WITH RECOVERY step. I've checked BOL and I can't seem to find out how to put the database back into a recovering state so the last log can be restored. I've actually never had to do this before.Anyone got any ideas?
Todd Fifield
You have to start over.
This is why we use script... can reuse all the work you already did... 😉
September 20, 2011 at 3:05 pm
Ninja's_RGR'us (9/20/2011)
tfifield (9/20/2011)
Well, everything worked fine thanks to Gail. However, when we were restoring the logs we stopped one short of where we really wanted to be and then did the WITH RECOVERY step. I've checked BOL and I can't seem to find out how to put the database back into a recovering state so the last log can be restored. I've actually never had to do this before.Anyone got any ideas?
Todd Fifield
You have to start over.
This is why we use script... can reuse all the work you already did... 😉
Remi,
Thanks - I was afraid of that.
Todd Fifield
September 20, 2011 at 3:08 pm
tfifield (9/20/2011)
Anyone got any ideas?Todd Fifield
Drop database, start again.
You can put the DB back into restoring easy enough, but it still won;t allow another log restore.
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
September 20, 2011 at 3:45 pm
GilaMonster (9/20/2011)
tfifield (9/20/2011)
Anyone got any ideas?Todd Fifield
Drop database, start again.
You can put the DB back into restoring easy enough, but it still won;t allow another log restore.
Thanks Gail. I found it out the hard way - ouch!.
After nearly 11 years working with SQL Server this is the first time I've ever had to do this. I don't go around deleting or truncating tables. Now I'll probably write some sort of script or procedure to get what I want out of MSDB and generate the restore statements.
My friend now owes me tickets to a Dodgers game!
Todd Fifield
September 20, 2011 at 3:50 pm
tfifield (9/20/2011)
GilaMonster (9/20/2011)
tfifield (9/20/2011)
Anyone got any ideas?Todd Fifield
Drop database, start again.
You can put the DB back into restoring easy enough, but it still won;t allow another log restore.
Thanks Gail. I found it out the hard way - ouch!.
After nearly 11 years working with SQL Server this is the first time I've ever had to do this. I don't go around deleting or truncating tables. Now I'll probably write some sort of script or procedure to get what I want out of MSDB and generate the restore statements.
My friend now owes me tickets to a Dodgers game!
Todd Fifield
You can do that in the restore GUI.
Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.
September 20, 2011 at 8:21 pm
Ninja's_RGR'us (9/20/2011)
tfifield (9/20/2011)
GilaMonster (9/20/2011)
tfifield (9/20/2011)
Anyone got any ideas?Todd Fifield
Drop database, start again.
You can put the DB back into restoring easy enough, but it still won;t allow another log restore.
Thanks Gail. I found it out the hard way - ouch!.
After nearly 11 years working with SQL Server this is the first time I've ever had to do this. I don't go around deleting or truncating tables. Now I'll probably write some sort of script or procedure to get what I want out of MSDB and generate the restore statements.
My friend now owes me tickets to a Dodgers game!
Todd Fifield
You can do that in the restore GUI.
Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.
Thanks Remi. I'll try that. It was kind of a PITA to do all of the logs by hand, but we did get it done with no data loss.
Todd
September 21, 2011 at 1:00 am
tfifield (9/20/2011)
Ninja's_RGR'us (9/20/2011)
You can do that in the restore GUI.Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.
Thanks Remi. I'll try that. It was kind of a PITA to do all of the logs by hand, but we did get it done with no data loss.
Todd
However restoring by hand is good practice for cases where you don't have the MSDB backup history around. Use the tools, but be sure you can do it without them too.
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
September 21, 2011 at 5:19 am
GilaMonster (9/21/2011)
tfifield (9/20/2011)
Ninja's_RGR'us (9/20/2011)
You can do that in the restore GUI.Pick your db, the PIT to which you want to restore to. Then SCRIPT & manually add the with move and new db name (ideally you'd do the restore on another server). Make sure you don't select the with replace option. That way you can't accidentally erase the prod db altogether.
Thanks Remi. I'll try that. It was kind of a PITA to do all of the logs by hand, but we did get it done with no data loss.
Todd
However restoring by hand is good practice for cases where you don't have the MSDB backup history around. Use the tools, but be sure you can do it without them too.
+100 on this one.
I have designed my whole DR strategy based on the fact that msdb will also be whacked. I rebuild the script solely based on the backup file names.
It's actually just as quick as the GUI, but with the certainty that it can't fail (because I've tested the script and I won't change it without fully retesting). With the GUI you can always forget or add an option by accident that makes the whole thing fail.
I had a real life test run with it 2 weeks ago.
I was out of office, I had never shown how to use the script and I was able to restore the prod DB over the phone on the first try & with minimal downtime without seeing the screen.
I call that a successful 'test' run ;-).
By now I know a monkey with a little training can start the restore of the prod DB in 2 minutes flat (no offense to the IT guys here :hehe:).
September 21, 2011 at 3:44 pm
Remi,
I can think of a couple of ways to do this using file names or dates. I'm curious as to how you do it?
Todd Fifield
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply