Restoring MySQL databases using manual and “mechanical” methods. Recovering deleted MySQL database files
Read, how to recover lost or remote base MySQL. How to recover damaged tables MySQL databases using myisamchk. The MySQL database is installed by default on the computer in a folder on drive C:
C:\Program Files\MySQL\My SQL Server 5.7
But the table data is stored in files in another folder on the C drive of the computer, this is:
The location of these files is indicated in the Server Status menu MySQL applications Workbench, in the Server Directories section.
If you need to restore database tables, you will be interested in the folder with the data of a specific database and the files that are located in it.
Content:MySQL Database Files
MySQL is compatible with big amount file formats, such as: .sql, .arm, .cnf, .dbs, .ddl, .frm, .ibd, .ism, .mrg, .myd, .myi, .mysql, .opt, .phl, .sal , .sqr, .tmd, .arz, .ibz, .ibc, .qbquery, .rul. But this article is not about that. Today we are interested in precisely those files in which data and database tables are stored, by restoring which the user will be able to return important information and avoid its possible loss in the future.
The data of each database is stored in a folder with its name, and depending on the type of table, it is stored in files with the following extensions:
- db.opt– a file in which the database characteristics specified during its creation are stored;
- .frm– table structure file;
- .myd– a file in which data of MyISAM tables is stored;
- .myi– a file in which indexes of MyISAM tables are stored;
- .ibd– a file in which data and indexes of InnoDB tables are stored.
How to restore a MySQL database
Restoring a MySQL database is technically not difficult process, but it depends on many conditions. For MySQL users Workbench feature provided Export And Import/Restore database data.
In addition, it is possible to create a backup copy and restore the database MySQL data by using mysqldump(which we described in detail in one of our articles).
But these features are more related to creating MySQL data backups using built-in tools. More advanced users or those who have not used the MySQL database data backup function in time will also be interested in the method of creating copies and restoring database data manually, by substituting the structure files and table data described above:
- Creating a copy of MySQL database table data is possible by copying structure and data files (*.opt, *.frm, *.myd, *.myi for MyIsam; *.opt, *.frm, *.ibd for InnoDB) and saving them in another folder.
- Restoring data from MySQL database tables is possible by substituting previously copied structure and data files into the folders of existing databases (in our case, these are two databases: my_db and my_db2).
Recovering a lost or deleted MySQL database
So, if for some reason you deleted the MySQL database, reinstalled Windows or formatted HDD, then you can restore it using the method described above, by placing the previously copied database files into the folder with the name of the database:
C:\ProgramData\MySQL\MySQL Server 5.7\Data
If the user did not create a copy of the database files in advance, they can be restored using , and then transferred to the desired folder in the manner described above.
For this:
Recovering damaged MySQL database tables using myisamchk
MyISAM MySQL database tables can be damaged as a result of unexpected interruption of the recording process or computer shutdown, hardware failures or software, and also in case of trying to debug using myisamchk table used by the server.
As a result of corruption, data may disappear from the table or display incorrectly, but most often, as a result of table corruption, users encounter the following error: “Incorrect key file for table: ‘table_name’. Try to repair it"
To restore damaged MyISAM tables, you can use the command myisamchk.
Myisamchk works by debugging and creating a copy of the .myd file, and then replacing the damaged file with it. Therefore, before using this command, it is better to create first backup copy table file that needs to be restored.
So, to restore a damaged table, use the command:
myisamchk -r -q TABLE_NAME
Where, -r -q– this is the mode quick recovery. IN in this case will be fixed index file without changing the data file. If the data file contains everything needed and the remote links point to correct positions in the data file, the table will be corrected as a result of this command.
If the previous command does not give required result, then make a backup copy of the data file and run the command:
myisamchk -r TABLE_NAME
Where, -r- it's just recovery mode. In this case, incorrect and lost entries will be deleted from the data file, and the index file will be created anew (as described above).
note. If you plan to debug and restore the table from command line, then you must first stop the server. It should be noted that when running mysqladmin shutdown with remote server mysqld will still run for a while after mysqladmin exits, until all queries are stopped and all keys are flushed to disk.
Today in lesson: MySQL.(MySQL is a free database management system). Restoring a database is quite simple, but to do this you need to have a backup copy of the database. When I had problems with the database, I could not access the site, and all my articles disappeared. I had to urgently decide how to restore the Wordprerss database.
This situation happened to me a couple of days ago when I was writing lesson 59. Literally a few minutes later I received an SMS from Yandex Metrica that my site was not available. If you have encountered such a problem before, then you know how to return everything to working condition, and if not, read on.
How to Restore WordPress Database (Method 1)
Most people write on the Internet how to make a database backup, but few people write, how to restore the database WordPress data . It is not necessary that with database Problems may arise due to your fault. Errors may occur in the database for other reasons.
If your blog has not yet installed a plugin or a similar one, then you risk being left without a blog. Imagine you are blogging for a long time, and then once, and that’s it! Amba! This plugin will not allow this to happen. It keeps your blog's database permanently in automatic mode and without your participation.
There is a lot of information on database recovery on the Internet, but sometimes they write such nonsense for which people are also grateful. The author of the article gives advice on how to do this and that correctly, but he himself does not even understand what he is writing about. That's it, it's time to get down to business :)
To return your blog to its previous state, you must have a fresh database backup. Unpack the database file and open the unpacked file in Windows notepad. Copy the contents of the file to . Go to the control panel on your hosting in PhpMyAdmin.
Click the name of the database you want to restore.
Then you need to click on "SQL" and paste into the window what you copied from the database file by clicking " CTRL " + "V". Click later " OK ".
Wait until the database restoration is completed. A success message should appear.
Your blog is now completely restored.
How to quickly restore your WordPress database (method 2)
So, without unnecessary introductions. Go to your hosting in control panel(cPanel). Find the link " MySQL" or " PhpMyAdmin ».
Now you need to log in to the database management panel, i.e. PhpMyAdmin. Click " To come in»
You will be taken to PhpMyAdmin. On the left, click on the database you are going to restore. In my case this is the dvpress database.
Once you select a database, all the tables in that database will appear. To ensure that no errors occur during recovery, this database must be completely deleted. We go down to the very bottom and find “ Mark all / Deselect " Click on " Select all "so that all database tables have a check mark in the checkbox. Select in the window to the right " Delete"and then confirm" Yes". The database should be completely cleared of all tables.
Now your task is to restore this database from a backup copy. Click at the top " Import", then click on the button " Choose File " Find the backup copy of the database on your computer and click " Open" Now in PhpMyAdmin at the bottom click " OK" The operation must be successful, which should be indicated by the inscription “ SQL query completed successfully ».
SQL Server supports three various types backup copies – full backup, differential backup and transaction log backup. The first two types of backups are available for databases in any recovery model, a transaction log backup is available for databases in the FULL and BULK-LOGGED recovery model (you can read briefly about recovery models, or better yet, in BOL).
If you use the SIMPLE recovery model, you will be able to restore your database only from a full backup (well, and additionally, differential copy). You will never recover to a point in time before the failure unless you created a backup immediately before the failure. If your database is in the FULL recovery model and you never back up the transaction log, but trim the log from time to time, read on to find out what you are missing out on :).
If you are reading this, I assume that your database is in the FULL recovery model, you regularly take full backups and transaction log copies. Additionally, your "recovery chain" from transaction log backups should be intact.
What is the "recovery chain"? This is a continuous sequence of backups consisting of a full backup and a continuous sequence of transaction log backups. For example, at 12.30 you created full backup(let's call it full1), and at 12.45, 13.00 and 13.15 they created backup copies of the transaction log (trlog1, trlog2, trlog3, respectively). As long as you have all these backups, you will be able to restore your database to any point in time between 12.30 and 13.15, 12.48 for example.
If you delete the copy of trlog2 created at 13.00, then the backup copy of trlog3 (and all copies made subsequently) will become completely useless - you can restore to any point in time between 12.30 and 12.45. The same thing will happen if someone creates a copy at 12.31 and deletes it - all subsequent copies will be useless. To start a new recovery chain you will need to take a full backup and then a transaction log backup.
There is one not very obvious (at least it was for me) moment in all this. A full backup always starts but never breaks the restore chain (this is true for SQL Server 2005 and older). Let's look at this with an example. In addition to the existing copies (full1, trlog1, trlog2, trlog3 - a continuous recovery chain), we will make a full backup of full2 at 13.30 and transaction log backups trlog3, trlog4, trlog5 at 13.45, 14.00 and 14.15, respectively.
Now, if we need to restore the database to 14.15, the easiest way is to use the "new" recovery chain, i.e. restore full2 and “roll” trlog3, trlog4, trlog5 onto it sequentially. But, if it turns out that we cannot recover from copy full2 (for example, the disk containing this copy was destroyed), then we can use our “first” recovery chain - restore backup copy full1 and sequentially roll copies of the transaction log trlog1, trlog2 onto it , trlog3, trlog4, trlog5, trlog6. In the same way, we can use the first recovery chain to restore the database to 13.29 - we restore full1 and roll up trlog1, trlog2, trlog3 and trlog4.
Thus, having a complete recovery chain, we can restore the necessary base data at any point in time between the time the full backup was created and the time the last backup was created. A small hint - if you have full model recovery, there are several backups and no copies of the transaction log, but you never “truncated” the transaction log - you can create this backup right now and be able to restore to any point in time between the time of the very first full backup and the current moment time. But, I repeat, only if transaction log backups have not been created before and the transaction log has not been trimmed.
Next, I will tell you several ways to use SQL Server backups.
The first option (it works for all recovery models) is necessary restore a database from a full backup. Everything is simple here - you need to run the T-SQL script:
RESOTRE DATABASE [database_name]
FROM DISK = "path to full backup"
WITH REPLACE, RECOVERY, STATS = 10
The REPLACE parameter indicates that if a database named [database_name] exists, then we replace its contents with the contents of the backup copy. If you are restoring a database using the GUI, you need to specify “Overwrite the existing database” on the “Options” tab.
RECOVERY indicates that the database needs to be brought into a consistent state and allow user connections. In the GUI this corresponds to the option “Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored." That is, SQL Server rolls back all uncommitted transactions and allows users to work with the data. After you have restored the backup from with the RECOVERY parameter, additional transaction log copies and differential copies cannot be applied to it. Be careful, this option is set by default and if you want to “roll up” another copy, the recovery process will have to start again.
STATS = 10 is responsible for displaying information about the recovery process. In this case, when every 10% of the backup is restored, a message will appear on the Messages tab in SSMS. When restoring using the GUI, you cannot control this parameter - you can monitor changes in the “graph” in the lower left corner.
By default, the copy is restored to the same location from where it was made. Those. if initially all database files were in the C:\Database directory, and you want to restore the copy to another location, then you must use the MOVE parameter. To use MOVE you need to know the logical file names - these can be viewed using the sys.database_files view. The figure shows an example of using this view.
Therefore, to restore the AdventureWorks database from a backup and at the same time move it to another location, you can use the following script:
WITH REPLACE, RECOVERY, STATS = 10,
If the restore is performed using the GUI, you must specify new file names on the “Options” tab (table “Restore the database files as:”, column “Restore As”).
Second option - restoring from a full backup and all transaction log backups(or differential copies).
A restore always starts with restoring a full backup, but if you want to restore any additional copies after that, you need to leave the database in the RESTORING state. To do this, we will use the following script:
RESTORE DATABASE
FROM DISK = "D:\backup\awfull.bak"
WITH REPLACE, NORECOVERY, STATS = 10,
MOVE "AdventureWorks_Data" TO "D:\database\AW_data.mdf",
MOVE "AdventureWorks_Log" TO "D:\database\AW_log.ldf"
If after performing the restore you try to access the database, you will receive the error:
Database "AdventureWorks" cannot be opened. It is in the middle of a restore.
After restoring the full backup, restore the last differential copy (if any):
RESTORE DATABASE
FROM DISK = "D:\backup\awDIFF.bak"
WITH NORECOVERY, STATS = 10
If you have a SIMPLE model, you can stop here - you won’t do anything else. If you have a FULL model, you can additionally roll back transaction log backups:
RESTORE LOG
FROM DISK = "D:\backup\awlog1.trn"
WITH NORECOVERY
….
RESTORE LOG
FROM DISK = "D:\backup\awlog12.trn"
WITH RECOVERY
Note, latest copy must be restored with the RECOVERY parameter. If the database remains in the RESTORING state, it can be brought into operational state and without backups:
RESTORE DATABASE
WITH RECOVERY
After this, the database will “come back to life”.
Third option - point in time recovery. I have the AdventureWorks database, a full backup of it, and a transaction log backup from earlier. Result of the request
SELECT *
FROM Person.AddressType
shown in the figure:
At 13.46 this table was deleted. The same query now returns:
Invalid object name "Person.AddressType"
To return everything to its place, first of all we make a backup copy of the transaction log:
BACKUP LOG
TO DISK = "D:\backup\awlog2.trn"
Once the copy is created, restore the full backup and the first transaction log backup (both with the NORECOVERY parameter).
Now let's restore the last backup created AFTER the table was deleted:
RESTORE LOG
WITH RECOVERY, STOPAT = "09/10/2010 13:45"
(the date here is specified in the format "MM/DD/YYYY HH:MM")
After this, the original query returns a normal result, the table has been restored.
Another option for data recovery. Life story :).
A few months ago we found ourselves in a not very pleasant situation - entries in one of the information registers (periodic, not subordinate to the registrar) were “corrupted”. That is, they seem to have remained, but they have lost their meaning. The register was large and necessary. The error occurred several hours ago, so it was impossible to completely restore the database. We were going to transfer data using XML Data UploadLoading processing, but the programmers asked to try transferring data using SQL, in the hope that it would be faster.
We restored a copy of the database from backups to the point in time before the data corruption. Using the “GetDatabaseStorageStructure” function, we found out the name of the information register in the database - _InfoReg4452 (for example, I don’t remember exactly). We cleared this “damaged” register in the main database using TRUNCATE TABLE _InfoReg4452.
On the copy of the database, we selected TASKS -> ExportData, a copy with normal register was specified as the source, a working server and a working database (with cleared register) as the destination. On the object selection page, we selected only one – the table we need:
Two more mouse clicks and that's it. The table was moved, people continued to work.
I would like to draw your attention to several nuances. Firstly, if you do not clear the destination table, all the data will remain in it, and new ones may not appear. Secondly, this method may be suitable for some information registers and directories, but for tables on which movements are made, or objects that make movements, it will be necessary to restore many additional tables– Think 100 times before using this method. Well, in general, this method goes against license agreement, but it is fast and convenient in some cases.
I hope this information was useful to at least someone.
P.S. Don't take my word for it - try doing all this on your copies!
For developers and administrators Microsoft SQL Server quite often the need arises restore a database from an archive(backup), for example, to create a copy of the database that will be used to test the application, or for example, you reinstalled operating system and you need to restore the database, which is exactly what we will do today.
In this article we will look at database recovery using a DBMS as an example. Microsoft SQL Server 2000. In order to restore a database, you need to have an archive of this database. It is created with the *.dat extension. I recommend everyone to create archives of their databases, and daily ones; if anything happens, you will always have an archive to restore.
Description of the process of restoring a database from Backup
Let's start, open Enterprise Manager, select the database that you want to restore, or if you have just installed the DBMS and there are no databases there yet, then simply create a database and restore it from the archive. Open the menu " Tools->Restore Database"and the following window will open:
Select " From device" and click " Select Devices».
(loadposition banner3
Click OK and in the “ Choose Restore Database» also click OK. And in the main window " Restore Database"Don't rush to click OK yet, go to the " tab Options» and check the box as in the picture.
Also here set the path for the database files, as it may be incorrect and you can overwrite, for example, existing base data, if you create a copy of the database, and if you have just installed everything (DBMS), then you don’t have to change it.
That's it, after that you can click OK. After some time, the database will be restored and you can use it.
By the way, if anyone is interested T-SQL language (is an extension of the SQL language in Microsoft SQL Server), then I can recommend the book “ The T-SQL Programmer's Path. Transact-SQL Language Tutorial", after reading which you will be able to easily program in T-SQL.
Which I posted last week seemed to arouse some interest, but, alas, it did not contain “practice”. Yes, it says how you can save data, but there are no examples.
Initially, I wanted to make another translation by the same author, but after thinking about it, I decided to write a post “on my own”, as if “based on it”. I will describe the reasons that prompted me to do this at the end of the post, in the notes.
Restoring Databases in SQL Server
As mentioned in the previous article, if pages of a clustered index or heap are damaged, then the data contained on these pages is lost and the only option for recovering them is to directly restore the database.SQL Server provides many options for database recovery. Firstly, this is a restoration of the entire database - it can take quite a lot of time (depending on the size of the database and hard speed disks). Secondly, recovery of individual file groups, or files if your database consists of several file groups (or, accordingly, files). In this case, it is possible to restore only damaged parts of the database without affecting the rest. These two types of database recovery are used quite often and will not be discussed further.
Third, SQL Server 2005 introduced the ability to restore individual pages DB - in this case, only specified pages. Such recovery will be especially important if DBCC CHECKDB finds several damaged pages in some huge table “lying” in a hefty file. Due to the fact that not the entire file, and not even the entire table, but only a few pages will be restored, downtime can be significantly reduced.
Requirements and restrictions
Recovery model and availability of transaction log backups
The most important thing to remember is that to recover individual pages, the database must use a full or bulk-logged recovery model. If your databases are in a simple recovery model, you don’t have to read any further.The second requirement is that your full and transaction log backups must form an unbroken log chain. If you never issue the BACKUP LOG WITH TRUNCATE_ONLY (NO_LOG) command and switch to simple model recovery in order to reduce the transaction log, and you have ALL transaction log backups since the last full backup containing no corrupted pages (including that most complete backup) - you don't have to worry about the log chain.
In the bulk-logged recovery model, in theory, individual page recovery should work fine as long as the conditions described above are met and the pages being recovered have not been modified by bare-logged operations.
SQL Server editions
Restoring pages is possible in any edition of SQL Server, but for the Enterprise Edition and Developer Edition it is possible to restore damaged pages on-line, i.e. the database can be accessed during recovery (and what’s more, you can even access the table that is being restored in this moment pages, if these pages themselves will not be “touched” - otherwise, the request will fail). For editions “below” Enterprise Edition, page restoration takes place off-line and the database becomes inaccessible during the restoration period.Type of damaged page
If index or data pages are damaged, they can be restored online in the Enterprise Edition.Pages belonging to critical system tables can be restored, but the database, when restored, will be unavailable in any edition of SQL Server.
"Placement cards" cannot be restored "separately". If GAM, SGAM, PFS, ML, DIFF pages are damaged, it is necessary to restore the entire database. The only exception is IAM pages. Although they are classified as "location maps", they describe only one table, not the entire database, and they can be restored.
The database loading page (the 9th page in the 1st database file) and the file header page (the 0th page in each file) cannot be restored “separately”; if they are damaged, the entire database will have to be restored.
Actually, restoration
Now, finally, we move from theory to practice.First of all, for training, you need a corrupted database.
Porting the database
For my experiments, I'll be using the AdventureWorks database that comes with SQL Server. If you have not installed it, you can download it if you wish. I translate it into the full recovery model:ALTER DATABASE AdventureWorks SET RECOVERY FULL I make sure that there are no errors in it yet:
DBCC CHECKDB("AdventureWorks") WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY and create a full backup:
BACKUP DATABASE AdventureWorks TO DISK = "D:\tmp\aw_backups\aw_full_ok1.bak"
In this database I create a crash table.
CREATE TABLE crash (txt varchar(1000)) We will corrupt the varchar type field in order to check what will happen if SQL Server suddenly finds in it data that is not the same as what it wrote there.
Before you spoil something, you need to fill it with something. I enter the left data into the created table.
SET NOCOUNT ON DECLARE @i INT SET @i = 1 WHILE @i<100000
BEGIN
INSERT INTO crash
SELECT REPLICATE("a", 1000)
SET @i = @i + 1
END
SET NOCOUNT OFF
Теперь делаю резервную копию журнала транзакций:
BACKUP LOG AdventureWorks TO DISK = "D:\tmp\aw_backups\aw_log_ok1.trn"
Now let's change the data a little: 
So, everything is ready. We disconnect the database and open the mdf file with FAR (or whatever is more convenient for you), look for the line “zzzzzzz” in it and replace several “z” with arbitrary characters: 
Now that the database is corrupted, we connect it. And, yes, I remember that in the previous article it was clearly stated that you should not detach/attach the database. But in our case it is absolutely “safe” - the database in “suspect” will not fall.
Looking for errors
So, the damaged database has successfully returned to service. Let's run the integrity check again:DBCC CHECKDB("AdventureWorks") WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY The result is what we were waiting for ( Be sure to remember the numbers of damaged pages!):
Msg 8928, Level 16, State 1, Line 1
Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data): Page (1:20455) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data), page (1:20455). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table "crash" (object ID 1883153754).
CHECKDB found 0 allocation errors and 2 consistency errors in database "AdventureWorks".
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorks).
In this case, the data itself located in the heap (index id = 0) is damaged, so SQL Server will not be able to recover this data.
Right now we have three options:
- Accept the data loss and run DBCC CHECKDB("AdventureWorks", REPAIR_ALLOW_DATA_LOSS)
- Make a backup of the active part of the transaction log and restore the entire database - there will be no data loss as a result, but it will take a long time
- Make a backup of the active part of the transaction log and restore only one (!) damaged page
Restoring a damaged page
First of all, we need to make a backup of the final fragment of the transaction log (tail backup). At the same time, if you have an Enterprise Edition, you don’t have to add the NORECOVERY parameter, which will put the database in the “restoring” state, since this edition supports on-line page restoration. Moreover, if you have transaction log backups performed on a regular basis, so as not to break the log chain, in the Enterprise Edition, you can make a COPY_ONLY backup.I follow the path of off-line recovery and do:
BACKUP LOG AdventureWorks TO DISK = "D:\tmp\aw_backups\aw_log_fail3.trn" WITH NORECOVERY
Now, you can restore the damaged page. First of all, we use a full backup (aw_full_ok1.bak):
RESTORE DATABASE AdventureWorks PAGE = "1:20455" FROM DISK = "D:\tmp\aw_backups\aw_full_ok1.bak" WITH NORECOVERY
As a result, we have:
Please note that you need to use the NORECOVERY option, since we still have to roll transaction log backups onto it.
RESTORE LOG AdventureWorks FROM DISK = "D:\tmp\aw_backups\aw_log_ok1.trn" WITH NORECOVERY and
RESTORE LOG AdventureWorks FROM DISK = "D:\tmp\aw_backups\aw_log_fail3.trn" WITH RECOVERY 
Everything seems to have gone well, let’s run DBCC CHECKDB and... 
The restoration was successful.
Please note that downtime is reduced due to the fact that from a full backup we do not restore the entire database, but only damaged pages (if I had restored the entire backup, the backup would have been restored in 8.5 seconds, but the larger the database size, the there will be a greater time difference). The lucky ones with SQL Server Enterprise Edition who use on-line recovery will also save time on restoring from log backups, and with off-line recovery, alas, the logs will be processed in their entirety.
It is also worth adding that in SQL Server 2005, 2008, 2008 R2, restoring a single page is only possible using T-SQL; in Denali, it became possible to do this through the GUI.
But what if it’s DBCC CHECKDB?
Just in case, I decided to check what SQL Server would do if I ran DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS parameter. Still the same error:
First, we switch the database to SINGLE_USER mode:
ALTER DATABASE AdventureWorks SET SINGLE_USER And then, we start the recovery:
DBCC CHECKDB("AdventureWorks", REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY As a result:
Repair: The page (1:20455) has been deallocated from object ID 1883153754, index ID 0, partition ID 72057594054246400, alloc unit ID 72057594061651968 (type In-row data).
Yep, SQL Server deleted the “corrupt” page. We switch the database to MULTI_USER mode so that it becomes accessible to everyone and check what is missing:
Considering that the page size in SQL Server is 8KB, and a little less is available for user data, everything is natural, the table has “lost weight” by 7 records (at the beginning there were 99999). Since there was no clustered index on this table, the data could be stored in random order, i.e. we couldn't even know what data would be lost.
So why, after all, not a translation?
So, why is this not a translation, but a post “based on”? The fact is that the article “Page Restore” authored by Gail Shaw is not publicly available. There is such a section in the book SQL Server MVP Deep Dives vol.2, which is sold for quite a significant amount of money (but, of course, is easily found on the Internet) and I’m not sure that publishing the translation is um... right or something.In general, I read the article, took note of the main points, and then wrote the text myself and, along the way, conducted a restoration experiment. I hope this experience was useful to someone.
And, gentlemen, I sincerely hope that if you decide to repeat this experiment, you will be extremely careful (for example, you will not experiment with the main database on the production server). Remember that I do not bear any responsibility for your actions.
