April 22, 2011 at 1:06 pm
i have a database which is in FULL recovery mode but neither full or log backups are taken( for someone reason we missed this db (: ). Can i rollback transactions from ldf?please let me know
April 22, 2011 at 1:09 pm
Not without backups, no. You restore to a point in time, but you can't reverse to it.
Though, that'd be a really cool feature.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 22, 2011 at 1:14 pm
i want to restore point in time? how can i do that? can i just take a fresh full back and then log backup and then point in time?
April 22, 2011 at 1:24 pm
You can't restore without a full backup to start with.
April 22, 2011 at 1:24 pm
sqldba_icon (4/22/2011)
i want to restore point in time? how can i do that? can i just take a fresh full back and then log backup and then point in time?
If you never took an original backup, you have no starting point for the logs to work from to restore to point in time. Taking one now would mean that you could start from this moment forward being able to do it, if you kept up on your log backups.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 22, 2011 at 2:17 pm
There are log reader tools, they may or may not work in this case (more likely won't if there has never ever been a backup) and the cost around $1000.
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
April 23, 2011 at 12:55 pm
Going forward, you can use something like this to identify databases that have not been backed up. Perhaps put it into a job with an email alert
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
b.recovery_model_desc as 'Recovery Model',
case
when state_desc <> 'ONLINE' then state_desc -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status'
FROM master.sys.databases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name --AND A.type = 'D'
GROUP BY B.name , a.type, b.recovery_model_desc, state_desc
ORDER BY B.name , LastBackupDate desc,a.type, b.recovery_model_desc, state_desc
April 25, 2011 at 12:56 am
Currently what is the situation?
What files u have?Mdf,ldf..size of files..
Thanks
April 26, 2011 at 7:49 am
I actually think ApexSQL's Log product might be able to unwind or redo transactions given your scenario. Tell them TheSQLGuru sent you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 26, 2011 at 9:13 am
thanks everyone.
April 26, 2011 at 2:24 pm
TheSQLGuru (4/26/2011)
I actually think ApexSQL's Log product might be able to unwind or redo transactions given your scenario. Tell them TheSQLGuru sent you.
Just to be clear - if that database has never been backed up before, there is nothing that can be done to recover transactions from the log. Until you have a backup - the database will not really be set to full recovery (kind of a psuedo-simple recovery model).
SQL Server knows that without a backup, there is no reason to keep the transactions in the log file so they are marked as reusable just the same as a database in simple recovery. Once you take a backup, the full recovery model functionality starts up and the log records are not marked as reusable until a log backup has been performed.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2011 at 4:46 am
as jeffrey sais...
as far as I can remember (think I read this in an article by paul randall sometime ago) a db is in simple mode until a data backup is taken. So, even though you changed the recovery model to 'full', it's still in 'simple' and so transactions are probably already overwritten in the log
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply