Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to restore SQL SERVER database files, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

First of all, think about this text, the task is to say that the SQL SERVER database will be destroyed, and so on. The following two days, restore SQL SERVER database files, save the system from beginning to end.

In the afternoon the day before yesterday, I received a call from a classmate of operation and maintenance. (it was estimated that someone had reported that the system could not run). He told me that the main database of our SQL SERVER cluster was being restored and asked me if there was any solution.

WHAT HOW, it's a flash in my head, and of course there's something else, STOP.

Calm down, I started to work in my head, searching the database in my mind for various solutions and the impact of using such programs.

First of all, I confirm that

1 the business is terminated

2 whether the overall database restore can be terminated

3 how to put the business database online after termination

Can I guarantee the consistency of the data

How long will it take me

6 how bad can the situation be

Perhaps speaking of here, some viewers already want to ask, how could it be? OMG, how to restore the database well.

I would like to advise, why is it impossible? there is nothing strange in this world. If it happens, don't ask how, who did it, such boring questions as that. He happened, and now he just calls me for SAVE IT. I don't care about the rest.

After asking myself those questions, the first reflection of my brain's database is that I can't let him restore it. If I go on, I don't have a chance to save the database. OK, our SQL SERVER is ALWAYS ON, OMG, three machines.

Some people asked, HI shutdown is not over, anyway, the business has stopped, SHUT UP, there are other databases running on that database, not only the database being restored, has stopped the key business, is it possible to shut down the database server, let the whole company boiling?

1 decision, the computer cannot be turned off, other services must be output normally, and the influence cannot be magnified

Someone will say, "if you don't turn off your phone, just let him continue to restore the database, NO NO NO. Fortunately, our company has ALWAYS ON (those that still make SQL SERVER (especially the main business) not suitable for ALWAYS ON, and of course, there are those who use cluster mode (similar to ORACLE RAC). Just cry."

I immediately told the operator that I was going to shut down a SQL SERVER slave library, and here was an explanation of why I mentioned SQL SERVER CLUSTER, a function similar to ORACLE RAC and SQL SERVER. To use such a scheme, there must be no doubt about death in the event of such a problem, and the important things must be said three times. The advantages of distributed database, shut down a machine, do not affect the business, data services continue to output.

After the shutdown, I was thinking, I am not afraid, at least I left the data of a database server, although the data on the distributed database has been restored, but I kept the original data, no longer damaged. I still have a chance to survive.

I should also do something, what to do next, stop the misoperation of the backup software there, and get the answer from the third-party software vendor is NO.

OMG, this is the service you get after spending money. I understand why Internet companies have to develop everything themselves and never buy backup software, monitoring software, system protection software and so on. Because if it is developed by the company itself, it will never say the cold NO. Instead, people continue to find ways to save things that need to be saved. (later, it turns out that only the team within the company is reliable, and the key thing is actually better to develop by yourself.)

Since the software said NO, that is can not stop the restore operation, how to check the software thread, has been connecting to SQL SERVER for data restore, or multi-threaded, stop enough choking, because he has a powerful retry function, it is estimated that the use of advanced accounts such as SA to connect to his database account, I do not expect anything.

Well, my concern is to go back to the shutdown server that I kept, and I got a call saying that the shutdown server would still be powered on, because I was trying to dissolve the cluster and try to stop the database from restoring B PLAN. OMG

After booting up, I again decided to stop the SQL SERVER service, so that the nodes that were connected immediately would not be destroyed. I started copying the database files of the nodes. (here is another story, and I can't handle it without this story, BULABULA). After copying my database files to a safe place, I turned on SQL SERVER's service, like the last scene of the disappearance of the tiger in the movie fantasy PI trip, and I knew that the machine's database was dead, too.

But I have copied the database, and now I am faced with the weird questions I sometimes have.

1 I only have data files and no server to recover the database that I can use

These files have been partially restored. How many files can I restore when I get these?

3 how to recover these data files

The knowledge of SQL SERVER PAGE pages that I have learned before comes to mind.

In SQL Server, there is a 96-byte header at the top of the page, which includes PageID, the type of structure the page belongs to, the number of records in the page, and pointers to the previous and next pages. So there are 8096 bytes to store records. However, the maximum length of the data record is 8060 bytes, and as shown at the bottom of the page (the latest 36 bytes), the slot array it resides contains information about the offset of rows (2 bytes per row)

Immediately, the question turned over in my head again.

If the page header is destroyed, it is finished

2 if the data page is not continuous, it is likely to be missing and corrupted.

3 if there is a spread, the damage may be more

And according to the page allocation of the SQL SERVER database file, it would be a dead end if the first page of 9Magne0123 was destroyed.

How to do, I originally wanted to use the previously used APEXLOG to try to re-read the file, but the principle is wrong, that software is to read log files, to recover data, and this devastating blow, according to the principle of ALWAYS ON, the log is probably the first file to be destroyed. And if I want to recover, at least I have to let these data files hang on the database server again before I can use APEXLOG. Unfortunately, none of these conditions are available.

An attempt was made to mount the data file. It was reported that the MASTER FILE was damaged at 32:32 and the database could not be mounted. Well, I should have thought of this a long time ago. I was covered and my phone was forcibly turned off. I should have hung it up. It was a sign of God.

I can't just sit back and wait. I try to find other ways. Without those conditions, I can recover the data by reading the data file. According to the physical characteristics of the file, I can actually read the data page directly through SQL SERVER DBCC and restore the data, but only if the database is online. Now the problem is that the database cannot be online. And the one who is online is also the one who is restoring the unstoppable database, and you can't risk the current database. To make matters worse, my idea of copying the data to UAT or the test library failed, resulting in hapless network bandwidth.

SQL SERVER itself can read data PAGE, in fact, in principle, as long as there is software to read this PAGE, try to find out, there is really similar software, then try it, install the software, start to copy the data file, SEARCH, online, I see the data that I haven't seen for a long time, like the feeling of my first love. All right, then, recover the data, BULABULA.

In addition, after getting the AUDIT LOG, the development team wants to try another method of recovery, which is also impressive, which should definitely go down in the annals of the company's history, and I have seen from this development effort that we do not give up, and we have also gained some experience on such issues, which is absolutely useful.

Summary:

First of all, I would like to thank the company that developed the software, and there are people who cracked the software, thank you again

2 if I make the choice of database deployment, 1 will not use ORACLE RAC or similar functions, because the more complex things are, it will be very difficult to recover from accidents, and if it is ORACLRE, it will be very difficult for companies to get technical insights and develop such software. If MYSQL doesn't even use third-party software, he will soon be able to solve this problem (do some work on the premise).

Although the operation and maintenance students are not in a department, I think their efforts to actively find a way to solve the problem should be affirmed.

In such a company, when there is a problem, we work together to solve the problem, rather than cold accusation (some companies have experienced, rest assured that such a thing is placed in those companies, I only have three words OMG), although something went wrong, we solved it, which is amazing. This also reminds me of whether it is better to draw a clear line of duties or to have a "big pot of rice", both of which have their advantages and disadvantages, but this time, it completely makes me feel the benefits of "big pot of rice". I remember that in a certain company four years ago, it was the same problem, but in the situation, I was really disgusted by the fact that various departments had nothing to do with their own affairs.

3 distributed database, (distributed not data fragmentation, that will not help), is helpful to this kind of problem.

4 calmness and confidence

5 experience and some problems found (just let them be in my head)

On how to restore SQL SERVER database files to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.