Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the solution that SQLSERVER can not start (error 9003). Many people may not understand it very well. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

First talk about the environment customer environment: Windows2003 Enterprise Edition SP2 32-bit SQL2005 Enterprise Edition 32-bit SP4 own laptop Environment: Windows7 SP1 32-bit SQL2005 individual developer Edition 32-bit my laptop computer name: joe client computer name: hs

The size of the master database on the customer's side: several MB business systems are winform systems. The environment of the customer is that the stand-alone system is not used in the domain network environment: the network speed of the customer side is relatively slow, so why should I explain the environment of my laptop when using remote assistance? Please keep watching patiently.

Check

First open the SQLSERVER configuration manager, start SQLSERVER, and find that SQLSERVER cannot be started.

So I opened Windows EventLog and found the following error

The code is as follows: invalid SQLSERVER error 9003:LSN (invalid log scan number). The log scan number passed to the log scan operation in the database 'master' (2806 master' 120 master') is invalid. This error may indicate data corruption or a mismatch between the log file (.ldf) and the data file (.mdf). If this error occurs during replication, recreate the publication. Otherwise, if the problem causes an error during startup, restore from backup.

So I searched this error on my computer for hundreds of degrees and found this article: sql server error 9003:LSN is invalid (the log scan number is invalid), the database in this article is the user database, the database in this article is the user database, using rebuild log,dbcc checkdb to solve the problem sadly, what is damaged by the customer is the master database

As a good database engineer, we must quickly know what methods can solve the current customer's problems and what are the advantages and disadvantages of these methods, because a delay of one second will cause more losses to the customer. the customer's business system can not function properly, and the consequences can be imagined.

Based on the following factors, I made a choice that the network speed is relatively slow and it is not convenient to write SQL sentences on the customer's computer. The business system is the winform system.

Choice: a method that the project manager taught me before. When SQLSERVER fails to start, I can replace the customer's master database with the master database of SQLSERVER that has just been installed.

This method has the following disadvantages

(1) the database version you use must be the same as that of the customer. (2) it is not possible to give SQLSERVER2012's master database to the customer. (3) Server triggers, certificates, linked servers, login users and other information will be lost.

Why do you have these disadvantages? you can take a look at the following introduction to Service SID in SQL Server 2008.

The code is as follows: [introduction of SERVICE SID] NT SERVICE\ MSSQL$KATMAI, NT SERVICE\ SQLAgent$KATMAI and NT SERVICE\ ClusSvc are all names corresponding to Service SID. The introduction of Service SID is to solve the security risks caused by multiple Service using the same service account. If IIS uses a Network Service account, other services may also use a Network Service account. In order for IIS to connect to SQL Server, we might use Network Service as the login of SQL Server, but this is not secure. Because other services can also access SQL Server if they use Network Service as their startup account. To solve this problem, in SQL Server 2008/Windows Server 2008 and after, we have the concept of SID, so that different services, even if the service startup account is the same, their SID is also different.

Because the mechanism of logging in users has changed during SQLSERVER2008, the database version you use is not the same as the customer's, but even if it is the same, there are drawbacks. The customer's login user originally looks like the following.

The code is as follows: builtin\ administratorshs\ administratorshs\ SQLServer2005MSFTEUser$hs$MSSQLSERVERhs\ SQLServer2005MSSQLUser$hs$MSSQLSERVERhs\ SQLServer2005SQLAgentUser$hs$MSSQLSERVER

But if I give my own master database to the customer, it will look like the following. If the customer's business system is the BBGUA S system, it will use IIS, or the customer's environment is a domain environment, and logging in to SQLSERVER requires Windows login authentication, not SQL login authentication, so it is very troublesome to do so. There is also a SQLSERVER of my own computer is a personal developer, and the customer's is an enterprise version, but I have done this before. Found there was no problem.

Begin to implement

The malpractice is over, and it has been put into practice.

Step 1: first separate all the SQLSERVERD user databases on my own laptop

Delete some login users that were not available at the time of installation, only keep the default login users, stop the SQL service on their computers, and then copy master.mdf and mastlog.ldf to the customers.

Step 2: use remote Assistance to rename the master database master.mdf file on the client's computer to master22.mdf

Rename the mastlog.ldf file to mastlog22.ldf

Then put the sent master database into the data folder.

Step 3: start SQLSERVER. Since there is no screenshot at that time, there will be no screenshot here. At this time, we can find that SQLSERVER has been started successfully.

Step 4: re-attach our business library

Step 5: delete the database user gxhtsyrain in the original business database

Step 6: create a new login user gxhtsyrain and map it to our business library

Step 7: disable sa users

Since the msdb database is not replaced, the maintenance plan is not lost

Error log

I checked SQL ERRORLOG and found the following error

Cannot enable Service Broker in database "msdb" because master is not the same as ID in msdb's Service Broker

NT AUTHORITY\ SYSTEM login failed

Since we do not use Service Broker technology, the error of "unable to enable Service Broker" in database "msdb" can be ignored.

NT AUTHORITY\ SYSTEM login failed, as mentioned in the introduction to Service SID in the article SQL Server 2008

The copy code is as follows: but we can't assume that with these SID accounts, we can easily delete other logins such as NT AUTHORITY\ SYSTEM.

Some applications, such as VSS Writer,Windows automatic updates, still need to be processed with a service startup account.

If the customer has a backup of the SQLSERVER system database

Here's what I'll do:

Step 1: back up the master database on my laptop first.

Step 2: get the customer's master.bak to your computer.

Step 3: restore the customer's master.bak to their own SQLSERVER

Step 4: stop the SQL service, then copy the master.mdf and mastlog.ldf and send them to the customer

Step 5: rename the master database master.mdf file on the client's computer to master22.mdf,mastlog.ldf file to mastlog22.ldf.

Step 6: then put the sent master database into the data folder

Step 7: start SQLSERVER

In fact, in addition to backing up the user database, the backup of the system database is also very important. We need to back up the two databases, master and msdb. When we deployed the business system at that time, we did not back up the system database, which is actually a negative example.

After reading the above, do you have any further understanding of the solution that SQLSERVER does not start (error 9003)? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.