Configure Logshipping for SQL SERVERVER 2008

--LOG SHIPPING USING T-SQL

select @@SERVERNAME

--backup and restore the database from primiry server to secondary server initially

backup database bbaa to disk='\\10.50.11.149\Backup\bb\bbbb.bak'

go

restore filelistonly from disk='\\10.50.11.149\Backup\bb\bbbb.bak'

go

--restore the database

restore database finalTest from disk='\\10.50.11.149\Backup\bb\bbbb.bak' with move 'bbbb' to 'L:\aaaabbbb\bbaa.mdf', move 'bbbb_log' to 'L:\aaaabbbb\bbaa.ldf',norecovery

go

--primary --> bbaa

--secondary --> AB

--1) adding primary database 

use master

exec sp_add_log_shipping_primary_database @database='bbaa',

@backup_directory='\\10.50.11.149\Backup\bbSource',

@backup_share='\\10.50.11.149\Backup\bbSource',

@backup_job_name='bbBackup_job',

@backup_compression=1,

@overwrite = 1

go

-- 2) adding job schedule for backup job

use msdb

exec sp_add_jobschedule

@job_name='bbBackup_job',

@name='backupSchedule',

@freq_type=4,

@enabled=1,

@freq_interval=1,

@freq_subday_type=0x4 ,

@freq_subday_interval=1

go

-- 3) attaching schedule

use msdb

exec sp_attach_schedule @job_name='bbBackup_job',

@schedule_name='backupSchedule'

go

-- 4) enable/disable

use msdb

exec sp_update_job @job_name='bbBackup_job',

@description='backing up...',

@enabled=1

go

--5) add job alert

use master

go

exec sp_add_log_shipping_alert_job;

--6) adding information about secondary database to primary database

use master

exec sp_add_log_shipping_primary_secondary @primary_database='bbaa',

@secondary_server='BETASQL2K8',

@secondary_database='finalTest'

go

----- primary over ----

--start secondary

--1) adding details about primary server in secondary server

use master

exec sp_add_log_shipping_secondary_primary @primary_server='BETASQL2K8',

@primary_database='bbaa',

@backup_source_directory='\\10.50.11.149\Backup\bbSource',

@backup_destination_directory='\\10.50.11.149\Backup\bbDestination',

@copy_job_name='copyJob1',

@restore_job_name='restoreJob1',

@overwrite = 1

go

--2) adding job schedule for copy job

use msdb

exec sp_add_jobschedule

@job_name='copyJob1',

@name='copylog',

@freq_type=4,

@enabled=1,

@freq_interval=1,

@freq_subday_type=0x4 ,

@freq_subday_interval=2

go

-- 3) attaching schedule

use msdb

exec sp_attach_schedule @job_name='copyJob1',

@schedule_name='copylog'

go

--4) adding job schedule for restore job

use msdb

exec sp_add_jobschedule

@job_name='restoreJob1',

@name='restorejob',

@enabled = 1,

@freq_type = 4,

@freq_interval = 1 ,

@freq_subday_type = 0x4,

@freq_subday_interval = 5

go

-- 5) attaching schedule

use msdb

exec sp_attach_schedule @job_name='restoreJob1',

@schedule_name='restorejob'

go

--6) adding secondary database

use master

exec sp_add_log_shipping_secondary_database @secondary_database='finalTest',

@primary_server='BETASQL2K8',

@primary_database='bbaa',

@overwrite = 1,

@restore_mode=1,

@disconnect_users=1

go

--7) update copy and restore jobs

use msdb

exec sp_update_job @job_name='copyJob1',

@description='copying...',

@enabled=1

go

use msdb

exec sp_update_job @job_name='restoreJob1',

@description='restoring...',

@enabled=1

go

--startind job (backup job)

use msdb

exec sp_start_job @job_name='bbBackup_job'

go

SELECT @@version