Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-22 : 17:02:13
|
| Paul Yakovenko writes "Hello.According to Documentation for BACKUP LOG command I can use option "NO_TRUNCATE" to handle the truncation of inactive entries (and it works).BUT Help says nothing about BACKUP DATABASE command. During some tests I found that sometimes MSSQL truncates log, and sometimes doesn't.Can you describe all situations when backup operation in MS SQL Server 2000 truncates the transaction log?Thanks in advancePaul" |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-22 : 17:24:56
|
| A full database backup will never truncate the log.Do you have recovery mode set to simple?If so, you may be seeing a checkpoint after the database backup, which would result in a truncation.HTH-Chad |
 |
|
|
pyak
Starting Member
2 Posts |
Posted - 2002-01-23 : 11:36:52
|
quote:
A full database backup will never truncate the log.Do you have recovery mode set to simple?
The origin for the question is the following situation:Recovery mode - FULLFULL DATABASE BACKUP is made every sunday at 8:00 AMDIFFERENTIAL BACKUP is made every day except sunday at 8:00 AMTRANSACTION LOG BACKUP is made every day every 1 hourIf I encounter a database failure on friday 3:00 PM according to Microsoft I should backup transaction log with NO_TRUNCATE, restore FULL backup, then friday differential backup and finally all friday transaction log backups.BUT I've found that I can restore my database using only full backup and all transaction log backups made during the week (missing differential backups). Can I really restore my database using the second algorythm (only with full backup and all logs, which were made after that backup)? or I can miss some action in database?Of course, I understand that the second algorythm is much slower than the first one.Paul |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-23 : 12:16:41
|
| A diff backup doesn't truncate the log either, so I would think you can do that (But why?), though I haven't ever tried it.You can test it. If SQL Server allows it, then you aren't missing any activity. If there is a gap in the LSNs of your backup set, SQL Server will not allow the restore.The best bet would be the first algorithm.-Chad |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-23 : 17:50:14
|
| It's one of the benefits of taking differential backups that if the last one fails you can still recover by restoring all the transaction log backups.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Truncation of transaction log during full backup