SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recover Data...Urgent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

matman
Starting Member

3 Posts

Posted - 06/06/2007 :  04:19:53  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 06/06/2007 :  04:57:01  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

matman
Starting Member

3 Posts

Posted - 06/06/2007 :  05:29:59  Show Profile  Reply with Quote
I only have a complete backup taken 'round 10 days ago.

He ran the script from Query Analyser while the lost database was selected.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/06/2007 :  05:55:50  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

matman
Starting Member

3 Posts

Posted - 06/06/2007 :  06:44:40  Show Profile  Reply with Quote
"...simple recovery mode (as you have no tr log backups)..." ?? Can you explain this

Thanks for the response... How can I avoid such a situation in future??
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/06/2007 :  09:40:55  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/06/2007 :  13:42:02  Show Profile  Reply with Quote
""...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 Backup
2) Restore the old FULL backup to a new, temporary, database
3) Restore the Transaction Log Backup using "Point in time" restore to just before the time of the accident
4) Merge any additional, newer, data from your main database into the temporary database
5) Proceed from there ...

Kristen
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 06/06/2007 :  14:12:32  Show Profile  Reply with Quote
If the db is in full recovery model, have another option. Get third party tool to recover lost data.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000