How to Perform SQL Server Auto Backup Every Day? (2 Ways)

Need SQL Server auto backup

Microsoft SQL Server (MSSQL) is one of the most popular database servers. It can help enterprises create and maintain databases. You can also use it to analyze the data through SQL Server Analysis Services (SSAS) and generate reports through SQL Server Reporting Services (SSRS).

After creating databases with SQL Server, it is necessary to make an auto backup for the server, such as SQL Server auto backup every day. That’s because your data in the databases is at risk due to human errors, system errors, media failure and so on, and backup is taken as the most primary disaster recovery solution. Besides, business data changes frequently, so you need auto backup to save energy and administration cost. Then, how to perform SQL Server database automatic daily backup? Just keep reading.

Run automatic SQL database backup using SSMS

SQL Server Management Studio (SSMS) is a software application used for configuring, managing, and administering all components within Microsoft SQL Server. First launched with SQL Server 2005, it includes both script editors and graphical tools. Learn how to do scheduled automatic SQL database backup using SSMS as follows:

Notes:
◑ SSMS is available only as a 32-bit application for Windows. If you need a tool that runs on operating systems other than Windows, Azure Data Studio is recommended.
◑ If you are using SQL Server Express editions, the following method is inapplicable. You should try either SQL Server Management Studio Express or a Transact-SQL script to do database backup.

1. Download, install and run SQL Server Management Studio on your computer. Then, complete all fields in the Connect to Server window and click Connect.

2. Go to the Object Explorer window and make sure that your SQL Server Agent is running.

Tip: If it is shown “Agent XPs disabled”, press Win + R keys and type services.msc in the Run window. Then, start SQL Server Agent in the Services window.

3. Expand Management. Then find the Maintenance Plan folder, right-click on it and select New Maintenance Plan.

4. Enter a name for the new maintenance plan and click OK. In the Maintenance Plan Design window, press on the calendar icon at the top.

5. In the New Job Schedule window, configure SQL Server auto backup every day as follows and click OK to save your settings.

• Schedule type: Recurring
• Frequency: Occurs Daily, Recurs every 1 day
• Daily frequency: Occurs once or every “n” hour(s)
• Duration: Specify start date and end date based on your need
• Summary: Review your settings

6. Click Toolbox in the left corner. Then, expand Maintenance Plan Tasks, double-click Back Up Database Task and the small pop-up window successively.

7. In the Back Up Database Task window, do the following settings and then click OK.

• Under the General tab: choose “Full” backup type, select one or more database to be backed up
• Under the Destination tab: you can keep the default location for the backup file (C:\Program Files\...\mssql\backup), specify another destination path, or your logical backup device in SQL Server.
• Under the Options tab: set backup compression, expiration time, encryption, etc.

8. Go to Object Explorer > SQL Server Agent > Jobs. From there, you’ll see a job created automatically according to the Maintenance Plan you just created.

Tip: If the job is not created automatically, you can go to the Maintenance Plan Design window and press Ctrl + S to save the maintenance plan first.

9. Right-click on the newly-created job and select Start job at Step… to test the maintenance plan you’ve created. After the job is executed successfully, you can go to the backup location to check the backup file.

Perform SQL Server daily backup via Centralized Backupper Database

AOMEI Centralized Backupper Database Edition is also easy-to-use SQL auto backup software that is worth a try. You can benefit a lot from its following features:

◉ It offers System/Disk/Partition/File Backup to help you create centralized backups for all clients within LAN. It also offers SQL Server Backup which is used to backup multiple instances on one PC/server.
◉ It enables you to create auto backup tasks using the Schedule feature. There are four schedule modes available: Once, Daily, Weekly, and Monthly.
◉ It supports backing up databases on SQL Server 2019/2017/2016/2014/2012/2008(R2)/2005.
◉ It can be installed on both Windows Server (Windows Server 2019/2016/2012/2011/2008/2003, etc) and Windows PC (Windows 10/8.1/8/7/Vista/XP).

Before you create backup tasks with AOMEI Centralized Backupper Database, you should:

1. Install Centralized Backupper Database on one of the computers within LAN which will be used as the central management computer.

2. Install client program

Launch Centralized Backupper Database and go to Computers > Uncontrolled Computers > Install client program. You’ll see two options as follows:

>> Manual Client Installation: click “Next” and you’ll get the client program setup file. Copy the setup file to the computer with SQL Server and run it as administrator to install Centralized Backupper Database Agent.

>> Remote Client Installation: click “Next” and you’ll have all available computers within LAN. Choose the computer with SQL Server, input username and password of it and click “Install”.

3. Request control

3.1. If you have installed client program manually, client computers will be listed under Computers > Uncontrolled Computers. Tick the computer with SQL Server and click Request Control to obtain control permissions. There are two methods provided:

>> By entering accounts: you should enter username and password of the client computer and click “OK”.

>> By sending a message: the program will send a message to the client computer. Click “Agree” on the client computer to authorize access.

3.2. If you have installed client program remotely, client computers will be listed under Computers > Controlled Computers. There’s no need for you to request control in this situation.

Now you can perform SQL Server auto backup every day via Centralized Backupper Backupper with the following steps:

1. Go to Tasks tab, click New Task and select SQL Server Backup.

2. In the pop-up window, edit task name based on your need. Then, click Add Computers in the “Step 1” column and select the controlled computer with SQL Server databases.

 3. Click the “Step 2” column and then click Add. The program will detect all SQL Server instances on the computer. Select the instance(s) and database(s) you want to backup and click OK.

4. Click the “Step 3” column and then click Add Storage. Type the network share or NAS path to add the destination location to store the backup file.

5. Click Schedule in the lower-left corner and choose the computer you want to create a schedule for. In the Schedule Settings window, choose Daily from the drop-down list and finish the corresponding settings. You can also choose to run full/differential backup by clicking Advanced.

6. Confirm the operation and click Start Backup. You can choose “Only create the task” or “Create and execute tasks” here.

Verdict

There are two effective methods offered in this page to help you perform SQL Server auto backup every day. If you only want backup solutions for SQL Server, then SQL Server Management Studio can meet your needs. If you want a backup tool that can help with not only SQL Server backup but also regular backups for all computers within your company, then AOMEI Centralized Backupper Database Edition is what you need.

AOMEI Centralized Backupper Database allows you to create centralized backup or sync tasks for multiple computers within LAN. Why not give it a shot?