Summary: This blog explains how to automate SQL Server database backup using Maintenance Plan Wizard and SQL Server Agent job. If you’re unable to take automatic backup due to corruption in the database, or the backup is corrupted, using the tools available in Stellar Toolkit for MS SQL may help.
One of the critical responsibilities of a SQL database administrator is to get a database backed up successfully. However, it is crucial to maintain regular database backups to reduce the chances of data loss while restoring the database from backup. You can accomplish this by scheduling automatic backup of a SQL Server database.
Taking SQL Server database backup automatically also saves time and the manual effort required to run database backup. There are different methods you can use to create a scheduled backup of a SQL database automatically.
Following are the two methods you can use to schedule an automated backup:
The Maintenance Plan Wizard is an easier and convenient method for creating automated backups with limited options.
You can set a schedule to automatically back up a SQL database using the Maintenance Plan Wizard by following these steps:
Now, from the ‘Object Explorer’ pane, right-click on SQL Server Agent, and you will see a job created automatically for the backup maintenance plan.
While the Maintenance Plan Wizard provides a more effortless and hassle-free way of taking database backup, it fails to provide granular control and configuration options for scheduling automated backups. An alternative is to create an automatic backup job using SQL Server Agent.
Follow these steps to create an automated backup on a scheduled basis using SQL Server Agent job:
|USE Test_Database |
BACKUP DATABASE [Test_Database]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\Test_Database.bak’
Note: You may also add SQL code for creating a differential and transaction log backup.
Note: You may also choose to set up notifications if the database backup job fails.
After executing the above steps, expand the Jobs folder. You will see the new ‘Automate DB Backup’ job. Also, you can see the alerts for the backup job that indicates corruption suspected while taking a backup.
What to do to fix corruption?
If the backup is corrupted, using Stellar Toolkit for MS SQL software can come in handy. The software comes with different tools that help database administrators resolve the most common issues with a SQL database. For example, using the Backup Extractor tool in the toolkit, you can extract all the data from the corrupted backup. Also, you can use the SQL repair tool to fix corruption in a database if required. Download the trial version of the MS SQL toolkit from here to check how it works.
Use the SQL Server Agent job for better control over taking automatic backup of SQL database.
This blog discussed the two different methods to take SQL Server database backup automatically. If you need a point-and-click interface to automate backup on a scheduled basis, you can use the Maintenance Plan Wizard to create a backup maintenance plan. Consider using the SQL Server Agent jobs as explained in the blog for more control and configuration options for scheduling automatic backups.
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.
Stellar Data Recovery has the right Windows Recovery tool for all your data recoveryRead More
Stellar Data Recovery for Mac program performs safe..
A comprehensive photo recovery software to restore photos, music & video files