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
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Restoring database in time (no backups)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kudzis
Starting Member

Lithuania
2 Posts

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

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?

sodeep
Flowing Fount of Yak Knowledge

USA
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

jeffw8713
Aged Yak Warrior

USA
806 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.

Jeff
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
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

Malaysia
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
SQLBackupRestore.com - 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

kudzis
Starting Member

Lithuania
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.


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
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.08 seconds. Powered By: Snitz Forums 2000