Solved

How can I restore SQL 2005 database from Log Files?

Posted on 2010-11-20
14
466 Views
Last Modified: 2012-05-10
I have a production database that was accidentally corrupted after my last backup. Tha backup is planned to backup the database every 20 days and it works fine.

I have restored the database to the last backup which is 15 days earlier and now need to restore the remaining data ftrom the lost 15 days.

I found a link said that I can restore that database using log files:

http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html

But my SQL Server in 2005 not 200o

Any ideas?
0
Comment
Question by:Mohamed Khairy
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 4

Accepted Solution

by:
pbarry1 earned 100 total points
ID: 34182125
Hi,

was your database in SIMPLE or FULL recovery mode?  

If your database was in SIMPLE recovery mode, you won't be able to recover the remaining days of data because SQL Server shrinks data on an ongoing basis.

If your database was in FULL recovery mode and you still have the original Log files, you can detach your database, copy the old Log files over the ones restored and reattach your database.  SQL Server should be able to reapply the remaining transactions.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 34182829
Above is true. You need to have setup Full or Bulk-Logged Recovery mode, and backed up all of the log files since your datafile backup. It's no different for 2000 or 2005 (or 2008).
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 200 total points
ID: 34183456
If the database is in SIMPLE recovery mode your log files would be empty execpt for the last uncommitted transactions but you will not be able to recover nothing in between the last full backup and the uncommitted transactions I was talking above.

If you have log files from the last full backup that means you database was in FULL recovery mode and you are in luck.

The classic way to restore to the point in time of failure moment would be to restore the full backup first but make sure you choose WITH NO RECOVERY to let the database in restore mode to add the log files.

After that you will restore the log file but WITH RECOVERY option. If you have more than 1 log files you will have to restore them one by one in their chronological mode, still WITH NO RECOVERY option except for the very last log file which would be WITH RECOVERY mode.

I am not aware of the "trick' with detach and attached database. As I know you cannot attach a backup file but only a .MDB file(data file).
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Author Comment

by:Mohamed Khairy
ID: 34183639
Really appreciate your valuable comments.

I have a file named errorlog.6 and its size is 82 GB where the database is only 10 GB!!!

s that the log file needed or what?

0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 34183642
As the name says, it is the error log, not the transaction log. errorlog contains messages of MSSQL server while running (informational, warning and error).
0
 
LVL 7

Author Comment

by:Mohamed Khairy
ID: 34220812
@ Qlemo: The Database size is only 10 GB and the error log is 82 GB!!!!! is that normal?
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 34221458
Not really. You should have look into it, and restart MSSQL so it creates a new log file.
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 200 total points
ID: 34221854
Being so big means it hasn't been backed up fo a longer time, which is good if you intend to look into it. If you want to shrink it you don't have to restart just do a backup and then a log truncate. Another way is to change the recovery model of the database from Full to Simple, truncate the log file and then turn it back to Full recovery mode:

USE master
GO
-- set to simple recovery
ALTER DATABASE database_name
SET RECOVERY SIMPLE
GO
-- truncate log file
BACKUP LOG database_name WITH TRUNCATE_ONLY
GO
-- set back to full recovery
ALTER DATABASE database_name
SET RECOVERY FULL

However, in full recovery mode you need to perform periodic backups otherwise the log file will grow again.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 34221985
Zberteoc,
We are talking about the errlog.* files, not the transaction log.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 34222029
Oh, sorry. Then just execute this:

EXEC sp_cycle_errorlog

will do the same to the error logs as the restart but without restarting. However this will probably not shrink the size.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 34222115
Good idea to use the that SP instead (learned something today ;-)). And no, the errlog will not get shrunk, because it contains important information.
0
 
LVL 7

Author Comment

by:Mohamed Khairy
ID: 34222170
I am a little bit confused now, can the error.log give me the missing data or only the root cause of  the problem?
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 200 total points
ID: 34222187
The errolog does not contain any data, only information like when MSSQL has been started, with which parameters, when backups happened - and most important if there are errors.
So no, you cannot get any data back from the errlog.
0
 
LVL 7

Author Comment

by:Mohamed Khairy
ID: 34249922
Thanks Guys, Unfortunately I can not restore the database but I've modified the backup plan and the recovery mode after read your valuable comments.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Nested Case statement 4 38
SQL Server Configuration Manager WMI Error 11 19
SQL Log size 3 18
Display SQL 2008 last modified/update Database 11 18
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question