다음을 통해 공유


Automatic Daily Database Backup To Specific Database In SQL Server

Introduction

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

Steps for Backup

Step 1

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

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image001.jpg

Step 2

Right click on Maintenance Plans and select New Maintenance looks like below screenshot.

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image002.jpg

Now enter any related Maintenance Plan name in New MaintenancePlan window and click ok.

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image003.jpg

Step 3

Toolbox and SampleDatabaseBackup – sa [Design] window will be open. Go to Toolbox and click “Back Up Database Task” in General

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image004.jpg

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

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image005.jpg

Step 4

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

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image006.jpg

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, finally click ok button.

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image007.jpg

Step 5

Click Job Schedule Properties and select what are the days and which times need backup. We can choose many options that look like the below screen shots then click ok button.

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image008.jpg

Step 6

When we close the Back-Up Database Task window will be asked “Save changes to the following items” so we should click Yes button

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image009.jpg

Step 7

We should check whether SQL Server Agent is Started or Stopped. Always start SQL Server Agent if stop means you will not generate a backup.

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image010.jpg

Step 8

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

http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/automatic-daily-database-backup-to-specific-database-in-sql-server/Images/image011.jpg

Conclusion

This article explained about how to take automatic backup daily the step by step way in SQL server. This article helps students, freshers and those who are new ro learning SQL Server.