AOMEI Cyber Backup
Guide | Backup and Restore SQL Database with Command Line
In this article, I will introduce how to backup SQL Server database using command line, and how to restore it. This method also applies to Express editions.
By Delia / Updated on June 13, 2023
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
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.
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:
This should solve your SQL database restore issue.
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, especially when you need to backup & restore multiple databases or instances, there’s also an easy alternative with intuitive GUI.
AOMEI Cyber Backup is a reliable enterprise backup solution for SQL databases and virtual machines within LAN. It supports SQL Server 2005 - 2022, and works on all Windows OS (Windows 7 and Windows Server 2008 R2 onwards).
To use it, you need to download and install AOMEI Cyber Backup and install it on the server machine. Here's a 30-day free trial:
After installation, launch AOMEI Cyber Backup and go to Source Device -> Microsoft SQL -> Add Microsoft SQL.
Choose Download proxy program (or Copy link) and install the proxy on the device with SQL Server installed. Then, click Already installed proxy and select the device to Confirm it.
Next, click 
-> Authentication to validate the database instance. Then you can start the SQL Server Backup.
How to centrally backup SQL database:
1. Click Backup Task -> Create New Task, and choose backup type as Microsoft SQL Backup.
2. Click on Device Name to specify the SQL instances and databases for backup. You can select one or multiple databases as you need.
3. Select Target to save backups. You can either specify a local path or network path.
4. Before Start Backup, you can also set up a Schedule to run the SQL database backup daily, weekly or monthly, and set the backup method as Full, Incremental or Differential Backup.
Execute the task. Once it's finished, you will find it in the Backup Task tab.
✍More useful features:
- Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
- Email Notification enables you to receive email notifications when the task is abnormal or successful.
How to restore SQL Server database from backup:
1. Click Backup Task on the left menu bar, locate the task you want to restore, and click -> Restore.
2. Select a backup version that you want to restore.
3. Select the target location that you want to restore to. Restore to original location is selected by default.
If you want to Restore to new location, you need to select target and specify the name of the new database. You can also modify the storage location.
4. After all the settings, click Start Restore to begin the restore progress, and wait patiently for it to complete. You can click on the button to see restore details or cancel it.
When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.
✍Notes:
- "Restore to original location" must confirm the original location exist. Or else, you can only select "Restore to new location".
- "Restore to original location" will overwrite or delete the original database data, if the original database has important data, it is recommended to choose "Restore to new location".
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 Cyber Backup
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.
