Simulating Log shipping – Help needed | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Simulating Log shipping – Help neededHello gurus,
I am trying to simulate log shipping to build a new database for reporting. I use the backup and log files from our OLTP database. The schedule of these backup is as follows…
Full backup – Taken at 1 AM every night
Transaction Log backup – Taken every 15 minutes between 5 AM and 2 AM.
I was not able to understand how to restore a full backup and apply transaction logs every 15 minutes there after while making the DB available to be used by the users.
I have the following questions. I would really appreciate if some of you can respond to them.
1. I use the copy of full backup to restore to reporting database. What kind of recovery option should I be using. My primary aim is to have the DB available to the users as soon the restore is done from full backup. After that I want to keep applying the logs as and when they are ready, which is every 15 minutes. Is it possible for me to have the users access the DB while the transaction log is being restored every 15 minutes? Or do they need to be kept away from accessing the DB during this process? Any example commands (not all of them, but just a sample that uses the correct options) would be grately appreciated.
2. Since I am taking a full backup at 2 AM every night, do I need to restore my reporting databsae from it once every night? Or can I keep applying all the logs ignoring the full backup taken at 2 AM? Please suggest.
3. I can’t use the logshipping option available in the server for several reasons. For one, these two servers are in different domains and a lot of fire walls need to be opened for this to happen. Company policies won’t all for it. However, I can have a process setup to copy the backups (full & tran log) to the destination server without a problem. So, a process that uses these files to constantly restore will work the best for me.
Thank you in advance for all your help.
Do you have only 1 standby server to restore logs in this log shipping setup?
1- Not possible and during the log restore all the users must be disconnected from the database and all the times the database recovery model should be in FULL and database be on STANDBY or READONLY.
2- Its better to restore the full database backup in order to continue the next set of transaction log backups too.
3- Log shipping need not to be SQL Server based, the approach you are referring is also called manual log shipping where the internal SQL login can copy the backup files to the standby server.