Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ismailc
Constraint Violating Yak Guru
290 Posts |
 Posted - 2010-02-06 : 05:08:51
|
| Good day, i urgently need help as i messed up.I have to DB with all tables exactly the same one being Live & other Test.Last night i truncated the incorrect DB table, i urgently ned to get it restored. There are backups & I tried restroring but keep on getting the error: Opertion on device "....bak exceeded retry count."But because i messed up I would like to do a rollback if possible, as the last avaialable restore I hope is fine as i can't restore (error of above) has data of the night before which is missing the data of the current day as of the truncate (truncate before backup)I am not a DB but have rights, please help i dont want to bugger up more :(Regards |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-06 : 05:50:12
|
| what is recovery model used? are there transactional log backups? |
 |
|
|
ismailc
Constraint Violating Yak Guru
290 Posts |
Posted - 2010-02-06 : 06:24:31
|
| Hi, i'm not sure - i tried in time restorei can see the backup was successfull but i cant restoreI'm on the server but dont know from what tape to restorecan i not do a rollback?I'm on the server, i select filegroups or files, then i select the date with the .mdf fileThe file does exists, but error: Device error or Device offlineWhen i do a point in time restore - select the date, path of .bak is correct then i get error: its not a valid Microsoft Tape format |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-06 : 06:40:26
|
quote:
Originally posted by ismailc
Hi, i'm not sure - i tried in time restorei can see the backup was successfull but i cant restoreI'm on the server but dont know from what tape to restorecan i not do a rollback?I'm on the server, i select filegroups or files, then i select the date with the .mdf fileThe file does exists, but error: Device error or Device offlineWhen i do a point in time restore - select the date, path of .bak is correct then i get error: its not a valid Microsoft Tape format
you cant rollback unless you use transaction and hasnt already commited it.Now the option available is to restore from backup to time till the truncation happened |
|
|
|
ismailc
Constraint Violating Yak Guru
290 Posts |
Posted - 2010-02-06 : 06:56:22
|
| I'm trying to restore but get all kinds of errors,not a valid Microsoft tape format, its offline etc |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-06 : 07:06:35
|
Do you:1) backup database direct to tapeor2) Backup database to file, and then "copy" the files to TapeIf (1) you have to restore from tape, using the appropriate tape-restore-agent that communicates with SQL directly. (IMHO this option should be avoided except for people who have full time very skilled DBA and have carefully evaluated and selected specialist software. But if that is what you have got then that's what you've got  )If (2) you just need to get the File off the tape - but maybe the original is still on disk? if so you can use that.Note that you will lose all changes to the database since the accident (maybe no-one gas used it since including no scheduled batch jobs that import from elsewhere etcIf you are using FULL Recovery Model you should make a Transaction Log Backup BEFORE restoring - you might need that to make a full restore.Also, if you have enough disk space, I would create a new, temporary, database and restore to that (so you can experiment before doing it for real).Personally I would change the database to "DBO Only, Single User" to prevent anyone / any jobs logging in to it and thus prevent any additional data changes from being made. |
|
|
|
ismailc
Constraint Violating Yak Guru
290 Posts |
Posted - 2010-02-06 : 12:21:57
|
| Thank You all, seeing that i already messed up - i think it's best i wait for the DBA on Monday. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-06 : 12:34:09
|
| Good idea.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Truncate incorrect DB table - need to rollback sql