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

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

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.

Bonus tip: Easier way to restore SQL server databases - GUI interface

Command Line is a common way to restore SQL databases. However, it is not convenient and complicated. If you are looking for an easy way to restore data, you can turn to AOMEI Cyber Backup. It is a user-friendly MS SQL server database backup and restore solution. It enables users to backup and restore all databases within a few clicks.

AOMEI Cyber Backup 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

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

Install and run AOMEI Cyber Backup on your machine, click Source Device -> Microsoft SQL -> Add Microsoft SQL.

Download proxy program and install it on the device with SQL Server installed. Then, click Already installed proxy and select the device with SQL installed.

Next, click  -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

🔹How to backup SQL Server databases:

1. Click Backup Task on the left menu bar, select Backup Task -> Create New Task to open the task creating page. Choose backup type as Microsoft SQL Backup, and set the Task Name as you like.

2. Click on Device Name to specify the SQL instances and databases you need to back up. You can select one or multiple database flexibly.

3. Select Target to save backups. You can specify a local or network path.

4. Before Start Backup, you can also set up a Schedule to run the SQL database backup automatically. Meanwhile, you can select the backup method as Full, Incremental or Differential Backup.

✍Useful features:

• Schedule Backup helps you backup your database automatically. The schedule setting is enabled by default. You can edit or disable it on your needs.
• Backup Cleanup is an advanced feature that can automatically remove history backup versions based on a rule to save your storage space.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

🔹How to restore SQL 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. If you restore to original location, you don’t need to select target. Restore to original location is selected by default and you can directly click Start Restore.

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

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 Cyber Backup 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 virtual machines in the LAN. So you can simply use it as a Hyper-V and VMware backup solution.