Backup database in SQL Server Management Studio
As you probably know, there are multiple backup methods in Microsoft SSMS, each with its own advantages and disadvantages. So before you start, I’d like to briefly introduce and compare them for your reference:
◉ Backup GUI: This feature is easy to operate without using scripts, but only one database can be backed up at a time, and the operation cannot be automated.
◉ T-SQL script: You need to know basic T-SQL commands beforehand, and any small mistake may lead to operation failure. But with effective scripts, you can perform some more advanced operations easily.
◉ Maintenance Plans: It allows you to backup multiple databases at once, and add a schedule to automate the task. However, it is only available for non-Express editions and is relatively cumbersome to operate.
◉ SQL Server Agent: It can also automate backups with high flexibility, but you need to fill in the backup script yourself as the core step. Also, it is not applicable to SQL Express editions either.
You can pick the way you like, or use another SQL backup software that can schedule backup multiple databases simply and intuitively.
Method 1. Create a SQL backup task with GUI
In Microsoft SQL Server Management Studio, the easiest way to backup database is using the backup GUI.
1. Launch SSMS and connect to your instance. Right-click the database you want to back up in Object Explorer, choose Tasks > Back Up.
2. Choose the backup type (full/differential/transaction log), and Add a path as you need.
It's recommended specify a easily accessible path so that the backup file can be easily found when you need to perform recovery. Then, enter the file name as you like and remember to add the .bak suffix.
3. Click OK to execute the backup. It will prompt you a message once the operation is complete. Then you can find the backup file in the target folder.
Tip: After backup, you can also restore SQL database from bak file via SSMS. Click to learn more details.
Method 2. Backup database with T-SQL commands
The basic T-SQL backup statements is BACKUP DATABASE TO DISK. You can use it flexibly as you need.
Create a full backup:
BACKUP DATABASE [database] TO DISK = 'filepath\fileaname.bak' WITH COMPRESSION, INIT, STATS = 5;
Create a differential backup:
BACKUP DATABASE [database] TO DISK = 'filepath\filename.bak' WITH DIFFERENTIAL
Create a transaction log backup:
BACKUP LOG [database] TO DISK ='filepath\filename.trn' WITH NORECOVERY
Method 3. Schedule backup with Maintenance Plans
This feature gives you options to create a plan manually, or complete the operation following a wizard. Here I will mainly introduce the first one.
1. Make sure the SQL Server Agent is enabled, otherwise please Start it first.
2. Navigate to Management > Maintenance Plans. Right-click it and choose New Maintenance Plan. Give it a name to proceed.
3. Click Subplan Schedule (calendar icon) to set up the backup schedule. Keep the schedule type as the default Recurring, and configure the frequency as you like. Click OK to confirm it.
4. Hit Toolbox and drag the Back Up Database Task into the blank area on the right side.
5. Double-click the task to choose the backup type, and the databases to backup. Then specify a target folder.
6. After saving the plan, you can find it under SQL Server Agent > Jobs. It will be executed automatically according to your schedule. You can also right-click it and choose Start Job at Step to test it immediately.
Tip: The procedure of creating a backup task with Maintenance Plan Wizard is largely the same. But it will walk you through all the steps, so it's more suitable for beginners.
Method 4. Create an auto backup job with SQL Server Agent
This method essentially uses T-SQL commands to perform database backup, but you can add a schedule to automate the task.
1. Right-click SQL Server Agent and choose New > Job.
2. Enter a name and description for the job. Then move to Steps tab.
3. Click New to add a step name and any T-SQL backup commands as you need. Here’s an example：
DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'filepath' + @strPath + '.bak'
BACKUP DATABASE [database] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
4. Move to Schedules tab and click New to set up the schedule type and frequency. Click OK to finish the setup.
5. Then you can find the created backup job under SQL Server Agent > Jobs, and choose Start job at Step to test it.
Easier way to backup multiple SQL databases with schedule
If you find the above 4 backup methods in SQL Server Management Studio complicated or not effective enough, you can also try AOMEI Centralized Backupper Database to backup MSSQL.
It supports full or differential SQL backup, and allows you to select multiple databases or even all databases at once. Also, you can set up a schedule to automate the operation easily.
Download the 30-day free trial to proceed:
Install Centralized Backupper Database on the central machine, and client packages on all network connectetd PCs, workstations and servers that you want to protect. Then you can Request Control over these client computers and manage their backup and restore tasks centrally.
Backup SQL databases with centralized backup software:
1. Navigate to Tasks > New Task > SQL Server Backup.
2. Click + Add Computers to detect the client computers with SQL database, select any of them from the result list.
3. Click + Add to detect instances on the specified computer. You can select multiple databases or the entire instance at once.
4. Click on Step 3 > Add Storage to specify a target network share or NAS. Then you can click Start Backup to save and execute the task.
☛ To automate the backup, just click Schedule to configure the frequency and backup type (full or differential).
☛ To restore from the backup, click Advanced at the top right corner of the task, and choose Restore to proceed. It allows you to restore one or multiple databases to any earlier date.
The purpose of this article is to introduce the frequently used methods of backing up databases via SQL Server Management Studio and a more convenient alternative - AOMEI Centralized Backupper Database Edition.
It not only provides a concise graphic interface, but also simplifies the process of selecting databases and creating schedules. As a centralized managment tool, it even enables you to restore SQL databases to another server directly.