By Delia / Last Updated December 20, 2021

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 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:

BACKUP DATABASE DatabaseName TO DISK ='Filepath'

ssms full backup

Create a differential backup of database:

BACKUP DATABASE DatabaseName TO DISK ='Filepath' WITH DIFFERENTIAL

ssms differential backup

Create a transaction log backup to disk:

BACKUP LOG DatabaseName TO DISK = 'Filepath' WITH NORECOVERY

ssms log backup

Backup database into multiple .bak files:

BACKUP DATABASE DatabaseName TO DISK = 'Filepath_1',
DISK = 'Filepath_2',
DISK = 'Filepath_3'

ssms backup into multiple files

Backup database and display backup progress:

BACKUP DATABASE DatabaseName TO DISK = 'Filepath' WITH STATS

*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%.

ssms backup with progress

Create a full backup and mirror to other locations:

BACKUP DATABASE DatabaseName TO DISK = 'Filepath_1'
MIRROR TO DISK = 'Filepath_2'
WITH FORMAT

ssms mirror backup

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:

DECLARE @FileName varchar(1000)
SELECT @FileName = (SELECT 'Path\Databasename' + convert(varchar(500), GetDate(),112) + '.bak')
BACKUP DATABASE databasename TO [email protected]

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

backup script with date

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.

sql server agent 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:

SqlCmd -E -S Server\Instance -Q "BACKUP DATABASE DatabaseName TO DISK ='Filepath'"

cmd full backup

Create a differential backup to disk:

SqlCmd -E -S Server\Instance -Q "BACKUP DATABASE DatabaseName TO DISK ='Filepath 'WITH DIFFERENTIAL"

cmd differential backup

Create a transaction log backup to disk:

SqlCmd -E -S Server\Instance -Q "BACKUP LOG DatabaseName TO DISK ='Filepath'WITH NORECOVERY"

cmd log backup

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:

Download Free Trial Win PCs & Servers
Secure Download

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.

install client program

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.

sql server backup

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.

add computer

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

select database

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.

select path

You can see two more options available below:

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

settings

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.

schedule

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.

manage tasks advanced

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.