SQL Server Log Shipping -- Complete Overview.. Part- VI

Next Chapter »

Step 4 Script

EXEC standby_server_name.master.dbo.restore_database_backups

This single line is used to fire the restore_database_backups stored procedure on the standby server. Note that I have used a fully qualified object name for the stored procedure. You will want to do the same.

Run this script from the Master database.

Scheduling the Database Backup Job

Once this multi-step job is created, the next step is to schedule it. It is important that this job only be scheduled to run once a day. If it does not, it will get out of synch with the transaction log backups, discussed in the next section. Select a time to run it when it will produce the least affect on your users.

The Transaction Log Backup Job

Now its time to look at the second job, the job used to backup the transaction log, move the log backup to the standby server, and then fire the stored procedure used to restore the transaction log. It consists of the three steps outlined in this table:

Step ID	Step Name	Step Type	On Success	On Failure
1	Backup Log	T-SQL Script	Goto Next Step	Quit With Failure
2	Copy Log	Operating System Command	Goto Next Step	Quit With Failure
3	Restore Log	T-SQL Script	Quit With Success	Quit With Failure

Let’s first talk about each step in general, then we will discuss each one in depth.

Step 1 is to back up the transaction log onto its backup device. Note that if this step fails that we “Quit With Failure”. No point in copying a failed backup.

Step 2 is copy the backup device from the production server to the standby server using the XCOPY command.

Step 3 fires the stored procedure on the standby server that is used to restore the transaction log on the standby server.

Now that we have a look at the big picture, lets let at the script behind each of these steps.

Step 1 Script

BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE

WAITFOR DELAY ’00:00:05′

Where database_name is the name of your database, and log_backup_device is the name of the backup device used for the log backup.

The “BACKUP LOG” command does a transaction log backup of the database to the named backup device. The “WITH INIT” option is used to delete any previous database backups from the backup device before the backup is made. The “NO_TRUNCATE” option is used to tell the “BACKUP LOG” command not to truncate the log, which is the default behavior of this command. We don’t want to truncate the log each time we do a transaction log backup because we might need it for some other reason later.

The “WAITFOR” line is used to give the previous time to complete before the next step in the job executes.

Run this script from the Master database.

Step 2 Script

xcopy g:mssql7backupbackup_device_name.bak   \standby_server_nameg$mssql7backup /c

For this operating system command to work, the account used to run this job must have local administrative rights on both the production and standby servers. You will of course have to substitute your paths and file names. The “g$” refers to the local administrative share on the standby server where the backup device is to be copied. The “/c” option tells the command to continue copying even if an error of any kind occurs.

Step 3 Script

EXEC standby_server_name.master.dbo.restore_log_backups

This single line is used to fire the restore_log_backups stored procedure on the standby server. Note that I have used a fully qualified object name for the stored procedure. You will want to do the same.

Run this script from the Master database.