SQL Server database forms the backbone of an organization. It consists of all the sensitive data required for smooth business functioning. Corrupt or damaged data could halt the working of your company, leading to loss of data, finances, customer trust, and market reputation. Prompt restore of the database from the corrupt file is hence imperative.
You can execute this job with the help of the SQL Server Management Studio interface or by using T-SQL commands. Using a SQL recovery tool such as Stellar Repair for MS SQL will be even better for MDF file repair. We will discuss these methods right here.
SQL Server Database Backup and Restore through SSMS
Restoring a SQL database with the help of a recently created backup is possible with the help of SQL Server Management Studio. We will discuss this process right here.
Taking Backup Using SQL SERVER Management Studio (SSMS)
Let’s start with creating the back of the database that you want to restore. Here are the easy steps.
- Start SQL Server Management Studio (SSMS)
- Connect to SQL Server instance
- In the Object Explorer expand the Database node
- Right-click the database for which you want to create a backup.
- Click Tasks -> Backup
- Under Source section, check the database name
- Select the backup type that you want, whether Full or Differential
- Under Destination section, select whether you want to store the backup to the disk or a URL.
- Confirm the path of the database backup file
- To change the path, select Remove and then, click Add to provide a new path.
- Click OK to create the database backup.
Restoring SQL Database by using Backup
- After creating a backup, it becomes easier to restore your database. Here are the steps to do it with the help of SSMS
- Launch SSMS and connect to SQL Server instance
- In the object explorer in the left pane, Right-click on the Database node
- Next, click Restore Database
- Under source, select the Database you want to restore
- You can also select Device to locate your database backup file
- Under Destination, select the Database in which you want to restore the backup file.
- Click OK to restore the SQL Server database backup
- Alternatively, you can also navigate to your selected database and then click Tasks -> Restore -> Database after doing a right-click on the Database that you wish to restore, as shown in the image below
SQL Server Database Backup and Restore through T-SQL command
Here is the Transact-SQL command to create an SQL Server backup and then restore the database by using the backup.
Creating Database Backup using the T-SQL command
To start with, admins use BACKUP DATABASE to back up a database. Here is the T-SQL command:
BACKUP DATABASE [SQL_TEST_DB]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\SQL_TEST_DB.bak’
WITH NOFORMAT, NOINIT,
NAME = N’SQL_TEST_DB-Full Database Backup’
The explanation of all the options is as follows:
- BACKUP DATABASE: Name of the database that you want to backup
- TO DISK: Indicates the Path of the Backup file’ that you want to create
- ‘WITH INIT: overwrites an existing backup file with the name of the newly created file
- WITH NOFORMAT: Avoids SQL Server from doing a format of the backup file
- NAME: Gives a name for the backup database
Database Restore using SQL Server T-Commands
In case your database has become corrupted or damaged, the foremost SQL recovery method would be to restore your database from the backup file. If you have already created a backup file, doing a data restore will be a lot easier.
Admins use the RESTORE DATABASE statement to restore a SQL SERVER database from a backup file. Here is the T-SQL command:
RESTORE DATABASE [SQL_TEST_DB]
FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\SQL_TEST_DB.bak’
WITH RECOVERY
The explanation is as follows:
- RESTORE DATABASE: Restores a SQL Server database from a previously created backup.
- FROM DISK: Includes the name and location of the database backup file
- WITH RECOVERY: Recovers the database by taking data from the backup and transaction logs
SQL recovery by using Stellar Repair for MS SQL
Recommended by SQL admins and Microsoft MVPS, this recovery tool from Stellar is an ideal one to restore an SQL Server database from backup. You can use it to extract the database from a damaged backup file, perform MDF file repair, or reset the database password. The software is compatible with SQL Server 2022, 2019, and all the older versions.
Steps to restore database through Stellar Repair for MSSQL
You may struggle to restore a corrupted MS SQL database from an obsolete or damaged backup file. Stellar Repair for MS SQL can resolve this issue effectively. Before using this software, make sure that your PC has SQL Server installed and running currently. Here are the necessary steps for this task.
- Download and install the Stellar Repair for MS SQL software
- Click the Extract from MS SQL Backup box.
- Click Select File to choose the backup file you want to repair.
- Next, start the scanning process by clicking Scan
- You will get a Select BackupSet dialog box that provides the details of all the backups taken in different modes
- Scroll through the Backup Type list to choose the backup file you want to recover
- Click Next to start the scanning
- After the completion of database scanning, you will get to see the number of records available in the backup file as logs.
- Preview the number of database records
- Click Save to save the recovered database file
- Next, choose MSSQL under the Save As section in the next window
- Click Browse to choose the location where you want to save the recovered file and then click OK
- Under the Saving Option, select Live Database or New Database.
- In the Connect to Server section, Provide the requisite details
- Click the Connect button
- Wait for the ‘Recovery process successfully completed’ message and then click OK
This will automatically save the recovered database in the chosen location.
To know more about the software or to buy it for a flawless database functioning in your organization, feel free to approach Stellar Info through its official website.
Conclusion
Several reasons can cause corruption in a database, leading the functioning of your organization to a standstill. As an instant solution, you can restore your data from a recently created backup, which makes it important to take regular backups of your database.
To create a backup and restore your SQL Server database from a recently created backup, you can use SQL Server Management Studio or T-SQL commands. Alternatively, an SQL recovery tool such as Stellar Repair for MSSQL can also prove beneficial in MDF file repair.
You can opt for its trial version to know about its working. To purchase the software, browse through the Stellar Info website today.
Also Read: Maximizing the Value of Your Data with SAP S/4HANA and SQL Server Integration
