Partitioining and Merging in MS SQL Server 2008

The alter partition function statement can be used to merge or split a partition based on values. The following partition function creates partitions:

CREATE PARTITION FUNCTION AutoPartsRangePFl ( int ) AS RANGE LEFT FOR VALUES (1,500, 1000); GO

The following function can merge the partitions between boundary values 1 and 500 and between boundary values 500 and 1000 to create a single partition between boundary values 1 and 1000:

ALTER PARTITION FUNCTION AutoPartsRangePF1 ( ) MERGE RANGE (500);

You can script a current job by using the SQL Server Management Studio (SSMS). You can highlight the job and choose Script Job as. You can script the job to a file or to the query window for editing. Once you have scripted the job, you can run the script to create the jobs on DB02.You can use sp_add jobserver to move the jobs from DB01 to DB02. This stored procedure can be used to assign a job to run on a different server. The job must exist on a source server. You must have a target server registered to assign a multiserver job to it. In this scenario, DB01 has the two jobs. You must register DB02 as the target server. The following code assigns the multiserver job Backup Job Orders to DB02:

USE msdb ; GO EXEC dbo.sp _add_jobserver@job_name = N' BackupJobOrders ',@ server_name = DB02; GO