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.
Although users can back up SQL with SQL Server Management Studio (SSMS), you still want to backup your SQL server database using command line to achieve some more intelligent operations, 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 and restore SQL database with command line, including a guide to fully backing up all databases and creating automated backup tasks for SQL.
In addition, we will also introduce an easier way to backup all remote SQL databases with flexibility.
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 if you are using a higher version, click the download link below to download it.
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.
• database name - the name of the database you want to backup
• TO DISK = 'filepath' - it is the path where you want to store the created backup files
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.
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.
Step 1. Please navigate to Databases > System Databases > master > Programmability > Stored Procedures, right-click it and choose New Stored Procedure.
Step 2. 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.
Step 3. 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.
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.
After creating a store procedure, follow the steps to auto backup stored procedure SQL server using script.
Step 1. Open the text editor and enter the backupdatabases command mentioned above.
Step 2. Save the file as .bat extension.
Step 3. 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 you created.
It’s equally simple to restore SQL database from a backup using command line. The command is:
How to solve the "The tail of the log for the database has not been backed up" error?
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.
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 an intuitive GUI.
AOMEI Cyber Backup is a reliable and popular 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).
And it comes with various features and advantages to meet different needs.
• Centralized and remote backup: Centrally backup all or selected SQL databases. • Easy-to-use: Provides an intuitive console. Just simply follow the software instructions, anyone can easily perform MS SQL backup and restore without training. • Fast backup and instant recovery: Gives a fast speed to complete everything to save your time and guarantee data security. • Automatic SQL backup: Easily customize a backup task for MS SQL to perform backup tasks daily, weekly, and monthly. It supports full backup, incremental, and differential backup. • Multiple backup locations: Backup SQL server databases to local path, external storage device, network share, NAS drive. And you can archive backup to an Amazon S3 storage.
Now, you can click the download button to install this software, then we will demonstrate to you the steps to backup and restore SQL server database easily.
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
Click Backup Task -> Create New Task, and choose backup type as Microsoft SQL Backup.
Click on Device Name to specify the SQL instances and databases for backup. You can select one or multiple databases as you need.
Select Target to save backups. You can either specify a local path or network path.
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:
Click Backup Task on the left menu bar, locate the task you want to restore, and click
Select a backup version that you want to restore.
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.
After all the settings, click Start Restore to begin the restore progress, and wait patiently for it to complete. You can click on the
When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.
✍Notes:
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.