November 11, 2011 at 9:48 am
I took the backup using the Backup Up option in the Microsoft SQL Server Management Studio. While I was taking the backup, the database was online (meaning it was not offline or detached).
More detail:
- To take the backup right click on my database-->Tasks-->Back Up
- Recovery model: FULL
- Backup type:Full
....
- Destination: Backup Device created using Server Objects on the Local Disk
Once backup is completed I copy the the backup to external hard disk. Currently I restore this backup on another server and working fine. But the backup is 5 days old. I did not take backup after that. unfortunately the hard disk (containing the latest data ) is not available (only the 5 day log file is available).
So, is there a mechanism to recover the lost data from the log file?
November 11, 2011 at 9:54 am
Ok, great, so it's a normal database backup.
Now, my other question:
Do you have log backups? If not, how are you managing your transaction log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 9:56 am
The Database transaction Log file was on a separate hard drive where the drive is working currently.But We didn't take log backup for the last 5 days
November 11, 2011 at 9:58 am
No log backup in 5 days, your log must have grown huge.
Or have you been manually truncating it or switching to simple recovery and back to full? Important, I need an exact answer to this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 10:03 am
... and did you take ANY single log backup after the full?
November 11, 2011 at 10:13 am
Ninja's_RGR'us (11/11/2011)
... and did you take ANY single log backup after the full?
And if so, do you have that log backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 10:14 am
When we backup the database 5 days a go, it was very large log file. Immediately after the back up, we created new log file and deleted the previous large log file keeping the mdf file as it is. Currently we have around 25 MB log file size (within 5 days). This 25MB size log file is the only data obtained from the deployment server (no mdf file obtained). May the option "...is there the possibility of appending the 25MB data from log file to the previously backup data?.... " or any other ....
November 11, 2011 at 10:16 am
assefam (11/11/2011)
Immediately after the back up, we created new log file and deleted the previous large log file keeping the mdf file as it is.
Sorry, what???? Detach, delete ldf, reattach_single_file? Seriously??????
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 10:20 am
GilaMonster (11/11/2011)
assefam (11/11/2011)
Immediately after the back up, we created new log file and deleted the previous large log file keeping the mdf file as it is.Sorry, what???? Detach, delete ldf, reattach_single_file? Seriously??????
I am done here. :w00t:
November 11, 2011 at 10:52 am
assefam (11/11/2011)
When we backup the database 5 days a go, it was very large log file. Immediately after the back up, we created new log file and deleted the previous large log file keeping the mdf file as it is. Currently we have around 25 MB log file size (within 5 days). This 25MB size log file is the only data obtained from the deployment server (no mdf file obtained). May the option "...is there the possibility of appending the 25MB data from log file to the previously backup data?.... " or any other ....
If you deleted the actual LDF file after you made the full database backup, then you are out of luck.
There is no way to restore without an unbroken chain of transaction log backups going back to the full backup or to differential backup taken after the full backup.
For the future, you need to implement a proper backup strategy. And never delete the LDF file.
November 11, 2011 at 11:00 am
I didn't take any single backup after that
November 11, 2011 at 11:06 am
assefam (11/11/2011)
I didn't take any single backup after that
Then the best you can do here is restore from that 5 day old backup and accept that 5 days of data has been lost due to the lack of a backup strategy and the the deletion of the log file after the last backup.
When the log file was deleted (which should never, ever, ever be done) the log chain was broken. Since the log chain was broken, SQL switched the DB into auto-truncate mode, overwriting log entries once a checkpoint marked them inactive. You don't have 5 days of data in the log file. You might have more like 5 minutes.
Once you're restored last week's backup, start putting a proper backup strategy in place, which includes log backups.
Please read through this - Managing Transaction Logs[/url]
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 11:08 am
Really thank U very much for your help.
The question is we have log backup before 5 days
November 11, 2011 at 11:14 am
Then the best you can do here is restore from that 5 day old backup and accept that 5 days of data has been lost due to the lack of a backup strategy and the the deletion of the log file after the last backup.
You can start from here.
Introduction to Backup and Restore Strategies in SQL Server
November 11, 2011 at 11:20 am
assefam (11/11/2011)
The question is we have log backup before 5 days
Doesn't matter. If you have no full backup (or diff) after the point you deleted the log file & recreated it, then you cannot recover past that last full backup that you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply