MSSQL Data Recovery Guide: DBMS Files
-
Author:
Vladimir Artiukh
-
Editor:
Oleg Afonin
Learn how to recover and prevent loss of DBMS files and data in MSSQL with ease in this comprehensive guide. Whether you’re a beginner or experienced user, discover step-by-step instructions and essential tips to seamlessly recover and prevent loss of your valuable database files and data. Dive deep into MSSQL data recovery and loss prevention techniques and explore insider strategies to maximize your chances of successful data restoration and prevention.
The main tool in the interface of SQL Server is Microsoft SQL Server Management Studio (SSMS).
- Files of the Microsoft SQL Server System
- Why MSSQL Can Lose Data
- Methods to Restore a Database
- Recovering a Deleted Database With Hetman Partition Recovery
- How to Create a Copy of SQL Server Database for Further Restore, Import or Transfer
- Questions and answers
- Comments
Files of the Microsoft SQL Server System
How to Recover MySQL, MSSql and Oracle Databases ⚕️👨💻🖥️
SQL Server files are saved to disk by default:
C:\Program Files\Microsoft SQL Server
For every base, a separate folder with its name is created. For example, in our case there are two Microsoft SQL Server databases created: MSSQL13.SQLEXPRESS, MSSQL13.HETMAN.
Data from any MSSQL database is stored in working system files of three types:
- *.mdf – a primary database file. Such file contains information necessary to launch the base, links to other database files, user data and objects. The data of any base is physically stored in an .mdf file.
- *.ndf – primary database files which are also used by the system to store the data of a base.
- *.ldf – transactions log files.
Each of these files has the name similar to the name of their database and is stored in the folder \DATA:
C:\Program Files\Microsoft SQL Server\DataBase Name\MSSQL\DATA
When creating and adjusting a MSSQL database, you can change the folder where to keep the database files. For safety reasons, and because database files can be quite large, it is recommended to save them to a disk other than C.
Why MSSQL Can Lose Data
In order to select the proper database recovery and backup methods, you should understand what can cause the loss of such data. There can be a number of things to cause such a problem, bu the main causes are here:
Software errors. These include logical errors or system failures. As a result of such errors, the system performs a disorderly close-down, after which restore operations are impossible.
Hardware failure or breakdown. The most frequent cause of a database information loss due to hardware issues is breakdown of the hard disk. However, Microsoft SQL Server data (or database) can be lost because of a comuter breakdown for any reason, as long as the user was working with the database at that particular moment.
The human factor. Loss of data as a result of unintended actions by the user or the system administrator.
Methods to Restore a Database
There are several methods to backup and restore a SQL Server database. The use of every method depends on what your goal is: performing scheduled backup of a database, or recstoring from a backup when transfering a database to another computer, or recovering data from a MSSQL database as a result of its deleting or loss.
You can create a database copy for further restoration both with the built-in tools of Microsoft SQL Server Management Studio, or manually. Creating and restoring a database from a manually created copy is a quicker process that creating an unpacking a backup copy, but it is less reliable.
In addition, if you copy database files manually without stopping a database or during a transaction, such files will be saved in an unaccorded form, which will result in errors when you try to use such files to restore the system. That is why, before you create a copy of MSSQL files manually (copies of database files and trasnactions logs) for your backup, the database should be stopped (switched into an offline mode).
To do it:
-
Launch SQL Server Configuration Manager.
-
Select SQL Server services
-
In the right windows of the manager, right-click on the database you need to stop and select «Stop».
-
You can start a database in a similar way by selecting «Start» from the menu.
A database can also be stopped and started with the help of commands:
- В Transact-SQL:
SHUTDOWN; - From the command prompt window:
Net stop MSSQLHETMAN
Net start MSSQLHETMAN
Where MSSQLHETMAN is the database name
Recovering a Deleted Database With Hetman Partition Recovery
How to Create a Microsoft Hyper V Virtual Machine and Recover Data from a VHDX Disk 💻📦⚕️
If a SQL Server database was lost or deleted from a computer, it can be recovered, on condition that the hard disk is working properly. It can be done with the program for hard disk data recovery – Hetman Partition Recovery.
In order to recover lost files of an MS SQL Server database:
-
Run Hetman Partition Recovery and scan the disk where the data files of SQL Server were stored
-
Using the program, go to the folder containing database files
-
Recover the required *.mdf, *.ndf, *.ldf data files
-
Attach the recovered data files to the SQL Server database with the function «Attach…»
To do it, enter the database and right-click on the folder «Database». Select the menu «Attach Databases» / «Add» button, specify the *.mdf data file of the recovered database and then click OK.
It should be noted, however, that if a database was deleted or lost due to a failure in the work of your computer (which could result in disk formatting or reinstallation of the operating system), and at the time of such loss / deleting the work of such database was not stopped, further starting such database can produce errors. If you need to recover a manually created copy of database files, there will not be any problems with recovering and starting it.
How to Create a Copy of SQL Server Database for Further Restore, Import or Transfer
In order to avoid loss of data from an MSSQL database in case of unforeseen circumstances, and when it is necessary to import a database or transfer it from one computer to another, Microsoft SQL Server Management Studio (SSMS) offers a range of tools for all such cases, some of which we have already mentioned in this article.
Create a Backup Copy… / Restore
In order to create a backup copy of a database, right-click on the folder with its name and select Tasks / Backup…
As a result, in the folder \Backup
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLHETMAN\MSSQL\Backup
a *.bak file containing a backup copy of the database will be created.
In order to restore a backup copy of a database, right-click on the folder with its name and select Tasks / Restore, then specify the path to the backup file.
Import Data… / Export Data…
With the help of the function Import Data / Export Data in Microsoft SQL Server you can copy data from a srouce to a destination file or server. This function supports such sources of data:
- SQL Server
- Microsoft Access
- Microsoft Excel
- Flat (non-structured) files.
In other words, data from a SQL Server database can be exported to another SQL Server or in an Access file, Excel file, or in a flat (non-structured) file. You can import data into SQL Server from the same sources.
To export data from a database, right-click on the folder with its name and select Tasks / Export Data….
Use the SQL Server Import and Export Wizard that appears to specify the data source and destination.
You can import data into a database in a similar way, using the menu Tasks / Import Data….
Detach… / Attach..
The most suitable way to create a database copy to transfer it to another computer is the function Detach… / Attach…
This method is convenient because it doesn’t create additional database files or files in other formats. As a result of detaching, a database is no longer shown in the database list, while its data files are still in their previous location, in the folder \DATA.
Such data files can be transferred to another location which is convenient for the user without a risk of losing data from a corresponding database, and attach them to the SQL Server of another computer (with a version no older than the SQL Server from which it was detached).
To detach a database, right-click on the folder with its name and select Tasks / Detach… / Ok.
To attach a database, right-click on the Databases folder and select Attach… / Add, and after that, specify the path to the *.mdf database file which you need to attach.
Note. If necessary, you can use Hetman Partition Recovery to recover a database backup copy file (*.bak), an Import/Export database file or detached database file (*.mdf, *.ndf ,*.ldf) in order to further attach or restore them in Microsoft SQL Server.
