June 10, 2015 at 9:40 pm
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed.
I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log.
Could you please give best suggestion and steps to restore full database back and one day's transaction log file.
Note: there is no differential database backup and transaction backup.
June 10, 2015 at 9:58 pm
What have you tried so far, exactly, and what are the errors you're encountering?
If your backup chain is just one full and one tlog, you would just be restoring the full using WITH NORECOVERY and then restoring the log using WITH RECOVERY.
While we're on the subject, what is your backup schedule? If you're only taking occasional full backups and 1 transaction log backup per day, that means even if your backups are perfect, you're vulnerable to up to 24 hours of data loss. In most cases you'd want to take more frequent backups than that.
Cheers!
EDIT: I just noticed the very end of your post, and it seems I may have misread initially. Do you only have a full backup, and no other backups?
June 10, 2015 at 10:36 pm
We have real time replication.
we have 3 databases like below
Database A: for Read and Write
Database B: for Read only
Database C: for Disaster Recovery Purpose
Real time replication is done from Database A to Database B and C.
Full Database and transaction log backup is being done in night time from Maintenance plan.
Database A and B is in sql Failover cluster.
Is it enough information for scenario to give information to suggest me for my question.
June 11, 2015 at 12:27 am
keshab.basnet (6/10/2015)
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed.I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log.
Could you please give best suggestion and steps to restore full database back and one day's transaction log file.
Note: there is no differential database backup and transaction backup.
If you only have full backups and no diffs or transaction log backups, then the full backup is your point of recovery and no further recovery is possible.
😎
Quick suggestion, either grab and read a good book on the subject (Accidental DBA etc.) or hire an expert to sort this out, that is if you don't want to loose a day worth of data again.
June 11, 2015 at 12:29 am
Nope, that's mostly irrelevant.
What backups do you have?
What times were they taken?
What recovery model is the database in?
What do you mean by 'crashed'?
What have you tried so far?
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
June 13, 2015 at 8:26 pm
Database is in full recovery mode. Back up is scheduled at night time 12 AM for both FULL DB backup and log file(that time almost no transaction in our database).
DB crashed means user production user data file is not working. needed to restore previous day Full backup and today's transaction log backup up to 5 PM. Could you please let me know if i still need to give other information.
June 14, 2015 at 7:01 am
Ok, let's get one thing clear, because I'm confused about it now. Do you have a transaction log backup? In the initial post you said you didn't, but in your most recent post you mention a transaction log backup.
If you have a full backup, and a transaction log backup that includes your restore point, then it would be as simple as restoring the full backup using WITH NORECOVERY, and then restoring the log backup using WITH STOPAT to restore to the point in time you wanted.
Has a log backup been taken since the time you want to restore to?
To go back to one of Gail's questions, though, telling us that that the file is not working doesn't really help us understand what's going on any more than telling us that it crashed.
What are the exact errors you are receiving? The more details we have the better we will be able to help.
Cheers!
June 14, 2015 at 8:44 am
keshab.basnet (6/13/2015)
DB crashed means user production user data file is not working.
Which means what exactly?
And it's kinda important because depending on exactly what you mean by 'not working', we may be able to restore to your last log backup or we may be able to restore to point of failure. But without knowing exactly what happened, very hard to give useful advice/
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
June 14, 2015 at 10:08 am
Listen to Gail, but also, what backup files do you have. You mention the full and log backup at midnight, but what else?
From your post, I assume you have:
- midnight full database backup
- midnight log backup
- tail log of crashed database? (has this been backed up?)
Anything else? Be specific. We can't see your system, so don't assume we know what files you have or what state things are in.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply