Ignacio A. Salom Rangel (7/24/2010)
Great piece Leo. I think for DBA's with less experience (Like myself) it would have been nice if the article was little more detailed, but then I guess it would be a book instead of an article.:-D
Thanks for it I'm experimenting a bit with it. Could you please tell me where could I get more detailed information regarding the two Jobs you created. Thanks in advance!
Unfortunately, you need implement those jobs by yourself. I did describe the steps that each job should have and pieces of code in the article.
"To keep log shipping near real time, I created two additional jobs. The first job is on the source server and will be started by the alert from a trigger on the table ls_backupset. This job has several steps such as copying database log files to the destination server based on the information from ls_backupset and CopySetHistory tables. When each log file is copied, the record of success or failure becomes inserted to the history table CopySetHistory. To define the log backup file’s name the last two characters have to be taken out from the file’s name in table msdb.. backupset. This is how Microsoft writes it in the name field. It can be done while inserting the records to the table ls_backupset and at the same time extension trn can be added at the end of the file’s name.
Then, this job starts a second job which is located on the destination server. It can be done many different ways. For example, one way is through the linked server option between source and destination servers. The destination server job is going to insert all new records from the source control tables: ls_backupset and CopySetHistory.
To restore log files,all database connections must be killed first. I developed 3 stored procedures. The first one is restoring one log file based on the next submitted parameters – database name, log file path and name, standby file path and name. The second one is the wrapper for one database that allows us to restore multiple log files for this database in a loop. The third one is the wrapper which allows us to restore log files for multiple databases on the same server. Remember that table BackupDBInfo has all the necessary information for each database and this allows us to construct a copy and restore statements with dynamically built code inside the stored procedures. Also, it permits us to make the procedures generic. "