To know about the steps to restore your SQL databases with the help of command line is crucial especially in the cases of large databases. To perform this task, the foremost task is to install MS SQL Server and then use sqlcmd. Knowing the procedure of how to restore your SQL server database will help if you face data corruption, loss, etc.
You can perform the SQL recovery task easily by using the command line. Right here, we will discuss the steps to perform to Restore SQL Server Database from Command Line. In the latter part, we will also discuss on restoring SQL Server database with the help of an automated tool, such as Stella Repair for MS SQL Technician. Let’s begin.
The importance of DBCC CHECKDB in restore SQL Server Database
Using DBCC CHECKDB is beneficial both before and after database restore operation even if you are doing it with command line. Before you start the restore process, the SQL command will ensure the consistency, integrity, and corruption-free status of the database. In case any issue surfaces, you can address it before opting for the restore. Here is the syntax for the same:
DBCC CHECKDB (‘database name’);
Example: DBCC CHECKDB (‘SQL_TEST_DB’);
There may arise inconsistencies in the databases after the SQL database recovery process. This is prevalent in case of damage backup files or issues encountered during restoration. The use of DBCC CHECKDB command after the database restore confirms the reliability and suitability of the database before you start to reuse it.
DBCC CHECKDB (‘restored database name’);
Example: DBCC CHECKDB (‘SQL_TEST_DB_Restored’);
Restore SQL Server Database with the help of Command Line
You can run the command line by pressing the shortcut key window – ‘windows button + R’.
As the Run window opens up, write cmd in the edit field and then the ‘OK’ button.
Next, you will see a window. Write the command as below:
sqlcmd -q “BACKUP DATABASE ‘database name’ TO DISK = ‘path of the database’
This command will use the trusted Windows Authentication by default and trigger sqlmd to backup the database. Additionally, you may also use the option of -E
sqlcmd -q “BACKUP DATABASE ‘database name’ TO DISK = path of the database’” -E
To recover the database, you must try the following command:
sqlcmd -q ” sqlcmd -q “RESTORE DATABASE ‘database name’ FROM DISK=N’ path of the database’ “
Restore SQL Server Database by using Command line with scripts
If the process to restore SQL server database from command line does not go well, but you still have to use it, you may create a backup or Restore T-SQL query in a .sql file. Generate this query in the query builder of SQL Server Management Studio (SSMS).
In this task, you need to create the Restore or backup T-SQL query in a certain .sql file:
Restore.sql
USE Master
GO
RESTORE DATABASE ‘database name’ FROM DISK=N’ path of database backup
You can generate the script in SSMS as well. Consider the screen shots given below:
- In the SSMS, click Databases > Restore Databases
- In the resulting window click Script
- Here is the query that you will get as a result:
USE [master]
RESTORE DATABASE [SQL_TST_DB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\SQL_TST_DB.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5
GO
Here, SQL_TST_DB is the database name. Also, notice the path of the back up file.
- Next, you can use the sqlcmd and invoke the script to recover your database. Here is the code for the same:
Sqlcmd -i “c:\sql\recover.sql” -E -o “c:\sql\result.txt”
This command will invoke the recover.sql, which consists of the T-SQL sentences to restore the database. Likewise, the sql/results.txt usually stores successful output messages or error messages.
Alternative Solution: Stellar Repair for MS SQL Technician
If the command line cannot do the SQL recovery task for some reason, you can still restore the backup file through an automated software. Specifically, by using Stellar Repair for MS SQL Technician you can ensure SQL recovery of your files from corrupted backup as well as .mdf files. Here is how you can use this useful software to recover your databases.
- Install the software Stellar Repair for MS SQL Technician version and run it.
You will see a window with three options:
- Repair MS SQL Database
- Extract from MS SQL Backup
- Recover MS SQL Password
- Choose the first option. The resulting window will ask you to close the SQL Management Studio or stop the MS SQL Server if the MS SQL Server is running.
- If your MS SQL Server is already closed, copy the database.
- Paste the database file to another location and press ‘OK.’
- Choose the corrupt database and press the ‘Browse’ button.
- In the resulting window, choose .mdf file.
- Press the ‘OPEN’ option and go to the previous window.
- You will see a .mdf file in the address bar. Click the Repair button
- The procedure will take a few seconds. However, if your database is large-sized, it may take a few additional minutes.
- After the process completes, you will notice successful repair of your chosen MS SQL Database.
- Press the ‘OK’ option located in instruction dialog window.
- Next, click on the ‘Save’ button and a window will appear with a certain functionality of the Stellar Repair for MS SQL Technician. It will allow you to save your repaired database.
- Save the repaired database file in more than one extensions. Here, you will get four types of extensions – CSV, MDF, XLS, and HTML.
- In the part Saving Option, save your database in the current and new file.
- In the option ‘Server Name’, select the name of the server.
- Also, in the Authentication part, choose the type of authentication and then save the database.
- After choosing those options, you will get the scope to save the file as Default SQL.
- In another option, you may choose the checkbox showing ‘New.’ This will allow you to save your file as a new one.
- Click the browse button and in the window that appears, choose the exact location where you wish to save your newly repaired database.
- Click ‘SAVE’ button, after choosing the location of the repaired database.
- A progress bar will run for a few minutes to save your file.
- A message citing successful saving of your SQL Database will also appear, resulting in satisfying SQL recovery.
Conclusion
Your database can become corrupt because of several reasons. In any case, it can halt the working of your business. Prompt database restore is therefore imperative, but before you do it, using DBCC CHECKDB will reveal all the other issues in the database (if any). You can proceed to restore after correcting these glitches.
Even after you complete the restore with the command line or through any other method, using DBCC CHECKDB will ensure that the database is free from inconsistencies and corruption. Evidently, you can resume using it for your business tasks. Your can restore SQL Server Database from Command Line or by using command line with scripts.
With these steps, you can complete the task with ease and use your recovered database later. If these manual methods are taking more time or if your database is still having issues, switching to an automated solution such as the Stellar Repair for MS SQL Technician will be an ideal solution as this software is the best for the SQL database recovery.
