Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kudzis
Starting Member
2 Posts |
 Posted - 2010-08-27 : 11:28:30
|
| 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
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-08-27 : 11:43:03
|
| You have to get third party tool like Log Explorer to get it. |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-08-27 : 16:02:10
|
| 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 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-27 : 18:31:20
|
 |
|
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2010-08-28 : 03:43:52
|
| 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 MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL 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 |
|
|
|
kudzis
Starting Member
2 Posts |
Posted - 2010-08-31 : 05:21:36
|
| 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. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-08-31 : 08:01:49
|
| 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Restoring database in time (no backups)