Data recovery is vital for most businesses. At Hostek, we understand this and already perform daily file level backups of all servers in our infrastructure. Still, we highly recommend creating maintenance plans within Microsoft SQL to perform daily or weekly backups of your database.
Having local full-level backups of your databases not only provides a redundant backup solution for your SQL databases, it also allows you the ability to restore a database yourself without having to create a support request and wait for our team to finish the restoration of the database.
Other benefits to creating SQL database backup maintenance plans:
Reduces the overall size of database transaction logs when a full level backup is performed.
Provides additional restore point options, when recovery is needed.
Allows for the restore to be completed to an alternative database if needed.
Full backup performed (.BAK), instead of only having file level (database .MDF and .LDF files).
Prerequisite checks before proceeding
1. In order to make use of the built-in Microsoft, maintenance plans your server must have the SQL Agent installed, which is a separate SQL service only available on paid versions of SQL. This means that you can not use these maintenance plans on the free (SQL Express) version.
Note: If you need to upgrade your SQL version, then please create a support request with our team.
2. Prior to setting up the scheduled backup plans, be sure to have a good general idea of how much disk space the backup sets will take on the server. You want to make sure that your server is capable of storing the number of backups on the server without running out of disk space. Running out of disk space on the server can cause important services (such as the web server, SQL, etc…) to not function correctly.
Note: If you need to upgrade your disk space, then please create a support request with our team.
Create scheduled database backups
We recommend checking the above prerequisite checks before proceeding. When you are ready to proceed with setting up this scheduled maintenance task to perform full-level backups of your database follow the steps below:
2. Open SQL Server Management Studio (SSMS) on the server and connect to your database instance with an administrative user (such as the built-in SA user, or server administrator user).
3. Expand the Management list item, then right-click on Maintenance Plans and choose ‘Maintenance Plan Wizard’.
4. A new window will open for SSMS > Maintenance Plan Wizard. You might see a getting started guide, in which case you will click Next. Enter in the Name and Description of your choice.
5. Now click on the Change… button to change the schedule of the task.
6. You will now want to set the frequency at which this task will perform. Then click OK.
Example: In the screenshot below we have this task running daily at 2 AM server time.
7. Click Next, then choose the option for Back Up Database (FULL). This will allow the databases to have full level backups, which is highly recommended. Then click Next.
8. Click Next once again, then choose the database(s) that you desire to be backed up each time this scheduled task runs.
We recommend choosing All databases if you have enough server disk space. If desired you can choose specific databases that this task applies to here.
We also recommend checking the box for ‘Ignore databases where the state is not online’, so that only your actively used databases are backed up.
9. In the Destination tab, we recommend setting the folder these backups get created in. We also recommended enabling the option for ‘Create a sub-directory for each database’ for a more organized backup set if you have a large number of databases.
10. In the Options tab, we highly recommend setting an expiration date for the backups. If an expiration date is NOT set, then your servers disk space might fill up due to the backups never being deleted.
Commonly, for daily task, we see this setting set to: Expire after 7 days
Commonly, for weekly task, we see this setting set to: Expire after 30 days
Again, this option is completely up to you and how long you want to retain local backups for on your server. We also recommend checking the box for ‘Continue on error’, so that if one database fails the rest execute as intended.
11. Click Next, Next, then Finish to proceed and set up this maintenance plan.
12. If you ever need to change a setting to this maintenance plan, then right click on the maintenance plan and choose Modify.
