SQL Server database restore with sqlcmd

Since SQL Server 2005 version, there is an excellent tool to manage SQL Server instances, that tool is “SQL Server Management Studio” (SSMS)
It is the regular tool to deal with SQL Server instances, but it is not the only way to do it.
In some cases you need to use “sqlcmd”.
Sqlcmd is a command line tool that helps us to interact with a SQL Server engine, it is useful where SSMS is not an option.
With SQL Server 2017 and Linux support, it will probably become a more popular tool.
In this post we will see how to restore a database using sqlcmd.
To be clear, this procedure will work on any SQL Server version over Windows or Linux.

Get ready for the restore

There is a lot we could discuss about the tasks we should do before a restore is performed, but we will keep it simple for this post.
We need to know the following data before we can perform the restore:
hostname/instance: If you are using a “default instance” and you are connected to the database server, “localhost” is probably fine here.
sql_user: Database user to connect to the SQL Server instance, usually a sysdba role user.
database_name: Name of the database you want to restore.
/path/to/backup/database_backup.BAK: Name of the file (device) of the backup and the complete path.

Restore the database

With the data you have, modify this command and execute it in a system console.

sqlcmd -S hostname/instance -U sql_user -Q "RESTORE DATABASE database_name FROM DISK='/path/to/backup/database_backup.BAK'"

After executing, it will ask for the password, just type it and there you go, you have your restore done.

Leave a Reply

Your email address will not be published. Required fields are marked *