June 11, 2002 at 6:28 pm
Does Sql Server 2000 truncate a transaction log after a full backup? Does it also shrink the transaction file after a full backup?
Sorry for these simple questions, I am new to Sql Server.
June 12, 2002 at 8:17 am
June 12, 2002 at 12:12 pm
Under most circumstances it does not and you will need to run BACKUP LOG [db] WITH TRUNCATE_ONLY right before a full backup. Then you can issue DBCC SHRINKFILE to shrink the log file, however if you generally grow back to the same size weekly consider leaving this size (might shrink and grow to that size in one step this way the file remains more contigious in the HD and do not have to waste processor on file growth.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 12, 2002 at 5:55 pm
We are planning on changing our backup and recovery strategy. I understand that if I do a BACKUP LOG [db] WITH TRUNCATE_ONLY and then perform a full backup of the database (and hopefully a data file will not get corrupted while the BACKUP LOG is running), my full database backup will be good and enough in case something goes wrong (it will be good up to the point of the end of the full backup). However, here is my question:
If I do a differential backup after running BACKUP LOG [db] WITH TRUNCATE_ONLY, will my differential backup be good and sufficient for a recovery?
I already know the answer to the above question, but I am new to SQL Server and would like to get your expert opinion on this. I hate surprises, specially if it has to do with backup and recovery.
Thanks in advance for your help.
June 12, 2002 at 7:15 pm
This point has always confused me and if I am wrong from what I was taught please let me know I have never gotten a sure answer. My understanding is that the transaction log must stay intact in order to perform a differential since the last full backup, so even if you have done a differential the TL must not have been truncated. The only reason I have questioned (but I have not had a chance to test) is that if an unlogged transaction such as TRUNCATE TABLE, or BULK INSERT was run it tells you you must run either a FULL or DIFF and TL backup will not run. Anyone else have more to add?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 12, 2002 at 10:04 pm
This is for SQL Server 2000.
Consider the case of a simple recovery configuration. The transaction log will be truncated whenever an automatic checkpoint occurs. Automatic checkpoints will occur either when the log becomes 70% full or the number of log records reaches the number SQL Server estimates it can process within the recovery interval, whichever is lesser.
SQL Server determines what to back up for a differential by the Differential Changed Map (DCM), a new data structure in SQL Server 2000. The DCM tracks what extents have changed since the last full backup. If the bit for an extent is set to 1, it needs to be grabbed by a differential. A full backup will set all bits back to 0.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 13, 2002 at 11:05 am
Gurus, please correct me if I am wrong with my understanding of how backup works. Here is what I have concluded so far (by reading online manuals, my own books, and your posts):
1. Perform BACKUP LOG...TRUNCATE_ONLY because SQL Server 2000 often does not truncate the logs after a full backup. This will delete all the inactive log entries.
2. Since I have truncated the logs, I need to perform a FULL BACKUP right after the truncate. The full backup ensures that I have got a full copy of the database as it exists at that point in time, which includes all of the changes that were in the log and were truncated in step 1.
3. A FULL BACKUP backs up all my data files and also the logs that were generated since the truncate in step 1.
4. I then have two options: backup logs or perform differential backup. Of course, it doesn't make sense to perform this step right after a full backup. This step is to be used once a day or as often (or as little) as needed.
5. If I choose to perform differential backups instead of log backups, the differential backup will backup the data file extents that have been changed (using DCM technology) since the full backup in step 2. It also will backup all the log entries that were generated from the time that the differential backup started (not from the time that the full backup backed up the logs in step 2).
6. Since the differential backups perform a backup of all changes since the last full backup, it is safe to say that I can even perform a BACKUP LOG...TRUNCATE_ONLY before my differential backup.
Several things:
I want to know if my understanding of how backup works (steps above) is correct. And I also want to know that using the BACKUP LOG...TRUNCATE_ONLY as I have used in the steps above will not compromise my backups. And finally, I want to know my understanding of how a full and differential backup backs up the logs is correct (see step 3 for example).
Thanks a million for your help everyone, you don't know how much this is helping me learn. Backup and recovery has always been my weakest point but I am now seriously working on learning backup and recovery and feeling comfortable with it.
Edited by - shahgols on 06/13/2002 11:07:52 AM
June 13, 2002 at 6:18 pm
Sounds right to me. However you may want to do a backup of the log or a differential right before issuing the truncate log just for a safety margin. Many folks I know who don't want to loose data make a file backup of the logs periodically and then a diff daily, and a full weekly. This allows them less data loss. But you are right do what suites your needs but no less, more is better.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/13/2002 6:19:35 PM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply