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 |
matman
Starting Member
3 Posts |
Posted - 2007-06-06 : 04:19:53
|
Hi,I need to receover lost data. My colleague ran a database creation script to create a new database, from Sql Query Analyzer. While doing this, an existing database was selected in the combo box that appears on the top,which led to deletion of records from the existing database. I have a backup of the d.base taken 10days ago. The data was lost yesterday. Is there any way I can restore the database to the previous condition. |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-06 : 04:57:01
|
Do you have transaction log backups?It sounds more like the colleague did a restore rather thana create and from the gui rather than a script.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
matman
Starting Member
3 Posts |
Posted - 2007-06-06 : 05:29:59
|
I only have a complete backup taken 'round 10 days ago.He ran the script from Query Analyser while the lost database was selected. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-06 : 05:55:50
|
I see - and the script did nasty things other than just creating objects.I think you will be stuck, I take it you have the database in simple recovery mode (as you have no tr log backups) so the transactions will have been lost.The only option is to go back to the backup or try to amend the data manually.You can erstore the backup to another database and compare the data if that helps.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
matman
Starting Member
3 Posts |
Posted - 2007-06-06 : 06:44:40
|
"...simple recovery mode (as you have no tr log backups)..." ?? Can you explain thisThanks for the response... How can I avoid such a situation in future?? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-06 : 09:40:55
|
quote: Originally posted by matman...Thanks for the response... How can I avoid such a situation in future??
Do you really have to ask?1. Setup jobs to make backups on a regular basis. If you need up to the minute recovery, put the database in full recovery mode and setup transaction log backups to run every 15 minutes. You can read all about in in SQL Server Books online. It's not hard; there's a Database Maintenance that will setup everyting for you.2. Don't give people who don't know what they are doing access to do things like that.CODO ERGO SUM |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-06 : 13:42:02
|
""...simple recovery mode (as you have no tr log backups)..." ?? Can you explain this"If your Database Recovery Model is SIMPLE then most likely you have no logging information that will be any help.However, if your Recovery Model is FULL then, even thought your most recent FULL backup is TEN days ago, your Log file will contain all the transactions processed since then. IF this is the case you can:1) Make a Transaction Log Backup2) Restore the old FULL backup to a new, temporary, database3) Restore the Transaction Log Backup using "Point in time" restore to just before the time of the accident4) Merge any additional, newer, data from your main database into the temporary database5) Proceed from there ... Kristen |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-06 : 14:12:32
|
If the db is in full recovery model, have another option. Get third party tool to recover lost data. |
|
|
|
|
|
|
|