• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Restore deleted records from a backup MSSQL 2K

I deleted some records out of some tables, so I went to try and restore these, I run a backup every 10minutes on the data which writes to a file, this is then backed to tape every night. However when I am ttrying to restore the data, everything sayys it has worked but the deleted data is no longer present. I even tried restoring to a new DB but have the same problem. Any help would be greatly appreciated.
Thanks
0
longbloke69
Asked:
longbloke69
  • 4
  • 3
  • 2
  • +1
1 Solution
 
tigin44Commented:
you should return to the point before the deletion operation occured. Whay kind of backup do you take at 10 minutes period? full? differential? The restore procedure changes according to your backing up policy.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Restore your backup (which you are sure that those records will be present ) into another database
Copy that particular table alone from that restored database into your database.
0
 
longbloke69Author Commented:
Tigin
That is what I did, this is a copy of the code that runs every 10 mins...
BACKUP DATABASE [ttv2] TO  DISK = N'c:\db_bkup\dbname' WITH  INIT ,  NOUNLOAD ,  NAME = N'dbname',  NOSKIP ,  STATS = 10,  NOFORMAT
Which I believe is a full backup

RRJEGAN
I tried that and none of the records appeared...


0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you backup the transaction log every 10 minutes.

so, you need to restore the last full backup of before that time of delete, and eventually apply the transaction logs to the point in time before that time of delete.

by default, the GUI will do a restore to the latest time available via the backed up file history.
0
 
tigin44Commented:
by using that backup option you cant return your data.. you are overwriting the previous one... asume your scenario...
10:00 backup
10:10 backup
10:15 delete records
10:20 backup


when you bacup at 10:20 you lost the table data by owerriting it. If your daily backup do not owerrites then return to the previous day and take data from that restore...
0
 
longbloke69Author Commented:
Not sure I believed the SQL statement did both....or am I mistaken...if not then there lies the problem. While restoring I saw nothing about restoring the transaction logs...a re you able to shed on light on how I apply the transaction logs to the pont in time before the time of delete
0
 
longbloke69Author Commented:
Ok tigin, that make sense thank you for the clarity, ,I have taken the backup from the pervious day and all is good. What would be the best way to then keep the data backed up without having to go to tape every day...4 backups a day...one every 6 hours...and each backup has a different name...
0
 
tigin44Commented:
backup policies depend on your need and data change speed... but basicly it would be better to take a full backup and then differential backups. the period may be full backup at midnight and differential backups at every 15 minutes. But this is just a suggestion. Its better that you should read about this. Also you should check the option in your bacup command...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< I tried that and none of the records appeared... >>

On one or the other day records would have been present.
If you are sure that records are present on a particular date, then you can restore the backup to another database and restore from that right.

For eg., If you know records are present on 31st of Dec, then restore your 30th Dec backup into a new database.
This is what I meant and this would solve you.
0
 
longbloke69Author Commented:
hey rrjegan that is what I did but did not work....as per tigins insights.

Tiggin thank you for your help.....points awarded
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now