May 13, 2014 at 12:20 pm
Hi guys I have a couple of quick questions about log shipping.
I have set up a quick example on our test box and so far as I can tell the process goes something like:
1) Create full backup of DB
2) Restore DB on secondary server
3) Every X minutes take log backup from server 1 and restore on server 2
A couple of questions here.
First) We do a full backup of our DB every Sunday, and then a log backup every night until the next Sunday to enable point in time recovery. Will the fact that the log shipping is doing log backups and then deleting them after x amount of hours break our maintenance plan log chain? Will this stop us restoring to a point in time?
Second) How does the restore on the secondary server work. Does it drop the database on secondary, restore from the initial bak file and apply the log backups. If this is the case would the restore time on secondary get out of hand after a while (i.e after 2 weeks you have to restore 2 weeks worth of log files created after the initial .bak file)??
Any help would be great.
May 13, 2014 at 1:54 pm
william.rees.howells (5/13/2014)
First) We do a full backup of our DB every Sunday, and then a log backup every night until the next Sunday to enable point in time recovery.
That will break the log shipping every night. Unless you're fond of re-initialising the entire thing every day, stop doing your own log backups. When log shipping, it must be the only thing taking log backups
Will the fact that the log shipping is doing log backups and then deleting them after x amount of hours break our maintenance plan log chain? Will this stop us restoring to a point in time?
Yes
Second) How does the restore on the secondary server work.
It restores each log backup after it's copied.
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
May 13, 2014 at 2:12 pm
Hi thanks for the response.
I have three further questions:
1) After the initialisation is done the first time from the BAK file, does the job just add the data from each new TRN file every time a new one arrives, or does it actually have to restore from the BAK file all over again every time the job runs and then apply all of the log files in order?
2) Scenario: I am going to get rid of the maintenance plan and run with log shipping. What happens now. Does log shipping ever take another full BAK file or will I just end up with hundreds of log files?
3) Scenario: I am going to get rid of the maintenance plan and run with log shipping. Suppose I want to make a copy of the database so I take a full .bak backup of the database. Do I need to leave this in the log shipping folder with all the .trn files to maintain ability to restore to point in time? Will this break anything?
May 13, 2014 at 2:30 pm
william.rees.howells (5/13/2014)
1) After the initialisation is done the first time from the BAK file, does the job just add the data from each new TRN file every time a new one arrives, or does it actually have to restore from the BAK file all over again every time the job runs and then apply all of the log files in order?
Thought I answered that one. It restores the transaction log backup.
2) Scenario: I am going to get rid of the maintenance plan and run with log shipping. What happens now. Does log shipping ever take another full BAK file or will I just end up with hundreds of log files?
You still need to take full backups yourself on a regular basis. Otherwise have a nice month restoring thousands of log backups when you need to restore (assuming that none have been deleted)
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
May 14, 2014 at 1:59 am
william.rees.howells (5/13/2014)
Hi guys I have a couple of quick questions about log shipping.I have set up a quick example on our test box and so far as I can tell the process goes something like:
1) Create full backup of DB
2) Restore DB on secondary server
3) Every X minutes take log backup from server 1 and restore on server 2
ignoring the process required to setup log shipping, it works like this
william.rees.howells (5/13/2014)
First) We do a full backup of our DB every Sunday, and then a log backup every night until the next Sunday to enable point in time recovery. Will the fact that the log shipping is doing log backups and then deleting them after x amount of hours break our maintenance plan log chain? Will this stop us restoring to a point in time?
In this case, yes. Unless you increase the file retention period or backup these files to tape before deletion you will have no point in time recovery, since the files have been removed.
Also ensure that the log shipped database is not a part of any other backup plan which is taking log backups, only the Primary agent backup job should backup the log to the specified location.
william.rees.howells (5/13/2014)
Second) How does the restore on the secondary server work. Does it drop the database on secondary, restore from the initial bak file and apply the log backups. If this is the case would the restore time on secondary get out of hand after a while (i.e after 2 weeks you have to restore 2 weeks worth of log files created after the initial .bak file)??Any help would be great.
The database is left in either restore or standby mode which allows subsequent log restores to take place. The database is initialised only once at the beginning of the plan. There is an option that specifies whether to disconnect users before attempting to restore the log backups, by default this is usually selected.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply