Hello everybody!
In this video, I’m going to tell you about recovering MySQL, MSSql and Oracle databases.
How to backup such databases, which software to use for this purpose, and how to restore
the databases from such backups.
Creating a backup copy and restoring MySQL databases with MySQL Workbench
The utility MySQL Workbench has functions of Export and Import/Data Restore for databases.
To create a backup copy in the program, click Data Export, choose the necessary database,
then select the folder where to export the database, click Start Export below and wait
until it is completed.
The file containing a backup copy will appear in the specified folder.
To restore from backup, click Data Import/Restore, specify the path to the previously created
dump, check the necessary database and click Start Import, and the restore operation will
be completed soon.
Restoring a database manually
It is possible to recover information from a MySQL database table by putting the previously
copied structure files and data files into the folders of already existing bases (in
our case, these are two bases: my_db и my_db2).
It is possible if you have created backup copies of the files which are located by default
in the folder C:\ProgramData\MySQL\MySQL Server 5.7\Data.
Here, copy the folder containing structure files and data files, with the names corresponding
to the name of your database.
Bring them to another location; for recovery, you’ll just have to copy them back.
Now, if for some reason you deleted a MySQL database, reinstalled Windows or formatted
the hard disk, the database can be restored by using the method described above.
If these files were deleted too, I’ll show you later in this video how to restore them.
Creating a backup copy and restoring MySQL databases with mysqldump
To create a backup copy, start the command prompt; to do it, enter “cmd” in the search
field, right-click on the command prompt shortcut and select “Run as administrator.”
In the command prompt, go to the folder containing MySQL/bin; by default, it can be found in
C:\Program Files\MySQL\MySQL Server 5.7\bin Now, to create a backup copy, enter the command
“mysqldump,” space, hyphen, user_name, in my case it’s Admin, then space, hyphen,
password, space and the database_name, in my case it’s my_db, (“greater than”
symbol>), then the name of the backup database, it can be any convenient name, so I’ll use
my_db-dump.sql Press Enter, and in the said “bin” folder,
a database backup file will appear.
mysqldump -uAdmin -proot my_db > my_db-dump.sql To restore a database, use the command prompt
to follow the path to the database backup file, and enter the same command mysqldump
-uAdmin -proot my_db < my_db-dump.sql, but change the direction where the arrow is pointing,
right here.
-u – the value that shows the login which we use in this case to connect to the database;
-p – the value that shows the password for the user of this login.
[base_name] (?????) – the name of the database a backup copy of which we need to create;
(???) [название_файла_резервной_копии_базы].sql – the name of the backup file we have created
before.
To save a database backup copy to another folder, you should specify the path to such
folder before the name of the database backup file.
In this case, the commands for creating and restoring a database backup will look like
this: # mysqldump -uAdmin -proot my_db > C:\Dumps\my_db-dump.sql
# mysqldump -uAdmin -proot my_db < C:\Dumps\my_db-dump.sql
Recovering damaged MySQL database tables with the help of myisamchk
MyISAM tables can be damaged as a result a sudden interruption of a record process or
a computer shutdown, hardware and software errors and failures, and also when trying
to debug the table used by the server with the help of myisamchk.
As a result of damage, data can disappear from the table or be wrongly represented,
but most often damage to the table results in an error like this: “Incorrect key file
for table: ‘table_name’.
Try to repair it” To recover damaged tables, you can use the
command myisamchk.
To run it, use the command prompt to go to the folder “bin,” by default it can be
found in c:\Program Files\MySQL\MySQL Server 5.7\bin>
Then enter the command and give the path to its location.
myisamchk -r -q TABLE_NAME “C:\ProgramData\MySQL\MySQL Server 5.7\Data\my_db2\ new_table_myisam.MYD”
myisamchk -r -q new_table_myisam "C:\ProgramData\MySQL\MySQL Server 5.7\Data\my_db2\new_table_myisam"
And press Enter where, -r -q - is the quick recovery mode.
In this case, the index file will be corrected without changing the database file.
If the data file contains all necessary things, and the remote links indicate correct positions
in the data file, as a result of this command the table will be repaired.
If the previous command brings no result, then create a backup data file and run this
command: myisamchk -r TABLE_NAME
where, -r – is just the recovery mode.
In this case, incorrect and lost entries will be deleted from the data file, and the index
file will be recreated (as described above).
To avoid the trouble of typing the path to the folder containing the file you should
add the path to the folder with myisamchk.exe to Windows Environment Variables.
To do it, right-click on “This PC”, select “Properties,” go on to “Advanced System
Settings,” and find “Environment Variables” in the tab “Advanced.”
In the window that opens, find Path in System Variables, select it and click Edit, then
New, Browse and specify the path to the folder, or just copy and paste, then click OK.
Now you don’t have to type the path in the command, but just run the command myisamchk
-r TABLE_NAME in the directory containing the table C:\ProgramData\MySQL\MySQL Server
5.7\Data\my_db2\ myisamchk –r new_table_myisam
Recovering a Microsoft SQL Server database
Now let’s talk about recovering a Microsoft SQL Server database.
There are several ways to do it.
The use of every method depends on what your goal is: performing scheduled backup of a
database, or restoring from a backup when transferring a database to another computer,
or recovering data from a 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.
How to create a copy of SQL Server database for further restore, import or transfer
In order to create a backup copy of a database, right-click on the folder with its name and
select Tasks / Backup... and then OK.
The backup has been successfully created.
The backup copy is by default located in the folder
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\ A file with the extension .bak.
In order to restore a backup copy of a database, right-click on the folder with its name and
select Tasks / Restore, check the device, then specify the path to the backup file,
click OK, and soon the data is recovered successfully.
Creating a backup file 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’s why before creating a backup file copy manually, the SQL Server should be stopped,
and only then you can copy the files which are by default stored in the folder
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA To stop a
SQL server, you should start SQL Server Configuration Manager, which you can find in the Start menu.
After that, select SQL Server services and 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.
Creating a backup copy of Oracle Database Express Edition
To create a backup, open Oracle Application Express and select the menu Application Builder
/ Export Specify the export type: the entire workspace
or one of its components.
Set the file format to export the data into, and click on Export Workspace
After that, a sql file containing a backup copy will appear in the downloads.
You can import a file here as well: select the menu Application Builder / Import
Select the file to import and specify its type, then click Next.
The file has been imported.
Such archiving can be done with the operating system tools by manual copying all working
files of the database from the database folder which is located by default in C:\oraclexe\app\oracle\oradata\XE
Archiving is performed with the stopped database, and users cannot work with it while it is
being archived.
To restore a database damaged because of a failure, it should be stopped, and backup
copies of working files and transaction logs should be written to their previous location.
Recovering tables with Hetman Partition Recovery
All databases deleted from a computer can be recovered with Hetman Partition Recovery,
and then be sent to the necessary folder.
I’ll show you how to recover files with the example of a MySQL database.
Start the program, click on the disk where the MySQL database was stored.
The recovery wizard will start.
Choose the analysis type and click Next; when the process is over, click Finish.
Using the program, go to the folder C:\ProgramData\MySQL\MySQL Server 5.7\Data,
or find the necessary database files with the search function.
Enter the file name but be careful: files containing data will have the same name as
the tables, not as the databases.
Now, recover database files.
To do it, select them, right-click and choose Recovery.
Specify the location where you want to recover them and click Next, choose the path if necessary
and click Recovery.
Ready: the files have been recovered, now bring them to the folder with the name of
the database.
When you start MySQL Workbench after that, the restored table files that you have put
into the database folder will be accessible.
In the same way, you can restore lost MSSql bases which are located by default in
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\ A file with the extension .bak.
After that, attach the recovered data files to the SQL Server database with the function
“Attach...”
To do it, open Microsoft SQL Server Management Studio, right-click on Databases and select
Attach, then Add, specify the path to the recovered .mdf file, check it, click OK twice.
In the same way, you can use Hetman Partition Recovery to restore Oracle database files,
which are located by default in C:\oraclexe\app\oracle\oradata\XE, and after that, just copy them to this folder.
That is all for now.
Hit the Like button and subscribe to our channel.
Leave comments to ask questions.
Thank you for watching.
Good luck.