December 2, 2008 at 8:16 pm
we have been asked to create a log shipped database for a client. they will send us an initial .bak file then all subsequent .trn files. but they will not be sending us the subsequent nightly .bak files.
we have tested the following sequence of events on the source database:
(1) full backup created
(2) log backup created
(3) log backup created
(4) new table added to database
(5) full backup created
(6) log backup created
then this sequence of events on the restored database:
full backup (1) shipped and restored
log backup (2) shipped and restored
log backup (3) shipped and restored
log backup (6) shipped and restored
we found that the table created in (4) in the source database was also created in the restored database. this surprised us because our interpretation of the sql help documentation led us to expect that the log file would be truncated when the full backup (5) was created.
so here are my questions:
- is there specific detail on this in the documentation?
- are there any general pitfalls for us to watch for with the log shipping solution whereby we will not receive the nightly full backups?
thanks
December 2, 2008 at 11:57 pm
Full backup does not truncate the tran log. Never has. Only a transaction log backup, an explicit truncate or a switch to simple recovery will truncate the tran log.
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
December 4, 2008 at 1:29 pm
As mentioned, you should be fine with just sending over log backups after the full is moved.
If you lose a log backup file, then you will need to send another full backup to get things restarted, but as long as all log backups copy and restore correctly, you'll be fine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply