‘D:\MSSQL_DBBackups’ – the first parameter tells the stored procedure where to look Usp_DeleteOldBackupFiles – calls the stored procedure we created earlier Usp_DeleteOldBackupFiles ‘D:\MSSQL_DBBackups’, ‘bak’, 720 In the New Job Step window, under General tab, enter a descriptive job name, under Database select the database which we want the job to work on and under Command: insert the following line: On the General tab, enter a descriptive name and optionally a description for the job: To do that, right click on SQL Server Agent and select New then Job… After running the query, we should end up with a stored procedure:Īfter creating the stored procedure, we need to create a scheduled job with SQL Server Agent which will use the stored procedure with our parameters to delete the old backup files.
It reads the file header to check what type of file it is and will only delete certain types based on the input parameters we choose. xp_delete_file 0 ,Īs it can be seen in this query, an extended system stored procedure xp_delete_file is being used. NVARCHAR (256 ) NVARCHAR (10 ) INT AS BEGIN SET NOCOUNT ON DECLARE NVARCHAR (50 ) DECLARE DATETIME SET = DateAdd (hh, -, GetDate ( ) ) SET = ( Select Replace( Convert( nvarchar, 111), '/', '-') + 'T' + Convert( nvarchar, 108))ĮXECUTE master. In the new query window enter the following T-SQL:ĬREATE PROCEDURE. Right click on the database upon which we want to act and select New Query: If the service is already running, you can skip this step:Ĭreate a user stored procedure which will use the input from the SQL Server Agent scheduled job to delete old backup files. Locate the SQL Server Agent service for the corresponding SQL Server instance, select it and click on Start in the top left of the window, or simply right click on it and select Start. Should the message “Agent XPs disabled” is shown beside it, go to Control Panel/System and Security/Administrative Tools and run Services (the exact location may vary due to different operating systems). In the Object Explorer and check the icon beside SQL Server Agent. SQL Server Agent service must be running. To create a SQL Server Agent scheduled to delete old backup files, we must do the following steps: The advantage of this is the ability to use the same stored procedure across different jobs with different input parameters. It is necessary to create a stored procedure first upon which the SQL Server Agent job we make would call to execution. It requires basic knowledge and understanding of Transact-SQL (T-SQL) scripting, but the process is then entirely automated, so there is no further input from the user needed. There are several options to delete old backup files automatically in SQL Server:ĭelete old database backup files automatically in SQL Server using a SQL Server Agent Jobĭelete old database backup files automatically in SQL Server using a SQL Server Maintenance planĭelete old database backup files automatically in SQL Server using SQL Server Agent:įor this option, the SQL Server Agent service must be installed and running. This can be done manually or automatically. Old database backups are in many cases unnecessary, and it is often prudent to delete those backups to increase available storage. However, strategies relying on frequent backups, although part of a sound strategy, can start causing issues when available storage space becomes limited. Automated backup and restore strategies ensure the recovery plan is successful. While working with a number of databases, creating a recovery plan can be challenging, especially if we want a foolproof design.
0 kommentar(er)
