Setting up Automated SQL Database Backup without compromising server performance
About the Project
Client is running an industry infosystems on our Windows SSD VPS Plans from the last 2 years. He is running various applications based on IIS and MSSQL Server. Client host websites on the IIS webserver with connecting MSSQL Database for his client as well as for his own project on our ultra-fast Windows SSD VPS.
We receive their queries very often but this time it is was a different case and we would like to put it here.
THE PROBLEM
As mentioned in the overview, the client is running his various applications based on IIS and MSSQL Express Edition on his Windows-based SSD VPS. Now, he is looking to perform database backup and asks us to set up a schedular process to take daily and weekly database backup process.
We do a daily backup with the retention period of 2 weeks and weekly backup with the retention period of 6 months. All daily backups need to be taken at 3 AM eastern time, and weekly backup at 4 AM every Sunday.
MSSQL Web edition provides SQL agent service, which provides auto-backup from SQL management studio. But, the client was running the SQL Express edition, and it doesn't provide SQL agent features for auto backup management. We need to set up the entire backup task manually.
OUR SOLUTION
First of all, we have tried to use the hosting panel scheduler to run the backup as it was the easiest option, but it consumed more resources on the server. It was resulting in an unstable server. Hence, we opted for this option.
To fulfill clients' requirements, we have installed a free SQL Backup Master tool on his server and configured daily backup at the folder (C:BackupDBDaily) with the retention period of 15 days.
We have also used some formatting for the database backup as well.
Daily Full Backup :
File name: dbnameyyyymmfullDaily.bak
Retention: 15 Days
Regarding the weekly backup, we used the windows task scheduler process that simply takes one of the daily backup files each week and copies to a weekly folder (C:BackupDBWeekly) with the retention period of 6 months. We have added a batch file to copy the last backup file every week and copy it to the mentioned folder. There was some issue with the task scheduler, which is also fixed.
For Weekly backup, we used below formatting for the database backup.
Weekly Full Backup :
File name: dbnameyyyymmfullWeekly.bak
Retention: 6 Months
We also recommend client to review their daily and weekly backups regularly and remove unwanted backup files. Also, we set the task scheduler to run as Administrator user.
THE RESULTS
We fixed some of the issues that occur in between the setup and finally it is done as per the client's requirement. We scheduled this ticket for a few days to monitor that DB backup is working as expected. Client was happy with this back up and we have finally closed that ticket.