SQL Server Database Automatic Daily Backup

Introduction

This article explains how to set automatic daily database backup of a database in SQL server. We can set the backup location and schedule a backup time in SQL server.

Steps of scheduling a backup

Step 1. Open SQL Server and connect the server. Go to Management and expand the Management.

Step 2. Right click on Maintenance Plans and select New Maintenance. Looks like the below screenshot.

Now enter the Maintenance Plan name in New Maintenance Plan window and click OK.



Step 3

Toolbox and SampleDatabaseBackup – sa [Design] window will be open. Or use your admin login credentials.

Go to Toolbox and click “Back Up Database Task” in General.



Now double click “Back Up Database Task” box in the wizard. Back Up Database Task window will open.



Step 4. Select the backup type as “Full” and Select the Database(s) which we need to set automatic daily backups.

We should select a path in “Back Up Database Task” window where the need to store database backup looks in the server or local machine looks like below screenshot. When done, click OK button.



Step 5. Click Job Schedule Properties and select what are the days and and times you need for the backup. We can choose several options that look like the below screen shots then click OK button.

Step 6. When we close the Back-Up Database Task window will be asked “Save changes to the following items”. Click Yes button.

Step 7

We should check whether SQL Server Agent is running, started or have stopped. SQL Server Agent service is the backend service that will kickoff and execute the backup process. We need to make sure the service is running.


Step 8

Once ran, we can see our database backup file in the specified path which we have selected. We can see the backup as “.bak” format.



Conclusion

This article explained about how to take automatic backup daily the step by step way in SQL server.