SQL Server job common schedule and backup tasks

1, Backup database

1. Automatic backup

The automatic remark here refers to the following steps to realize the automatic backup of data at the specified time (the following steps can be directly completed in SQL Server Management Studio, and the code implementation process is mainly described here):

1) The first step is to create a job. You can click SQL server agent in SQL Server Management Studio, select a job, and right-click to create a new job;

2) The second is to enable the job. The SQL statement is as follows:

public void backupEnable(int enable){
		String sql = "EXEC msdb.dbo.sp_update_job "
				+ "@job_name = 'backup', "
				+ "@enabled = "+enable+";";
		//Call database execution
}

3) To set the job, the SQL statement is as follows

//Path the absolute path to the backup file store, for example: D: /test/airrmdb.bak
public void backup(String path){
		String sql = "EXEC msdb.dbo.sp_update_jobstep "
				+ "@job_name = 'backup', "
				+ "@step_id = 1, "
				+ "@command = 'BACKUP DATABASE airrmdb TO DISK = \""+path+"\"  WITH FORMAT;'"; 
		saveBySql(sql);
}

4) Set the job plan, i.e. when to execute and how many times to execute. The SQL statement is as follows

/**
	 * Automatic backup job
	 * @param type A value indicating the execution time of the job. *Freq'u type * is * * int * *, the default value is * * 0 * *, and can be one of these values. 1 once, 4 days, 8 weeks, 16 months, 32 months, relative to * frequency interval *, 64SQLServerAgent service startup time, 128 computer idle time 
	 * @param interval The number of days the job was executed. *freq_interval * is * * int * *, the default value is * * 0 * *, and depends on the value * freq_type *,
	 * @param relativeInterval
	 * @param recurrenceFactor Execution interval
	 * @param startDate
	 * @param startTime
	 */
	public void backupCycle(int type,int interval,int relativeInterval,int recurrenceFactor,String startDate,String startTime){
		String sql = "EXEC msdb.dbo.sp_update_schedule "
				+ "@name = 'backup', "
				+ "@enabled = 1, "
				+ "@freq_type = "+type+", "
				+ "@freq_interval = "+interval+", "
				+ "@freq_relative_interval = "+relativeInterval+", "
				+ "@freq_recurrence_factor = "+recurrenceFactor+", "
				+ "@active_start_date = "+startDate+", "
				+ "@active_start_time = "+startTime+" ;"; 
		//Call database execution
        ....
	}

2. Manual backup

Manual backup is relatively simple. You only need to call one statement

2, Delete data

There is no big difference between the plan part and the backup part. Here are the following statements to delete files, pictures, etc

1) How many days ago was the picture deleted

 

Tags: SQL Database

Posted on Thu, 13 Feb 2020 16:29:51 -0500 by troy_mccormick