December 14, 2009 at 8:31 am
I just implemented Log shipping. Everything is working fine so far.
My principal Server backup setting used to be like this:
- Weekly FULL backup, nightly DIFF backup and hourly LOG backups
I disabled the LOG backups in order to do the Log shipping backups and be restored in the standby server automatically. Everything else is working the same (full and diff backups)
Since is the first time that I do this, I have some questions:
1)Should I keep the differential backups in the primary server?, if so, why?
2)If the log shipping is being restored in my standby server/database, how exactly works the restore process for that database? I get confuse with the differential and log shipping because I do understand that Log shipping is restoring the last transactions, if so, why I need the differential backups?
3)What are the steps to restore a StandBy server/database? Since the restore process is only the Logs. What happen with the full backups, diff of the primary server?
I was trying to find these answers in the internet, but I don’t find explanation about the full and differentials restores in the standby. Plus I don’t find the way to restore a standby database.
Any one has worked with log shipping and restores it? Can you advice please?
December 14, 2009 at 12:01 pm
The reason you need full backups is to have a point to restart the log shipping mechanism if it ever breaks, or you recover the standby server for whatever reason (you should do this at least once to test everything).
Having the differentials is not mandatory, but can save you some time perhaps, it really depends on how often you are taking a full backup and what the transaction log backup interval is... as well as how large your full database backup is. Generally speaking all you normally need is full backups and the transaction log backups.
Bringing the standby server to on-line status is as simple as issuing the restore database with recovery command on your standby server.
The probability of survival is inversely proportional to the angle of arrival.
December 14, 2009 at 12:05 pm
Yes, I do understand that.
But, since the logs are being restored in the standby. i will need to do a restore steps like:
Restore las Fulbackup withnot recovery
Restore last diff with not recovery
Restore last log with recovery ?
If so, What is the reason to restore the logs in the log shipping process ?
December 14, 2009 at 12:08 pm
Forgot to mention:
I have weekly full backups and hourly log backups
full backup size is 4GB
.tran is 1GB
so, evern if I have my differential sets. I can work just fine with the full and logs bakcups for the Stand by ?
December 14, 2009 at 12:11 pm
I'm not sure the diffs help you here in that you would likely fail to the standby, not recover the primary. If you then wanted to fail back, you're have to restore a full from the new primary (old standby) and then logs.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 14, 2009 at 12:16 pm
That (seems to me anyway) a reasonable size backup file. If you are actively restoring the transaction logs to the standby server as they are available every hour (with standby or with no recovery) you will have nothing else to do but issue the "restore database xxyyzz with recovery" command when you want to bring it on-line.
You may also have to run a script that maps the login IDs with the UserIds in the database... but that's another story. 😉
The probability of survival is inversely proportional to the angle of arrival.
December 14, 2009 at 12:18 pm
I got it now.
Its just a different way to restore in time. I just need to restore the last one that hasnt been restored. or maybe the T-Log of the primary server .
thank you 🙂
December 14, 2009 at 1:30 pm
Ok.. Im doing some testing and I force the primary server to have an error so I can bring to live my StandBy database
the last log file wasnt restored, so I need to do it manually. I'm trying to birng to live the last log file. but I get an error. Am I missing something?
Restore log [backup] FROM DISK 'c:\Logshipping\backup_20091214200600.trn' with recovery
this is the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'c:\Logshipping\backup_20091214200600.trn'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
December 14, 2009 at 1:46 pm
MTY-1082557 (12/14/2009)
Ok.. Im doing some testing and I force the primary server to have an error so I can bring to live my StandBy databaseRestore log [backup] FROM DISK 'c:\Logshipping\backup_20091214200600.trn' with recovery
this is the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'c:\Logshipping\backup_20091214200600.trn'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
You forgot the =
Restore log DBname FROM DISK = 'c:\Logshipping\backup_20091214200600.trn' with recovery
The probability of survival is inversely proportional to the angle of arrival.
December 14, 2009 at 2:04 pm
Forgot the little detail.
I tried and I found I can do either way:
Option a
RESTORE DATABASE [backup] WITH noRECOVERY
Restore log [backup] FROM DISK = 'c:\Logshipping\LastTran.trn' with recovery
Option b
Restore log [backup] FROM DISK = 'c:\Logshipping\LastTran.trn' with recovery
In order to have the last transactions. I did a T-log backup from the primary and resotre this one.
Thank you
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply