SQL Server does not start up (Error 9003) solution sharing _mssql

Let's talk about the environment.

Customer Environment: WINDOWS2003 Enterprise Edition SP2 32-bit SQL2005 Enterprise Edition 32-bit SP4
Your laptop environment: Windows7 SP1 32-bit SQL2005 personal developer version 32
Computer name of my notebook computer: Joe
Computer name of client computer: HS

master database size In Client side: several MB
Business System is WinForm system
Customer's environment is a stand-alone system is not used to the domain
Network environment: Customer over there slow speed, with Remote Assistance when the comparison card
Why do I have to explain my laptop environment? Please keep your eyes on the patient.

Check

Open SQL Server Configuration Manager First, start SQL Server, and discover that SQL Server does not start up

So I turned on Windows EventLog and found the following error

Copy Code code as follows:

SQL Server error 9003:LSN Invalid (log scan number is invalid)
The log scan number (2806:120:1) passed to the log scan operation in database ' master ' 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, recreate the publication. Otherwise, if the problem causes an error during startup, restore from backup.

So I was on my computer Baidu a bit of this error
Search to this article: SQL Server error 9003:LSN Invalid (log scan number is invalid), repair of the database
The database in this article is the user database, with rebuild LOG,DBCC CHECKDB solves the problem
The sad reminder is that the master database is damaged on the client side.

To
As a good database engineer, be sure to quickly find out how to solve the current customer problem
What are the pros and cons of these methods, because the delay of one second, will result in more customer losses, the customer's business system can not operate properly, the consequences can be imagined

By the following several factors, I made a choice
Slow speed, not convenient to write SQL statements on the client's computer
Business System is WinForm system

Option: A previous project manager taught me a way to run into SQL Server that didn't start up
You can replace the master database on the client side with the master database of the SQL Server that you just installed

This approach has the following drawbacks

(1) You use the database version must be the same as the customer's
(2) will not SQLSERVER2012 master database to the customer is not
(3) server triggers, certificates, linked servers, logged-in users, etc. information will be lost

Why there are these drawbacks, you can look at the following article
Introduction to service SID in SQL Server 2008

Copy Code code as follows:

"Introduction of SERVICE Sid"
NT Service\mssql$katmai, NT Service\sqlagent$katmai and NT service\clussvc are all names corresponding to the SERVICE SID.
The introduction of service SID is to solve the security risks caused by multiple service accounts with a service account.
If IIS uses the Network Service account, other services may also use the Network Service account.
In order for IIS to be able to connect to SQL Server, we might use the Network service as login for SQL Server, but this is not safe.
Because other services can also access SQL Server if they are network service as startup accounts.
To solve this problem, in SQL Server 2008/windows Server 2008 and beyond,
We have the concept of SID, so that different services, even if the service startup account is the same, their SIDs are different.

Since the mechanism of the logged-in user has changed in SQLSERVER2008, the database version you are using is not as good as the client's.
But even if the same, there are drawbacks, the client's login user is originally the look of the following

Copy Code code as follows:

BUILTIN\Administrators
Hs\administrators
Hs\sqlserver2005msfteuser$hs$mssqlserver
Hs\sqlserver2005mssqluser$hs$mssqlserver
Hs\sqlserver2005sqlagentuser$hs$mssqlserver

But if I give my master database to a client, it's going to look like the following.
If the customer's business system is b/s system, will use IIS, or the customer's environment is a domain environment,
Logging on to SQL Server requires Windows logon authentication instead of SQL logon verification, which can be cumbersome.
And one of my own computer SQL Server is a personal developer, the customer is the enterprise version, but has done so before, found no problem

Start implementation

The malpractice is over, and it's implemented.

Step one: First, sqlserverd all user databases on my laptop,

Delete some of the login users that do not have the default login only, stop their computer's SQL service, and then copy the Master.mdf and mastlog.ldf to the customer

Step two: Use Remote Assistance to rename the master database master.mdf file in the client's computer to Master22.mdf

Mastlog.ldf file renamed to Mastlog22.ldf

And put the master database you sent in the Data folder.

Step three: Start SQL Server, because there is no screenshot, there is no screenshot, this time you can find that SQL Server successfully started

Step Fourth: Reattach our business library

Step Fifth: Delete the database user Gxhtsyrain in the original business library

Sixth step: New Login user Gxhtsyrain and map to our business library

Step Seventh: Disable the SA user

Maintenance plan not lost because it is not replacing the msdb database

Error log

I looked at the SQL ERRORLOG and found the following error

Service Broker in database ' msdb ' cannot be enabled because the ID of master and msdb Service Broker is not the same

NT Authority\System Login Failed

Because we are not using Service Broker technology, "unable to enable Service Broker in database" msdb "This error can be ignored

The NT AUTHORITY\SYSTEM login failed, as described in the Introduction to service SID in article SQL Server 2008

Copy Code code as follows:

But we also cannot think that has these several Sid's account number, can casually put other login like NT AUTHORITY\SYSTEM to delete.

Some applications, such as VSS writer,windows Automatic Updates, will need to be processed with a service startup account.

If the client has a system database backup to SQL Server

I will do this:

Step one: First back up the master database in my laptop.

Step two: Take the client side of the Master.bak to your computer

Step three: Restore the customer's master.bak to their own SQL Server

Step Fourth: Stop the SQL service and then copy the Master.mdf and mastlog.ldf to the customer

Fifth step: The client computer in the master database master.mdf file renamed to master22.mdf,mastlog.ldf file renamed Mastlog22.ldf

Step sixth: Then put the master database sent over into the data folder

Seventh step: Start SQL Server

Summarize

In fact, in addition to backing up the user database, the system database backup is also very important
We need to back up the two databases of master and msdb
We didn't have a backup system database when we deployed the business system, but it was a negative example.
My approach is based on the functionality that our business system uses to SQL Server and only the maintenance plan, so make the above solution
If you use a lot of SQL Server related technology, maybe my solution is not the best
I also hope that you can put forward your views and opinions, or you use the method than I am better, faster