How to solve SQL server startup failure (error 9003)

Let's talk about the environment first.

Customer environment: Windows2003 Enterprise Edition SP2 32-bit SQL2005 Enterprise Edition 32-bit SP4
Self-built laptop environment: Windows 7 SP1 32-bit SQL2005 individual Developer Edition 32-bit
My laptop computer name: joe
Customer computer name: hs

Size of the customer's master database: MB
The Business System is a winform system.
The customer's environment is that the single-host system is not used in the domain
Network Environment: the customer's network speed is relatively slow.
Why should I describe my laptop environment? Please take a look.

Check

Enable SQLSERVER Configuration Manager and start SQLSERVER. It is found that SQLSERVER cannot be started.

So I opened Windows EventLog and found the following error.

Copy codeThe Code is as follows:
SQLSERVER error 9003: Invalid LSN (invalid log scan number)
"The log scan number (2806: 120: 1) passed to the 'master' of the database is invalid.
This error may indicate data corruption, or the log file (. ldf) does not match the data file (. mdf.
If this error occurs during replication, create a new release. Otherwise, if this problem causes an error during startup, please restore it from the backup.

So I made this mistake on my computer for hundreds of times.
Found this article: SQL server Error 9003: LSN is invalid (log scan number is invalid), fix the database
In this article, the database is a user database, and the rebuild log and dbcc checkdb are used to solve the problem.
Sadly, the customer damaged the master database.

Find a solution
As a good database engineer, you must quickly know which methods can solve the current customer's problems.
What are the advantages and disadvantages of these methods, because a delay of one second will lead to more losses to the customer, and the customer's business system cannot operate normally. The consequences can be imagined.

 

I made a choice based on the following factors:
The network speed is slow and it is not convenient to write SQL statements on the customer's computer
The Business System is a winform system.

Option: one of the methods that the project manager taught me in the past cannot start SQLSERVER.
You can replace the master database of the customer with the master database of the newly installed SQLSERVER.

 

This method has the following drawbacks:

(1) The database version you use must be the same as that of the customer
(2) It is impossible to give the master database of SQLSERVER2012 to the customer.
(3) server triggers, certificates, linked servers, login users, and other information will be lost.

Why are there any of these drawbacks? You can refer to the following article in workshop.
Introduction to Service SID in SQL Server 2008

Copy codeThe Code is as follows:
[Introduction of service sid]
Nt service \ MSSQL $ KATMAI, nt service \ SQLAgent $ KATMAI and nt service \ ClusSvc are actually The names corresponding to the Service SID.
The introduction of Service SID aims to solve the security risks that multiple services may share with one Service account.
If IIS uses a Network Service account, other services may also use a Network Service account.
To enable IIS to connect to SQL Server, we may use Network Service as the login of SQL Server, but this is not safe.
If other services use Network Service as the startup account, they can also access SQL Server.
To solve this problem, after SQL Server 2008/Windows Server 2008 and later,
We have the SID concept, so that different services, even if the Service Startup accounts are the same, their SID is also different.

Because the Login User mechanism has changed during SQLSERVER2008, the database version you use is not the same as the customer's.
However, there are also drawbacks in the same way. The customer's login users are as follows:
Copy codeThe Code is as follows:
Builtin \ administrators
Hs \ administrators
Hs \ SQLServer2005MSFTEUser $ hs $ MSSQLSERVER
Hs \ SQLServer2005MSSQLUser $ hs $ MSSQLSERVER
Hs \ SQLServer2005SQLAgentUser $ hs $ MSSQLSERVER


However, if I give my master database to the customer, it will become the following:
If the customer's business system is a B/S system, IIS is used, or the customer's environment is a domain environment,
To log on to SQLSERVER, you need to use Windows login verification instead of SQL login verification. This is very troublesome.
Another SQL Server on my computer is a personal developer and the Enterprise Edition of the customer. However, I have done this before and found that there is no problem.

Implementation started

The disadvantage is over. Implementation started.

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

Delete some login users that are not installed, only retain the default login users, stop the SQL service on your computer, and copy master. mdf and mastlog. ldf to the customer.

Step 2: Use Remote Assistance to rename the master database master. mdf file on the customer's computer to master22.mdf

The mastlog. ldf file is renamed as mastlog22.ldf.

Then, store the sent master database in the data folder.

Step 3: Start SQLSERVER. Because it was not available at the time, it will not be placed here. At this time, you can find that SQLSERVER has been successfully started.

Step 4: Re-append our Business Database

Step 5: Delete the database user gxhtsyrain in the original Business Database

Step 6: Create a login user gxhtsyrain and map it to our business database

Step 7: Disable the sa user

Because the msdb database is not replaced, the maintenance plan is not lost.

Error Log

I checked SQL ERRORLOG and found the following error

Service Broker in Database "msdb" cannot be enabled, because the ID of the master and msdb Service Broker is different.

Nt authority \ SYSTEM Logon Failed

Because the Service Broker technology is not used, the error "Service Broker in Database" msdb "cannot be enabled" can be ignored.

Nt authority \ SYSTEM logon failed, as mentioned in the introduction to Service SID in SQL Server 2008

Copy codeThe Code is as follows:
However, we cannot assume that accounts with these SID numbers can delete other logins, such as nt authority \ SYSTEM.

Some applications, such as VSS Writer and Windows automatic updates, still need to be processed using the service startup account.

If the customer backs up the SQL Server System database

I will do this:

Step 1: First back up the master database in my laptop

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

Step 3: Restore the customer's master. bak to your SQL Server

Step 4: Stop the SQL Service and copy master. mdf and mastlog. ldf to send them to the customer.

Step 5: Change the master database master. mdf file on the customer's computer to master22.mdf, and change the mastlog. ldf file to mastlog22.ldf.

Step 6: Put the sent master database in the data folder.

Step 7: START SQLSERVER

Summary

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 master and msdb databases.
We did not back up the system database when deploying the business system at that time, which is actually a negative teaching material.
My method is to use SQL server functions based on our business system, and only the maintenance plan is available, so the above solution is made.
If many SQLSERVER related technologies are used, maybe my solution is not the best.
I also hope that you can give your comments and opinions, or your use is better and faster than mine.