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 Toolkit for MS SQL also helps us to restore the database from backup files and from corrupt .mdf files. In the Stellar Toolkit, we recover and restore the database with the help of some clicks.
- MS SQL Server.
- SQL Server Management Studio.
- Stellar Toolkit for MS SQL.
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 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 Toolkit for MS SQL – Alternative Solution
If for some reason, the command line fails due to backup corruption, it is possible to restore the backup file.
Stellar Toolkit for MS SQL 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 Toolkit and you will see a window look like below.
In this window here we see three options.
- Repair MS SQL Database.
- Recover MS SQL Password.
- 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 Toolkit that allows us to save our recovered or repaired database in multiple extensions. Stellar Toolkit provides us four types of extensions.
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.
In this section, we studied how we can repair our database using 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 Toolkit for MS SQL. This software is very easy to use and provides us more functionalities.