In this article, we are going to discuss how to backup SQL database automatically with the help of SQLBackupAndFTP and SQL Server Management Studio (SSMS). Scheduling the backup process ought to be one of the elements that are vital in the life of any DBA.
This simple procedure will certainly save you lots of time and effort in the future.
How to Backup SQL Database Automatically Using SQLBackupAndFTP
Pass these simple steps and you will find out how to backup SQL database automatically with the help of SQLBackupAndFTP.
- First of all download and install SQLBackupAndFTP, then launch it. The connection window will be opened automatically where you have to specify your SQL Server credentials and press “Test SQL Connection” to check the connection.
- Go to the “Selected Databases” section and click the gear button then select all databases you want to backup.
- The next step is to select a place where you prefer to your SQL Server database backups. To do it click the plus button in the “Store backups in selected destinations” section then select a destination place from the list.
- Here is the answer to the question: “How to backup SQL database automatically”. Now you need to create SQL Server backup schedule. To do it turn on “Schedule backups”. You can find “Advanced backup schedule” by clicking the gear button.
That is all, your backup job is created.
How to Backup SQL Database Automatically Using SSMS
If you are looking for the tutorial how to backup SQL database automatically with the help of SSMS follow this instruction:
- From the very beginning log into SQL Server Management Studio (SSMS) and connect to the database. Now go to the Object Explorer window (located on the left) and make sure that your SQL Server Agent is running.
- Open the folder named Managment and find the Maintenance Plan folder. Note, you will need to have SYSADMIN database roleIn to schedule such a plan. Then right-click Maintenance Plan folder and select New Maintenance Plan.
- Enter the name of the Maintenance Plan you are going to create. Press on the calendar icon on the top right highlighted section on the job schedule screen. It will bring up the job schedule screen. In the opened window set up all necessary backup schedule.
- Now choose from the bottom left side of the screen select the Backup Database Task to set up the backup process, and drag the element onto the right window as shown in the picture.
- Few more steps and you will know how to backup SQL database automatically with the help of SSMS. Now you need double-click on the database task. In the opened window set up, the configuration related to the database backup.
- Specify the databases you are going to backup. Also, you can pick the files to expire after a certain number of days or on a specific date.
That is all, your backup plan is ready and all your backups will be made according to the specified schedule. Now you know how to backup SQL database automatically.
How to Restore SQL Server Automatically.
SQLBackupAndFTP allows you not only backup your SQL Server databases automatically, but also restore them. After you create and start a backup job SQLBackupAndFTP starts to create backups according to a selected schedule. All backups will be displayed at the “History & restore” section.
To restore SQL Server database you need select a backup and click “Restore from Backup…” in the opened window click the “Restore” button.