Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Restoring database in time (no backups)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 08/27/2010 :  11:28:30  Show Profile  Reply with Quote

my problem - some data was deleted, and i want to restore database to point in time.

My database has logging set to "full", however, i do not have any backups (yes, I know, stupid me). Database file size is ~300MB, log file size is ~8,1 GB.

So, i imagine all my deleted data is in log file. Is it possible to extract it?

Flowing Fount of Yak Knowledge

7174 Posts

Posted - 08/27/2010 :  11:43:03  Show Profile  Reply with Quote
You have to get third party tool like Log Explorer to get it.
Go to Top of Page

Aged Yak Warrior

819 Posts

Posted - 08/27/2010 :  16:02:10  Show Profile  Reply with Quote
You must have at least one full backup that was performed. If you didn't have that, your log file would never have grown. Until the first backup is completed, SQL Server starts out in a psuedo-simple logging - since you cannot recover from anything in the log file until that first backup is done.

The first thing you need to do is try to find that initial backup file. Without out, none of the log explorer type applications will be able to do anything either. At least, all of the ones I have tried require a backup file before they can start reading the transaction log.

The next thing you need to do is backup the transaction log. And, once it is backed up - make sure you save that backup file in a very safe place.

I am afraid that without an initial backup to work from, you are not going to be able to get to the data in the transaction log.

Go to Top of Page


5072 Posts

Posted - 08/27/2010 :  18:31:20  Show Profile  Visit russell's Homepage  Reply with Quote
Go to Top of Page

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 08/28/2010 :  03:43:52  Show Profile  Reply with Quote
I agree with Jeff, you must have had a full backup performed previously in order for the database to actually use the full recovery model. Look in the msdb..backupset table, linked to the msdb..backupmediafamily, to find the backup details and physical file name.

With regards to the log explorer applications, I believe ApexSQL Log can read from the active transaction log, so you might want to give that a try. You might also want to visit this site that describes some limitations of that product, and also how to buy that product at a discount.

Good luck.

Ray Mond - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily
Go to Top of Page

Starting Member

2 Posts

Posted - 08/31/2010 :  05:21:36  Show Profile  Reply with Quote
Hi, i've found previous full backup (which was a bit old, but client agreed to use it), so problem is solved. Thank You everyone who contributed to this thread.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/31/2010 :  08:01:49  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
You've had a lucky escape this time.

have you changed your backup strategy? If not your really really should.

Backups are not a "nice to have". They are your first priority.

Msg 3903, Level 16, State 1, Line 1736
Go to Top of Page
  Previous Topic Topic Next 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.03 seconds. Powered By: Snitz Forums 2000