This blog shows 3 ways for users how to automate SQL backup and set a backup schedule to facilitate the process.
Updated by Iris Lee on 2023/06/27
Maintaining frequent SQL Server backup is always critical for SQL database users not only because it reduces data loss and it also supplies a source for disaster recovery in event of ransomware, cyberattacks, or other accidents. And it will save you a lot of effort and energy if you back up the database repeatedly and periodically. Taking SQL Server backup automatically further helps administrators and makes their work easier.
This blog shows 3 ways for users how to automate SQL backup and set a backup schedule to facilitate the process.
The Maintenance Plan creates a maintenance plan for SQL Server users to perform different management operations, such as backup, data updates, etc. But before that, you must hold the sysadmin fixed server role and enable the Agent XPs Server Configuration Option.
1. Launch SQL Server Management Studio (SSMS), then establish a connection to a server instance.
2. Click the Management folder in Object Explorer, then right-click on Maintenance Plans and select New Maintenance Plan. (The Maintenance Plan Wizard leads you throughout the way but provides fewer configuration options.)
3. Choose Backup Up Database Task in the Maintenance Plan Tasks toolbar.
4. Customize the backup configurations in the pop-up window after double click the created task. For over one database backup, click Create a backup file for every database.
5. You may now automate the backup by clicking on the Subplan Schedule calendar icon.
6. Click Save and the job will run as scheduled. You can find the job under SQL Server Agent.
SQL Server Agent is a part of Microsoft SQL Server that manages automated jobs and schedules tasks.
1. Launch SSMS and connect it to your server, In the object explorer panel of SSMS's SQL Server Agent node, right-click Jobs and choose New Job from the context menu.
2. Enter the name and description in the pop-up window.
3. Select Steps> New to configure the job. Make sure the Database is set to "master" and that the Type is "Transact-SQL script (T-SQL)". Create a full backup before.
To make a differential backup:
GO
BACKUP DATABASE [Example]
TO DISK = 'D:SQL Server example.bak'
WITH CHECKSUM;
BACKUP DATABASE [Example]
TO DISK = 'D:SQL Serverexample.bak'
WITH DIFFERENTIAL;
WITH CHECKSUM;
GO
To create a transaction log backup:
BACKUP LOG [Example]
TO DISK = 'D:SQL Serverexample.log';
GO
Click OK to add a step and tap OK again.
4. Under the Schedule tab, click New to create a scheduled job, configure the scheduled type and frequency and click OK.
5. Continue to set up alerts and notifications if you need them. Right-click the job and click Start Job at Step… to see if it works. Select View History from the right-click menu to look for any mistakes if it doesn’t work properly.
All of the solutions mentioned above can backup one or more databases, and they are built in the SQL Server, but they are not very clear-cut and not all versions could enable these features.
You might as well try automatic database backup software like Vinchin Backup & Recovery, which performs effortless and intuitive SQL backup in 3 steps and has multiple backup strategies like encrypted transmission, retention policy, etc. With the solution, you can deliver hot SQL backup without any effect on database operation and au-to check the data consistency while backing up.
The software supports Windows Server 2008, 2012, 2016, and 2019, and to use it, you need to Download Backup Plugin on the login screen and install it.
1. Select the SQL Server instance (s) you want to backup.
2. Specify the backup destination.
You can choose a backup node from the Target Node dropdown list where the backup data will be processed and stored.
The storage that belongs to the selected backup node can be chosen from the Target Storage selection list.
3. Configure backup strategies.
You can set the job as Backup as Scheduled or Once-off Backup in the Mode dropdown list, and automate the process, here, we choose Backup as Scheduled and set the backup schedules after we decide which backup type should be.
Speed Controller is optional to limit the transmission speed, and Data Storage Policy includes Data Deduplication and Data Compression for saving backup storage space.
Retain the database backups according to the Number of Restore Points or Number of Days.
Enable Encrypted Transmission for data security and transfer via LAN by default.
Advanced Strategy including Check Database Integrity, SQL Server Compression and Page Checksum.
Review and submit the job after you finish the setup and the job will run as scheduled.
Vinchin Backup & Recovery not only supports SQL Server backup and recovery, but also supports the physical and virtual machines protection of MySQL, Oracle, and PostgreSQL. And you can administer all database backups in one central management console.
Want to test the incredible and easy database backup and recovery? You can download the 60-day free trial now to explore.
Share on:
