Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In this video tutorial it is explained that we should use the transaction log backup of the primary server database and restore it into the secondary server database for log shipping.

I was doing a sample practice to check at my machine and got to know that we don't need to take a backup of the transaction log from the primary database server. Just take a full backup and restore it to the secondary server.

By doing this it will automatically copy the transaction log file to the secondary server.

Can you please confirm if this is the right approach or if it is mandatory to include the transaction log backup from the primary server?

share|improve this question
    
Do you mean to initialize the logshipping or in general? –  Edward Dortland Sep 6 '12 at 18:43
    
Initialize the logshipping –  testing Sep 7 '12 at 10:02
add comment

3 Answers 3

If you don't include the transaction log, then it's not really log shipping, is it?

Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at that same point in time. I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups).

So if your intention is to do this backup / restore once a day, and you don't need to keep the copy up to date throughout the day, just do a simple backup and restore, and stop calling it log shipping.

If you want the copy to stay relatively up to date throughout the day, then yes, it will be mandatory to take periodic log backups on the primary server and restore them on the secondary server. Note that you have to weigh what's more important: keeping a copy of the data up to date, or letting users query stale data uninterrupted. If you restore a log to the secondary server every 15 minutes, you have to kick all the read-only users out every 15 minutes, because you need exclusive access to the database in order to restore the log.

You can read more information about log shipping here:

http://msdn.microsoft.com/en-us/library/ms187103(v=sql.90).aspx

share|improve this answer
    
Link for step by step LS setup: mssqltips.com/sqlservertip/2301/… –  Eric Higgins Sep 6 '12 at 16:51
    
If you don't include the transaction log, then it's not really log shipping, is it? It is happening –  testing Sep 7 '12 at 9:59
    
I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups). I just restored Full Backup and not the Transactional Log Backup and still it is updating the secondary database as per the schedule. I set it to 2 mins..as it contained just a table. –  testing Sep 7 '12 at 10:06
add comment

Log shipping is just what it says - shipping of the transaction log files. Using a full database backup will not copy the transaction log file from the original server. It creates a new LDF file, the size is the same as the size of the original one, but it's just inflated. So you'll get a LDF file with no data but active portion of the transaction log, which is far from what you have in the original transaction log file.

share|improve this answer
add comment

Transactional log backup is the backup of data since last you tack a backup. For to apply the transactional log backup you must have to apply full backup first only then the transactional backup will be valid.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.