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 consider using AOMEI Centralized Backupper Database to auto backup MSSQL databases. If 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 Centralized Backupper Database on your computer. Here’s a 30-day free trial:

Then, install Agent on all the client computers you want to backup. You can launch Centralized Backupper Database, switch go Computers tab, and choose to Install client program manually or remotely.

*The client programs installed remotely will be auto controlled by the central machine, but if you choose the manul way, you still need to Request Control over them.

After that, you can go to Tasks tab and create a New Task as you need. The options include file, system, partition, disk and SQL Server. Here choose SQL Server Backup to get started.

As you can see, the setup window is divided into 3 steps, you can simply follow it to complete the backup:

Step 1: Click + Add Computers to detect controlled computers with SQL instances, and select one to backup.

Step 2: Click + Add to specify the source data based on the selected computer. You can select multiple databases or even instances at once.

Step 3: Click on the checkbox, and hit Add Storage in the pop-up window. Then you can input the path of a network share or NAS, and select a folder in it as destination.

You can see two more options available below:

Settings: Here you can encrypt the backup with password, enable email notification, or choose compression level.

Schedule: It gives you options to automate database backup on a daily/weekly/monthly basis. In Advanced tab, you can also choose to perform full backup or differential backup.

After all settings have been completed, it’s time to Start Backup. You can either create a task and execute it immediately, or just create the task and perform the backup later.

All the created tasks can be managed under Tasks tab. You can click Backup to run it manually, or click Advanced to edit, restore, or delete it.

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 Centralized Backupper Database Edition 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 endpoint backup software, it also allows you to create and manage client computer backups from a central machine.