[Full Guide] Backup and Restore SQL Database with Command Line


Table of Contents
  1. Backup and restore SQL Server database with command line
  2. How to backup SQL database via command line
    1. How to backup all databases in a SQL Server instance
    2. How to automate the SQL Server database backup
  3. How to restore SQL database via command line
  4. Alternative: Easier backup solution for SQL Server (Express) database
    1. Easy steps to take backup of SQL database
    2. How to restore SQL Server database from backup:
  5. Conclusion

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.

mssqlserver

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.

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:

sqlcmd -E -S servername -Q "BACKUP DATABASE database name TO DISK ='filepath'"

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:

sqlcmd -E -S .\MSSQLSERVER_01 -Q "BACKUP DATABASE example TO DISK ='D:\SQL Server\example.bak'"

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.

create stored procedure

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:

sqlcmd -S servername -E -Q "EXEC sp_BackupDatabases @backupLocation='filepath', @backupType='F'"

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:

sqlcmd -S .\MSSQLSERVER_01 -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQL Server\', @backupType='F'"

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.

task scheduler

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.

task scheduler

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:

sqlcmd -E -S servername -Q "RESTORE DATABASE databasename FROM DISK='filepath'"

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."

restore-terminating-abnormally

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:

sqlcmd -E -S .\MSSQLSERVER_01 -Q "RESTORE DATABASE example FROM DISK='D:\SQL Server\example.bak' WITH REPLACE"

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:

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:

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


Is this information helpful?     

What can we do to improve this information? (Optional)
Refresh Please enter the verification code!