This article provides summary how to schedule MachPanel Database delete via Task scheduler.
This article applies to MachPanel all build versions.
Database Backup can be taken using the configuration studio and based on setting "Keep Last ----- Backup.", the older file and database backups get removed automatically in case the Web and Database Server is on same server. In the case of having Database hosted on a different server, the removal of the old database backup files will need to be done using the execution of SQL script through scheduling a task using Windows built-in Task Scheduler. It is a one-time configuration process and the removal of old database files will be automated afterwards, based on the settings configured while scheduling the task.
There can be different ways to handle database backup removal within the Management studio / SQL Maintenance plans and outside of that via PS Scripts. Customers can use whatever method is easy to adopt. In case of SQL server agent job, it must be added to all SQL nodes involved in Custer / AG / mirroring etc.
Below is one way to schedule a task for auto removal of older backups.
- Any user with full administrative rights on folder (where script file is placed) and SQL server service, can configure this task. The windows default administrator account or one with similar rights should be used.
- Open the script file using Notepad and make the required changes in the highlighted part i.e. Name of your Database and the How much previous backups you want to keep.
Please find the complete code in attachment.
-
When the above-mentioned prerequisites are verified the next step is to schedule the task using Task Scheduler on your SQL server. Navigate to Search and type “Task Scheduler”, right click on it and click “Run as Administrator” as shown below.
- Below window will appear after you run the Task Scheduler.
Step -1: Click on Task Scheduler present in the Object Explorer present at left side of screen and select “Create Task” and the new task addition window will appear on your screen. In the Create Task window under the General tab you will have to provide Name of Task, description of task and the security options should be configured as shown below and click OK at the end:
Step-2: In
the Triggers tab, click on ‘New’ to add trigger, a new window will appear by
name “Edit Trigger” in which you will configure the desired settings related to
trigger this task. You have to select date/time and also Enable the trigger at
the end of window and click OK.
Step-3: In the Actions tab in which you will have to select the action for your task and under the settings Program/script you will have to look for SQLCMD.EXE in your SQL installation directory and select the EXE file. In the Actions tab, click on ‘New’ button and a new window will appear to add/edit the desired action.
Sample input is mentioned below, but the input should be according to your SQL installation details.
Program/Script:
"C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.EXE"
Next step is to add the arguments value and in this case the argument sample input should be like:
-S "MP-PSS-2016\MACHPANELSQL08R2" -i "C:\Program Files\MachSol\RemoveBackups.sql" -E
You have to enter based on your SQL installation.
Note: Values must be enclosed in double quotes.
-S = SQL server name\instance name (EDIT it accordingly)
- It can be "AG Listener Name", "Cluster name" or "SQL Server Name / Instance Name". You can get the "Database Server" name from MachPanel Configuration Studio on MachPanel Server.
-i = Path for the script file to be executed through Task Scheduler. (File is attached with document)
-E = for trusted connection (no value expected) and click on OK.
Step-4: Next tab is the Conditions tab, which specifies the execution of task based on the conditions i.e. the task will not Run if any condition set here is not true.
Step-5: The next tab is the Settings tab in which you will have to specify the additional settings that will affect the behavior of the task as shown below and save the desired changes:
Step-6: The last one is the History tab which basically holds the history of the task when it is being executed through the Task Scheduler.