How to Restore SQL Server Database from Command Line

Introduction:

In this article, we will study how we can restore our SQL Server databases using the command line. For this, we need to install MS SQL Server and after this, we need sqlcmd. We don’t need anything else for restoring the database, because we know here, we can use the command line. Command-line is a built-in program in windows. On the other hand, we will also show what to do if the database backup is corrupt and how to recover it. The software Stellar Repair for MS SQL Technician also helps us to restore the database from backup files and from corrupt .mdf files. In the Stellar Technician, we recover and restore MS SQL database with the help of some clicks.

Requirements:

• MS SQL Server.
• SQL Server Management Studio.
• Stellar Repair for MS SQL Technician.

Let’s explore the solutions to restore the SQL database using the command line:

Using Command-line (without script)

The first thing is we will run Command-Line from pressing a shortcut key window (windows button) +R.

Here we see a window look like below.

Write cmd in the edit field and press the “OK” button. You will see a window

Here we will write a command:

sqlcmd -q "BACKUP DATABASE testdb TO DISK = 'c:\sql\test.bak'"

This command will invoke the sqlcmd and backup the database named testdb in the test.bak file.

For more information about the different arguments of the sqlcmd command line refer to this link: sqlcmd Utility

By default, it is using Trusted authentication (Windows Authentication). Optionally you can use the -E like this:

sqlcmd -q "BACKUP DATABASE testdb TO DISK = 'c:\sql\test.bak'" -E

In order to recover the database, you can use the following command:

sqlcmd -q " sqlcmd -q "RESTORE DATABASE TESTDB  FROM DISK=N'c:\sql\test.bak'" 

Using the command line with scripts

If you do not like the command line, but you need to use it, I recommend to create the Restore or backup T-SQL Sentences in a .sql file:

Restore.sql
USE Master
GO
RESTORE DATABASE TESTDB  FROM DISK=N'c:\sql\test.bak

You can always generate the T-SQL script using the SSMS:

And then you can use the sqlcmd to invoke the script to recover the database:

Sqlcmd -i “c:\sql\recover.sql” -E -o “c:\sql\result.txt”

The command is invoking the recover.sql that contains the T-SQL sentences to restore and the sql\results.txt stores the errors or successful output messages.

Stellar Repair for MS SQL Technician – Alternative Solution

If for some reason, the command line fails due to backup corruption, it is possible to restore the backup file.

Stellar Repair for MS SQL Technician helps to recover restore from corrupted backup and .mdf files. Here I show you how you can use this software and it will recover your databases. First, we need to install the software on our machine.

After the successful installation of the software run the Stellar Technician and you will see a window look like below.

In this window here we see three options.

1. Repair MS SQL Database.
2. Recover MS SQL Password.
3. Extract from MS SQL Backup

Select option #1 and you will see a new window.

In the last window, you saw a message from software. This message tells us if the MS SQL Server is running then stop the MS SQL Server or close the SQL Management Studio. Otherwise, if MS SQL Server is already closed then copy the database and past it to a different location. Press the “OK” button.

Now, you need to select the corrupt database

In this section, we will press the “Browse” button. Here we will see a window in which we select us .mdf file.

After selecting the file, we will press the “OPEN” button. We will transfer to the previous window.

In the address bar, we see a .mdf file address. Now press the “Repair” button.

It will take some seconds if the database size is large maybe it will take some minutes. In the previous window which I showed, we see our selected MS SQL Database is repaired successfully. Press the “OK” button in the instruction dialog window.

Now press the” Save” button which I highlighted in the red section. We will see a new window look like below.

Here we see a functionality of the Stellar Technician that allows us to save our recovered or repaired database in multiple extensions. Stellar Technician provides us four types of extensions.

1. MDF
2. CSV
3. HTML
4. XLS

In the section of Saving Option, we will save our database in the current file and new file.

In the Server Name/Instance Name Select the server name and, in the Authentication, section selects the authentication type and save your database. After selecting those options, the software allows you to save the file as Default SQL or you can select the “New” checkbox. It will allow you to save your file as a new file. Now the “Browse” button is visible if the “New” section is selected. When you press the browse button you will see a window look like below.

In this window, you can select the location where you want to save your repaired database. Press the “OK” button. Your location is selected press the “SAVE” button. Here you can see a progress bar is running in the below of the window. It will take a while.

After successfully save the file you will see an instruction window that tells you your SQL Database is successfully saved.

Conclusion

In this section, we studied how we can repair our database using the command line. In this process, we didn’t need any of this software. On another hand, if our files are not recovered because it is corrupt, You can use the software called Stellar Repair for MS SQL Technician. This software is very easy to use and provides us with more functionalities.

Related Post