September 21, 2012 at 8:40 am
Hi all
I'm used to backing up log files as .TRN. But I have taken over a system that backs up the log as .bak file.
I am also used to the process of point in time recovery using TRN files that create seperate files every xmins.
This appends the same .bak file with the log backups.
To do a point in time recovery would I use the STOPAT caluse using the log.bak file. Is it possible.
Can I recover to a POINT IN TIME?
September 21, 2012 at 8:49 am
The extension of the files for backups (or data /log files) has no meaning. By convention we use .BAK, .DFF, and .TRN, but it isn't required.
Point in time uses the STOPAT option.
September 21, 2012 at 8:50 am
Extension doesn't matter. You could call a SQL backup file readme.txt and it would still be a SQL backup file. If those are log backups, you can restore them the way you are used to restoring log backups
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
September 21, 2012 at 8:54 am
Yes you will be able to recover to a point in time.
I would strongly recommend moving into the old way that your used to in creating seperate files for each backup run as the way you have it now, if the big bak file becomes corrupt for whatever reason, then anything in that backup file (so all full, diff, tran backups) are rendered useless.
When restoring you will restore as normal but add in the FILE option of the restore command so SQL knows which one of the many files to use for that step of the restore.
So if I wanted to use file 9 which is the full backup it would be something like this
RESTORE DATABASE dbname FROM DISK = 'C:\Backups\dbname.bak' WITH FILE = 9
September 21, 2012 at 9:08 am
Thanks all. I thought as much but always nice to have it confirmed when it's your main db. I will take the file corruption into account and may change it soon as that is a very valid point.
September 22, 2012 at 9:09 am
If the current process is backing up to a single file - at what point is that file initialized? Because, once initialized - all previous log backups are gone.
If you do not have a good backup of that file taken right before it is initialized - then you have a broken log chain. You lose the ability to restore to a prior full backup and apply all transaction log backups from that point forward to current.
If your current full backup is corrupted - for any reason, or worse - your database is corrupted some time before the full backup is taken and that full backup process initializes the backup devices and actually backs up the database you now no longer have any way of recovering.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply