Multiple log shipping

hungnnhungnn Posts: 18
edited October 9, 2008 1:07AM in SQL Backup Previous Versions
Hi

I got the scenario when I have one source database and I want to logship it into two different destination databases. The way I did was to create two different log shipping jobs to accomodate this and make sure the time frame does not collide each other. I do not know whether this is the standard way to do it or you guys have another ideal?

The second issue I got is: When doing logshipping from source to destination server the restore job in the destination database normally failed around 30% of the time with the error message: " The log in this backup set begins at LSN.... which is too late to apply to the database. An earlier log backup that includes LSN... can be restored". Does it mean my logshipping does not setup properly?

Thanks
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    When you perform log shipping, there should only be one process backing up the transaction logs so that there is known continuous chain of log backups. If you set up two backup jobs, you will need to apply the backups from both those jobs to both the secondary databases.

    You can start off by understanding how log shipping is performed in SQL Backup by reading this article. Assuming you have set up log shipping between a pair of SQL Server instances, here's roughly what you'll need to do in order to log ship to additional standby databases (ServerX):

    - ensure the log backups are in locations accessible by all the standby databases
    This could be the initial location specified during the log shipping setup, or you may want to copy the backup files to additional folders. If the latter, you'll need to modify the backup job using Query Analyzer/Management Studio to add additional COPYTO commands.

    - initialize the database on ServerX with the NORECOVERY/STANDBY option
    If you have a recent full database backup, you can use that, and only restore all transaction log backups created after that full backup. If not, you'll need to perform a full backup and restore.

    - create a restore job on ServerX using Query Analyzer/Management Studio
    You can use the restore job created on the initial secondary server as a template. You'll probably need to modify the FROM DISK and MOVETO options, but should should be about it.

    In short, you'll need to ensure that:
    - the backup files are accessible by all secondary servers
    - the databases on all secondary servers are in recovery mode
    - restore jobs are created on all secondary servers to restore the transaction logs
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • hungnnhungnn Posts: 18
    Hi Petey

    Thanks for your prompt email. Just one question about your procedure:

    You set initialize the database on ServerX with the NORECOVERY/STANDBY option. How can I do that by using command line or Query Analyzer?? and because I already setup the log shipping between the source and another destination server, can I use the full backup and transactions log backups of this pair? Sorry for the dummy question.

    Is there any other option beside the logshipping. Previously I use attach/detach method and it is not safe at all with the source server. I saw you have the schedule backup jobs so I can scheduly backup the database in the shared folder. Is there anyway to automate the restore process of destination server?

    Regards
    Hung Nguyen
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    - initializing the database
    If you have a recent full backup, just use that to restore the database in non-recovery/read-only mode. After that, just ensure that the secondary server has access to only the transaction log backups created after that full backup.

    >> Is there any other option beside the logshipping?
    What is your objective? Maintain a read-only copy of the database, maintain a standby database for high availability purposes, maintain a copy of the database for development use etc?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • hungnnhungnn Posts: 18
    Hi Petey

    Beside of maintaining logshipping between a pair of working Server and backup server I just want to maintain the laptop incase of power failure user can still access database by using this laptop. I just want to backup database from the working server into this laptop (may be once or twice per day). We can automatically schedule the backup but cannot automate the process of restore into laptop. This is the way that I am still investigating the Redgate software

    Thanks
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    >> I just want to backup database from the working server into this laptop (may be once or twice per day).

    I assume you are referring to full database backups, which you want to restore on your laptop. The SQL Backup GUI presently does not allow you to schedule a restore. You will have to do that manually, by setting up a SQL Backup Agent job using Enterprise Manager/Management Studio. Set up a T-SQL job step, and use the SQL Backup extended stored procedure to run the necessary commands.

    Here are some points for consideration:

    - your laptop needs access to the full database backup file(s)
    The SQL Backup Agent service startup account on your laptop will need to have access to the backup file(s). It could access the files directly by creating a network share on your production server (not a good idea) or you could modify the backup job on your production server to copy the files over to your laptop, using the COPYTO option. I would suggest the latter option because of the next point.

    - your laptop needs to restore the right files
    SQL Backup can use file search patterns to identify files to restore from e.g.
    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [h:\backups\AdventureWorks_FULL*]" '
    will perform a full database restore using all files matching the pattern AdventureWorks_FULL* in the h:\backups\ folder. That means that you should only have one set of backup files in that folder at any one time.

    - the restored files need to be moved out of the original folder
    After the restore, you should move the restored files out of the original folder, so that the next time the restore runs, the same files won't be picked up for the restore. You can do this by using the MOVETO option e.g.
    EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks FROM DISK = [h:\backups\AdventureWorks_FULL*] WITH MOVETO = [h:\restored\]" '
    will move all backup files used for the restore from the h:\backups\ to the h:\restored\ folder.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.