August 16, 2009 at 4:06 pm
Hi,
I have configured Log shipping in SQL Server 2005. Before configuring Log shipping, We used to have Full backup running dialy, differential at every 8 hrs and Log backup every hr.
Now, before Configuring Log shipping I followed the below steps. Correct me If I'm wrong.
1.Disable the existing Differential & Log backups
2.Performed the full backups and copied to the secondary server and restored the databases WITH NORECOVERY and then configured the log shipping
One thing I'm not clear is "Do we need to completely stop the Differential backups, if we configure log shipping"??
August 16, 2009 at 4:22 pm
Full and Differential backups will not break the log chain on the source database, so you will be able to continue running your full and differential backups after configuring log shipping..
August 16, 2009 at 6:37 pm
Lynn is correct. You'll be fine, and I wouldn't stop any full or diff backups due to log shipping.
August 16, 2009 at 10:11 pm
As masters above have mentioned, it wont break the logshipping process until Enable the Explicit Log backups. Any log backup done will have different (next) LSN and the destination server requires this backed up log to continue restoring database. The whole scenario of logshipping is based on LSN in the header of .trn files and any irregularity in it will break the whole process.
If you have existing test logshipping environment, have a look at the backupset system table on both source and destination servers and you can get an idea on how logshipping works.
August 16, 2009 at 10:23 pm
anam (8/16/2009)
As masters above have mentioned, it wont break the logshipping process until Enable the Explicit Log backups. Any log backup done will have different (next) LSN and the destination server requires this backed up log to continue restoring database. The whole scenario of logshipping is based on LSN in the header of .trn files and any irregularity in it will break the whole process.If you have existing test logshipping environment, have a look at the backupset system table on both source and destination servers and you can get an idea on how logshipping works.
Not quite sure what you mean? Full backups and differential backps won't break the t-log backup chain once the logshipping is setup. The problem that will occur is if the source system has to be restored, in which case log shipping while need to be resynced as well.
August 16, 2009 at 10:52 pm
Sorry for any confusion. I agree with you on your following points
Full backups and differential backps won't break the t-log backup chain once the logshipping is setup.
YES
The problem that will occur is if the source system has to be restored, in which case log shipping while need to be resynced as well.
YES
I was trying to explain a scenario of not taking any explicit log backups along side full or differential database backups. Lets say you are running the following queries as regular job for db backups
BACKUP DATABASE [DBName] TO DISK = 'c:\DBName.bak'
WITH NOFORMAT, NOINIT, NAME = N'DBName - Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP LOG [DBName] TO DISK = 'c:\DBName_yymmdd.trn'
WITH NOFORMAT, NOINIT, NAME = N'DBName', SKIP, REWIND, NOUNLOAD, STATS = 10
Please ignore if there are any errors in query hoping you have got an idea what i meant to say.
Now with log shipping setup you should not run the second query of backing up database logs with your backup database command as it will change the LSN number for the logshipping setup and secondary server will start throwing errors related to wrong trn file to be restored.
August 16, 2009 at 10:59 pm
anam (8/16/2009)
Sorry for any confusion. I agree with you on your following pointsFull backups and differential backps won't break the t-log backup chain once the logshipping is setup.
YES
The problem that will occur is if the source system has to be restored, in which case log shipping while need to be resynced as well.
YES
I was trying to explain a scenario of not taking any explicit log backups along side full or differential database backups. Lets say you are running the following queries as regular job for db backups
BACKUP DATABASE [DBName] TO DISK = 'c:\DBName.bak'WITH NOFORMAT, NOINIT, NAME = N'DBName - Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP LOG [DBName] TO DISK = 'c:\DBName_yymmdd.trn'WITH NOFORMAT, NOINIT, NAME = N'DBName', SKIP, REWIND, NOUNLOAD, STATS = 10
Please ignore if there are any errors in query hoping you have got an idea what i meant to say.
Now with log shipping setup you should not run the second query of backing up database logs with your backup database command as it will change the LSN number for the logshipping setup and secondary server will start throwing errors related to wrong trn file to be restored.
Sorry, but I am trying to understand how the second query will break the log chain? Please understand, I understand the concept behind log shipping, but I haven't personally had to worry about supporting it at this time.
If you are talking about the processing of a log backup outside of the log shipping process, and not including that log backup being shipped to and processed on the destination server, then I think I understand.
However, I think the question being asked was could they continue running differential backups ater setting up log shipping.
August 16, 2009 at 11:40 pm
If you are talking about the processing of a log backup outside of the log shipping process, and not including that log backup being shipped to and processed on the destination server, then I think I understand.
Spot on.
However, I think the question being asked was could they continue running differential backups ater setting up log shipping.
Yes you are right about the initial question being asked. I thought only to warn the user not to take any transaction database log backups along side database backups when log shipping is being configured and running.
August 17, 2009 at 8:12 am
I am not a master and not certified in SQL Server yet, I just took some SQL Server training, but I think that you certainly can do differential backups while setting log shipping, why not? Differential backups simply backup the database change since the last backup (.mdf files, to my memory?) Log shipping simply copies and moves to another server log files (.trn files), which are totally different from .mdf files. Log shipping is nothing but moving the .trn files to another server, and is totally independent from backing up .mdf files.
I hope it helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply