Restore SQL Server Database from Backup Using Command Line

Can you restore SQL Server database using command line?

The most common ways of restoring SQL databases are using restore GUI or T-SQL statements in SSMS (SQL Server Management Studio). But if you do not want to log into SSMS every single time, command line is also an option to execute MSSQL related tasks quickly.

Using command line to restore SQL database is actually achieved by typing T-SQL statements in Command Prompt, but how? Here I will provide a guick guide and basic SQLCMD commands for database recovery.

• How to enter T-SQL statements in Command Prompt
• Restore SQL Server database from bak file using command line
• Restore SQL databases efficiently without using commands

How to enter T-SQL statements in Command Prompt

The command line utility SQLCMD enables users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server instances in Command Prompt.

SQLCMD is shipped as part of the product in SQL Server 2014 and lower versions. If you are running SQL Server 2016 and above version, you may need to download it from this page.

With it, you can use command line to backup and restore SQL databases. Just type “cmd” in Windows search bar, and run Command Prompt to input T-SQL statements.

*You can also press Win + R key, type "cmd" in the Run window, and tap OK to launch it.

Restore SQL Server database from bak file using command line

In MSSQL, you usually need to restore database from .bak file, which can also be created by command line using this backup statement:

SqlCmd -E -S ServerName -Q "BACKUP DATABASE DatabaseName TO DISK ='Filepath'"

Fill in your own server name, database name and the filepath (include the filename and .bak suffix), the backup will be executed as you need.

*Click on the link to learn more about how to backup SQL database with command line.

With a previously created .bak file, you can use command line to restore SQL database from backup. Here are some frequently used command:

◉ Restore SQL database from a full backup

SqlCmd -E -S ServerName -Q "RESTORE DATABASE DatabaseName FROM DISK ='Filepath'"

◉ Restore SQL database from differential backup

SqlCmd -E -S ServerName -Q "RESTORE DATABASE DatabaseName FROM DISK='Filepath_Full' WITH NORECOVERY"
SqlCmd -E -S ServerName -Q "RESTORE DATABASE DatabaseName FROM DISK='Filepath_Diff' WITH RECOVERY"

◉ Restore SQL Server transaction log backup

SqlCmd -E -S ServerName -Q "RESTORE LOG DatabaseName FROM DISK ='Filepath' WITH NORECOVERY"

Enter the corresponding command in Command Prompt, and press Enter to run it. If you need to execute the same command frequently, you can also type it in a text file, and save with .bat extension. Thus you can simply run it by double-clicking, i.e. use batch file to restore SQL database.

The tail of the log for the database has not been backed up

When using command line to restore a SQL database, you may receive an error prompt “The tail of the log for the database 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.”

To resolve this problem, you just need to follow the prompt, backup the transaction log of the database WITH NORECOVERY before restoring (if you don't want to lose the contens of the log):

SqlCmd -E -S ServerName -Q "RESTORE LOG DatabaseName FROM DISK ='Filepath' WITH NORECOVERY"

Or use WITH REPLACE or WITH STOPAT to just overwrite the contents of the log:

SqlCmd -E -S ServerName -Q "RESTORE DATABASE DatabaseName FROM DISK=''Filepath' WITH REPLACE"

How to restore multiple SQL databases at once

If you only want to restore one or a few databases from backup, just repeat the command with corresponding database names and .bak files.

If you want to restore many databases at once, like all the databases of an instance, it's recommended to use T-SQL statements in SSMS directly. Check details in restore multiple SQL databases.

Restore SQL databases efficiently without using commands

Using command line to restore SQL database is a convenient way, but only if you have some knowledge of T-SQL statements. Here I will provide an easier alternative - AOMEI Centralized Backupper Database. It is suitable for people who have the following needs:

• Perform backup and restore through simple clicks instead of error-prone commands
• Automatically execute the same backup task at regular intervals
• Backup and restore multiple SQL databases at once
• Manage SQL databases of all client computers in the LAN
• Protect a wide range of data including SQL databases, operating system, files, partitions, disks

If you want to try it out, here’s a 30-day free trial:

It is a centralized backup software, which can manage not only your own computer, but all the Windows desktops, laptops, servers, virtual machines within LAN. To set it properly, please follow this process:

• Download and install Centralized Backupper Database on your computer as a central management console.
• Launch the software, Install client program to the detected computers on the network remotely or manually.
• Select the client computers you want to manage, Request Control over them and wait for their confirmations.

Then you can start to backup or restore SQL database on all the controlled computer as you wish.

Steps to backup SQL database

1. Select SQL Server Backup in Home tab or Tasks > New Task.

2. The setup window is divided into 3 main steps.

Step 1: Click + Add Computers to detect all the controlled computers with SQL instances, and select the one you want to back up.

Step 2: Click + Add to select the source data. You can select multiple databases or even multiple instances at once.

Step 3: Click Add Storage to enter the network path of a share of NAS as backup target.

3. After the basic setup, you can add a Schedule to auto execute the task once/daily/weekly/monthly, or manage the backup encryption, compression, notification, etc. in Settings. Then, hit Start Backup to create and execute the task.

Steps to restore SQL database

1. The created backup task will be displayed in Tasks tab. You can directly restore databases from it by clicking Advanced > Restore.

2. Select the source computer and then the source database. You can choose exactly which backup to restore from (if you performed the task multiple times), whether to restore all databases in the backup or individual databases.

3. Then, choose whether to Restore to original location or Restore to a new location. By the second option, you can even restore database to another SQL Server on another client computer.

4. Click Start Restore to perform the restoration and wait for it to complete.

Conclusion

Performing SQL Server database restore with command line can save you some effort, but a small error when inputting T-SQL statements may cause the operation to fail. If you prefer a more straightforward way, AOMEI Centralized Backupper Database Edition could be a choice worth trying.

Its concise GUI greatly simplifies the restore operations, especially when you need to restore multiple databases at once. In addition, it not only supports SQL databases, but also manages backups and restores of files, systems, disks and partitions of all computers in the LAN. So you can simply use it as a client-server backup solution.