SQL Server Log Shipping -- Complete Overview.. Paert - V

Next Chapter »

Create the Backup Jobs

Now that we have learned how to restore our databases and transaction logs, its about time we learn how to back them up, move them from the production server to the standby server, and then fire a remote stored procedure on the standby server to perform the necessary restore.

This is accomplished using two multi-step SQL Server jobs. One job, as you might expect, if for backing up the database, and the second job is for backing up the transaction logs.

The Database Backup Job

Let’s begin our look at these jobs with the job used to backup the database on the production server, move it to the standby server, and then fire the stored procedure on the standby server to perform the database restore. It consists of the four steps outlined in this table:

Step ID	Step Name	Step Type	On Success	On Failure
1	Truncate Log	T-SQL Script	Goto Next Step	Goto Next Step
2	Backup Database	T-SQL Script	Goto Next Step	Quit With Failure
3	Copy Backup	Operating System Command	Goto Next Step	Quit With Failure
4	Restore Database	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 truncate the transaction log. Why do we want to do this? The reason for this is that when we perform the transaction log backups (to be discussed in detail later), we won’t at that time truncate the log. Instead, we will do it here. If we don’t do it here, and we don’t truncate the log when we back up the log, then it will grow forever. Even if this step of the job should fail, we still want to backup the database. That’s why I specify “Goto Next Step” if this step should fail.

Step 2 is to backup the database onto its backup device. Note that if this step in the job fails that we “Quit With Failure”. No point in copying a failed backup.

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

Step 4 fires the stored procedure on the standby server that is used to restore the database 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 WITH TRUNCATE_ONLY

WAITFOR DELAY ’00:00:05′

Where database_name is the name of your database.

The “BACKUP LOG” line is used to truncate the transaction log of the database.

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

Run this script from the Master database.

Step 2 Script

BACKUP DATABASE database_name TO database_name_backup_device WITH INIT

WAITFOR DELAY ’00:00:05′

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

The “BACKUP DATABASE” does a full 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 “WAITFOR” line is used to give the previous step time to complete before the next step in the job executes.

Run this script from the Master database.

Step 3 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.