Hello All, We are going to start new batch from next week. message/call or mail us for more details.

18 August 2012

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 NameStep TypeOn SuccessOn Failure
1Truncate LogT-SQL ScriptGoto Next StepGoto Next Step
2Backup DatabaseT-SQL ScriptGoto Next StepQuit With Failure
3Copy BackupOperating System CommandGoto Next StepQuit With Failure
4Restore DatabaseT-SQL ScriptQuit With SuccessQuit 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.

No comments:

Post a Comment