Backup SQL Server with BACKUP DATABASE TO DISK Command

Use T-SQL statement to backup database to disk

T-SQL is the most command way for SQL Server backup, and BACKUP DATABASE TO DISK is the basic statement to create a full backup, differential backup and so on.

Compared to other backup methods, it offers the flexibility to customize the source data, destination and backup types. Once you understand its basic syntax, you can simply implement the backup operations as you wish.

How to use BACKUP DATABASE TO DISK command in SQL Server

Normally you can run BACKUP DATABASE TO DISK commands in SSMS. Just connect to your SQL instance and create a "New Query", then input the backup statement as you need and "Execute" it.

Here are some frequently used SQL Server backup database to disk commands you can refer to. Please note that the “Filepath” below should include the backup file name and corresponding suffix.

Create a full database backup to disk:

Create a differential backup of database:

Create a transaction log backup to disk:

Backup database into multiple .bak files:

Backup database and display backup progress:

*It will create a full backup and displays the backup progress every 10%. You can also specify the progress status by your own, like using WITH STATS = 5%.

Create a full backup and mirror to other locations:

Perform advanced operation with BACKUP DATABASE script

Above are some basic applications of T-SQL backup command. In fact, you can also achieve some advanced operations in SQL Server with more complex BACKUP DATABASE TO DISK scripts.

For example, if you want to backup a database with date in backup file, you can use the following command:

You can also do this to multiple databases, details can be found in backup SQL database with date in file name.

Sometimes you may want to automate a T-SQL script to backup the same databases with the same settings regularly. To do that, you need to enable SQL Server Agent in SSMS to create a new job, and input the script as its “Step”, then create a schedule to automate it. For detailed steps, you could refer to SQL Server backup script.

Run BACKUP DATABASE TO DISK from command line

If you don’t want to log into SSMS for database backup and restore, you can also execute SQLCMD BACKUP DATABASE TO DISK command from Command Prompt.

Press Win+R to call up the Run window, enter “cmd” in the textbox and press Enter, then you can input the BACKUP DATABASE command in the popping out window.

Please note that the commands used in cmd are slightly different from those used in SSMS. Take the 3 types of backup in SQL Server as an example.

Create a full backup to disk:

Create a differential backup to disk:

Create a transaction log backup to disk:

Tip: If you want to perform a backup task frequently, you can create a text file, enter the backup commands in it, and save it as a batch file. Thus you can double-click it to execute the backup, or even use Windows Task Scheduler to automate it. For more information, please read batch file to backup SQL database.

Backup SQL database without using T-SQL command (easier)

Using BACKUP DATABASE TO DISK command for SQL Server backup has the advantage of simplicity and flexibility, but if you want to do more advanced operations, such as backup all databases, automatic database backup, etc., the operation will be more complicated.

Also, since T-SQL commands are entered manually, there may be small errors that make the operation to fail, and you need to take the time for troubleshooting.

With these considerations, you can try AOMEI Cyber Backup to auto backup MSSQL databases. It provides you a simple interface to complete all the operations with simple clicks. Furthermore, as a centralized backup solution, it enables you to backup and restore all the SQL Server on the network.

To use it, you can download and install AOMEI Cyber Backup on your computer. Here’s a 30-day free trial:

Run AOMEI Cyber Backup on the server machine, click Source Device -> Microsoft SQL -> Add Microsoft SQL.

Download proxy program and install it on the device with SQL Server installed. Then, click Already installed proxy and select the device with SQL installed.

Next, click -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

Steps to backup SQL database to local disk or network share

1. Click Backup Task on the left menu bar, select Backup Task -> Create New Task to open the task creating page. Choose backup type as Microsoft SQL Backup, and set the Task Name as you like.

2. Click on Device Name to specify the SQL instances and databases you need to back up. You can select one or multiple database flexibly.

3. Select Target to save backups. You can specify a local or network path.

4. Before Start Backup, you can also set up a Schedule to run the SQL database backup automatically. Meanwhile, you can select the backup method as Full, Incremental or Differential Backup.

✍Schedule Backup helps you backup your database automatically. The schedule setting is enabled by default. You can edit or disable it on your needs.

✍Backup Cleanup is an advanced feature that can automatically remove history backup versions based on a rule to save your storage space.

✍Email Notification enables you to receive email notifications when the task is abnormal or successful.

A completed task will be listed in the Backup Task tab. Then you can edit or restore from SQL database from it when you need.

Conclusion

In SQL Server, BACKUP DATABASE TO DISK is a practical T-SQL statement to perform different types of backup. But if you want to use a more intuitive way to do such operations, AOMEI Cyber Backup is also a choice worth trying.

With it, you can perform automatic backup with ease, or backup all SQL databases of an instance. More than that, as an enterprise-level backup software, it also allows you to create and manage VMware ESXi VMs backup centrally.