[Full Guide] Backup and Restore SQL Database with Command Line
Backup and restore SQL Server database with command line
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.
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 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.
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.
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.
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.
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.
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:
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.
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 an intuitive GUI.
Qiling 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.
Easy steps to take backup of SQL database
Execute the task. Once it's finished, you will find it in the Backup 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:
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 - Qiling 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
Related Articles
- Schedule Database Backup in SQL Server 2012/2014/2016/2019
You will learn how to schedule database backup in SQL server 2012/2014/2016 using SQL Server Agent jobs and SQL Serve Maintenance Plans. Also, an easier way is included. - Best Server Centralized Backup Strategy for Multiple Windows Servers
Do you need a centralized server backup strategy to protect multiple Windows servers or PCs on network? Here's a simple solution for home or business use. - How to Use PowerShell Script to Backup Multiple SQL Databases
Have multiple SQL databases that need to be backed up? Go through this article to learn two effective backup solutions – PowerShell script and Qiling Centralized Disk Master.