How to Take SQL Server Database Backup Automatically

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.

Methods to Schedule a Backup

Following are the two methods you can use to schedule an automated backup:

Method 1 – Taking Database Backup Automatically Using Maintenance Plan Wizard

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.

Method 2 – Taking Database Backup Automatically Using SQL Server Agent Jobs

Follow these steps to create an automated backup on a scheduled basis using SQL Server Agent job:

USE Test_Database 
GO
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.

End Note

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.

Related Post

Exit mobile version