September 18, 2009 at 9:25 am
Subect line says it all. I have no idea how my statement
Delete from tablename
where field in ('value1', 'value2', etc.)
got turned into
Delete from tablename
as soon as I clicked the Go button, but it did, right in front of me.
I am trying to restore that table from a backup doen a couple of days ago. Don't want the whole database, just that one table. I have googled and there is nothing out there that is understandable, including BOL, although it's probably because I am not using the correct terms in my search.
Any help appreciated. Fairly urgent. SQL Server 2005.
September 18, 2009 at 9:42 am
hello middletree.
Im not sure if thats possible.
what you could do, is restore that backup on a new database, and copy the data you need
September 18, 2009 at 9:48 am
Fausto Echevarria (9/18/2009)
hello middletree.Im not sure if thats possible.
what you could do, is restore that backup on a new database, and copy the data you need
That's the easiest method, unless you are using a backup tool like Idera's SQLSafe that allows individual table restores.
next time you do a delete, insert or update, do this:
begin tran
Delete from tablename
where field in ('value1', 'value2', etc.)
then if there's a problem, run:
rollback
If the counts are as expected, run:
commit
September 18, 2009 at 11:08 am
You can do it with the latest version of RedGate backup software (Version 6.2 – released August 11, 2009).
Just go to www.RedGate.com and download a trial version, which works 14 days as a fully functional version (for free).
September 18, 2009 at 11:50 am
Redgate's is rather cumbersome last I knew, requiring and extra product install and several steps. The Idera uses the regular restore GUI, and you just click table restore & choose the table.
Either way, it won't help the OP in this case because the software would have to have been used for the backup before the data loss.
September 18, 2009 at 12:29 pm
Either way, it won't help the OP in this case because the software would have to have been used for the backup before the data loss.
Nope.
RedGate can work not just with it's own .sqb backups, but with the standard .bak files as well.
This is info from RedGate tech support:
SQL Object Level Recovery Native has been released and is available for download.
SQL Object Level Recovery Native enables you to recover individual database objects from Microsoft® Tape Format (MTF) SQL Server backup files (.bak), and restore them to a database of your choice.
This is useful, for example, if a user accidentally drops a table, or deletes important data from a production database. By recovering only the objects you specify, SQL Object Level Recovery Native can save you a considerable amount of time when compared to restoring from a full backup. You can also save space, as you do not need to restore a complete database. Further information and download instructions are available on the Red Gate website.
September 18, 2009 at 1:59 pm
ooops !
September 21, 2009 at 12:07 am
what about snapshot restore?
If you have a snapshot backup, you can do an INSERT..SELECT statement and point the SELECT to your snapshot backup...
any ideas on this??:rolleyes:
September 21, 2009 at 10:35 am
One way is to restore the database into a new database. You can then populate your old table from the contents of the restored database.
Maybe it's just me but I top and tail statements as below and run the begin tran and then the statements Finally, I the rollback or commit tran, depending on outcome of your post update tests. You don't need to specifically name the transaction, I have included this for effect ... ;o)
Hope this helps.
begin tran live_saver
-- potentially dangerous SQL statements here
rollback tran live_saver
commit tran live_saver
September 22, 2009 at 11:13 am
I love the suggestion to bracket delete and update statements (and other dangerous code) with the begin tran/commit or rollback. I knew of these statements but never considered using them in TSQL.
One question: in the (very) unlikely event of a lockup/crash etc after the tran and before the commit, when server is rebooted and the database is automatically restored will it default to rollback or will the database be waiting for a commit or rollback?
September 22, 2009 at 11:56 am
When SQL server restarts, it will be brought into a consistent state. Your uncommitted transaction will be rolled back.
Glad to be of help.
September 22, 2009 at 12:41 pm
if the system does crash, upon power-up the transaction log is reviewed and any transactions that havent been checkpointed are now written to the database.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply