September 24, 2014 at 1:50 pm
Hello,
According to my understand, when I do Sql Server full database backup, data in transaction log will be written to data files and transaction log is cleared.
My issues is my database backup on local server is always good but coping backup files to backup server is always failed which makes the whole backup failed and I cannot recover database to point in time because I only keep one backup copy on local server disk. I backup log file every 2 hours and DB backup nightly. Is there a way to resolve this issue like I can backup transaction log which is always keeping increametal data changes and if I have one full DB backup and I have all logs backup since that time, I can always recover database to point in time even if that backup is several days before?
Thanks for your input!
April
September 24, 2014 at 2:49 pm
ying.wang (9/24/2014)
According to my understand, when I do Sql Server full database backup, data in transaction log will be written to data files and transaction log is cleared.
No.
When you take a full backup, SQL copies the extents in the data file, plus a small portion of the transaction log, to the backup file. That's it.
My issues is my database backup on local server is always good but coping backup files to backup server is always failed which makes the whole backup failed
That sounds like a network issue. I'd start by investigating why copying a file is damaging it. Speak to your network admin, he should be able to help.
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 24, 2014 at 3:08 pm
Thanks for your input! So my backup solution is ok even if some recent days' backup failed only if I have all logs backed up to date, I can still recover database to point in time. I have realized that when I do full backup, only inactive logical log file are cleared and active logical log file is still intact until next time log file backed up and it switch logical log file.
It is a network issues to make copying 240G backup files to backup server failed.
Thanks for your Help!
April
September 25, 2014 at 2:38 am
ying.wang (9/24/2014)
I have realized that when I do full backup, only inactive logical log file are cleared and active logical log file is still intact
No!
Full backup does not clear any portion of the log. It's the log backups only which mark portions of the log as reusable. Not the full backup.
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 25, 2014 at 3:57 am
Read up about FULL and LOG backup Back Up and Restore of SQL Server Databases. Like Gail mentioned a FULL backup doesn't clear anything in the LOG files.
With a FULL backup and all sequential LOG backups it is possible to restore a database to a point in time. The sequential LOG backups still works when another FULL or DIFF backup is taken inbetween.
Take this scenario: a daily FULL backup and a hourly DIFF.
You can restore the last FULL backup and all LOG backups taken since that time and restore the database to the state of the last LOG backup.
But you could also restore the FULL of three days ago and all LOG backups taken since that time and restore the database to the same state as above. You need to restore a lot more LOG backups compared to the previous option but the result will be the same.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply