Guide | Backup and Restore SQL Database with Command Line

Backup and restore SQL Server database with command line

Although SQL Server Management Studio (SSMS) has basic backup features built in, you may want to achieve some more intelligent operations with command line, especially in free Express editions that lack advanced features such as Maintenance Plans and SQL Server Agent.

In the following article, I will introduce how to backup SQL database with command line, and how to restore from the created backup.

• How to backup SQL database via command line?
• How to restore SQL database from .bak file via command line?
• Alternative: Easier backup solution for SQL Server (Express) database

How to backup SQL database via command line?

First, you need SQLCMD utility to enter Transact-SQL statements, system procedures, and script files in Command Prompt. It’s shipped with SQL Server in 2014 and lower versions. But in higher versions, you need to download it separately.

Download SQLCMD (x64)

Download SQLCMD (x32)

Then you can open Command Prompt to backup SQL (Express) database with command line. Here's the basic SQL Server backup command line:

In this command, -E means to use a trusted connection. If you want to use a username and password instead, you can replace it with -U -P switches. In case you need to use other switches, please click here to learn more SQLCMD syntax.

This is a case where I use SQLCMD to backup database “example” in a named instance:

Note we normally reference the default instance by its server name, and reference a named instance by its server name\instance name. You could also get the accurate name by right-clicking an instance and choosing Properties from the menu.

How to backup all databases in a SQL Server instance?

If what you want to back up is not one or two specific databases, but all databases in an instance, it can be a pain to specify them one by one. Therefore, you can create a stored procedure in your master database, so as to simplify the backup command you need to use each time.

First, please navigate to Databases > System Databases > master > Programmability > Stored Procedures, right-click it and choose New Stored Procedure.

Microsoft has provided a script for SQL Express backup, you can copy all the content into the SQLQuery window, and hit Execute button to create a stored procedure named sp_BackupDatabases. This stored procedure specifies the databases to backup, you can also modify the exclusion criteria to match your requirements.

For instance, if you are using non-Express SQL editions, and want to backup all the databases in an instance except for system databases, you can use “DELETE @DBs where DBNAME IN ('master','model','msdb','tempdb')” under each backup type.

Once the stored procedure is executed successfully, use the following command line to back up these databases:

As for backupType,  “F” refers to full backup, “D” refers to differential backup and “L” refers to Transaction log backup.

If you want to use SQLLogin instead of Windows Authentication, then add -U -P switches and remove -E.

Here my example is:

When the backup is complete, a .bak file will be generated for each database.

How to automate the SQL Server database backup?

If you need to back up the same database(s) frequently, setting up the backup in Command Prompt each time wouldn’t be the easiest way. Instead, you can automate the procedure by Windows Task Scheduler.

First of all, open text editor, enter the commands you use for database backup in it, and save it as a batch file (with .bat extension). Then, go to Control Pane > Administrative Tools > Task Scheduler > Create Basic Task, and follow the wizard to create a scheduled task.

More specifically, you need to select a Tigger according to your needs, select Action as “Start a program” and locate the batch file your created. For details, please refer to auto backup SQL Server database.

How to restore SQL database via command line?

It’s equally simple to restore SQL database from a backup using command line. The command is:

But in practical use, you may receive an error saying “The tail of the log for the database "databasename" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.”

This is because the backup file that you are trying to restore is older than the database you are want to restore. And the solution is just as stated in the prompt message.

For example, I want to restore the older backup and simply overwrite the existing database, then I can use the “WITH REPLACE” switch, which equals to the “Overwrite the existing database” option in SSMS. And the command will be like:

Alternative: Easier backup solution for SQL Server (Express) database

Whether you are looking for convenience, or trying to schedule backup in Express editions, backup sql database using command line is a considerable solution. But if you find the script difficult to master, especially when you need to backup & restore multiple databases or instances, there’s also an easy alternative with intuitive GUI.

AOMEI Centralized Backupper Database is a reliable backup management solution for all desktop PCs, laptops, workstations and servers within LAN. It contains a database backup function that supports SQL Server 2005 - 2019, and enables you to centrally backup & restore databases on any client computer.

To use it, you need to download and install AOMEI Centralized Backupper Database Edition on the central machine (here's a 30-day free trial):

Then install Centralized Backupper Database on client side, and request control over those client computers for further operation:

How to centrally backup SQL database:

1. Launch AOMEI Centralized Backupper Database, navigate to Tasks > New Task > SQL Server Backup.

2. In Step 1, click + Add Computer to detect all the controlled computers with SQL Server database, then select the one you want to backup.

3. In Step 2, click + Add to detect all the SQL Server instances on the selected client machine. In the pop-up window, you can select multiple databases or even multiple instances to backup.

4. In Step 3, hit Add Storage to enter the share or NAS path and the corresponding authentication. Then you can specify a folder as the backup target.

5. If you want to automate the backup, please click on Schedule to configure the schedule settings, full backup and differential backup are also supported in Advanced tab. After the setup, click on Start Backup to execute the task.

How to restore SQL Server database from backup:

1. Find the created backup task in Task tab, click Advanced > Restore on its top-right corner.

2. Select the computer, and then the databases you want to restore, then click Next to select the destination.

3. In addition to the original location, you can also restore to new location. Make your choice and click Start Restore.

Conclusion

This article provides a quick guide to backup SQL database with command line, automate the backup, and restore from the backups. If you find it complicated, there’s also an alternative software - AOMEI Centralized Backupper Database Edition.

It simplifies the backup and restore procedure, especially when you want to auto backup SQL Server database or restore SQL Server database to another server. And not only the local server, you can easily manage backup tasks for all the machines within LAN.