The general processing steps and methods _mssql of the MSSQL database system after crashing

General processing steps after the collapse of MSSQL database system
Situation Description:
SQL Server 2000 crashes, reinstalling the database.
There are the following preparations:
1, full backup of three system libraries (Master,msdb,model):
22 User Database (USER01,USER02) backup (full backup in Sunday + differential backup every day except Sunday):
Three system databases are early backup, no updated account information, no increase or decrease in the job, etc.
Two user databases made full backups late Sunday (User01.bak and User02.bak)
Prepare to reinstall the database, hoping to revert to the state before the last backup of the user database.
The general recovery process is generally as follows
1. Back up available data, including data files and log files for all system databases and user databases (*.MDF/LDF/NDF)
2. Uninstall the original installation
3. system table to find and remove all MSSQLSERVER items
4. Remove all files generated by installing SQL Server on disk
5. Reinstall SQL Server, the version of the patch required to be installed is required to maintain the same patch version that was installed when backing up the system database
6. Restore master database in single user mode
7. Recovery of other system databases
8. Restore user Database
Can do the try
If the time is adequate and you want to try to recover the data to the nearest point in time, you can do the following in the steps above
1. Replace the 6 and 72 steps with the following:
A. Cessation of MSSQL services
B. Replace the file of the system database that was built after installation with the data files and log files of the system database backed up by step 1
C. Set up a repository of user databases before the crash of SQL Server system and store user database files in their original locations
D. Initiating MSSQL services
E. If the MSSQL service is successful, see if the user database is questioned in enterprise management, and if there is no doubt, no other operation is necessary, the data has been restored
Attention:
Before you do step B above, back up the files that you prepared to overwrite
2. If the attempt to step 1 is unsuccessful, try the following to modify step 8 to the following:
A. Cessation of MSSQL services
B. Restoring overwritten files with backed up files
C. Try to restore the user database in an additional way
D. If successful, fix orphaned users in each user database
The recovery process will involve some specific processing
1. Restore the System database:
In a SQL Server database, System information is stored in the system database, and the primary system databases include:
master-controls user databases and SQL Server operations as a whole, and backs up any user-defined objects after they are created
Model-provides templates and prototypes for new databases
msdb-contains information on operations, alarms and operators.
If the media containing the system database has changed, the system database must be rebuilt, and if you can still start the SQL Server service, you can recover the database from the backup of the system database through the RESTORE statement.
If Master does not boot the system, you can follow the steps below to restore
1. Rebuild the system database run C:\mssql7\binn\rebuildm.exe, follow the prompts,
The path of the system database sample is required in the process, which can be found in the installation CD;
2 after rebuilding the system database, start the SQL Server service and restore the database with the system database backup
Usually the recovery sequence is Master->msdb->model.
When restoring Master's backup, be aware that it must be done in single user mode
Ways to enter Single-user mode:
A. Enter in command line mode: sqlservr-c-f-m or input sqlservr-m
Where:-C can shorten startup time, SQL Server does not start as Windows NT service
-F Start SQL Server with minimal configuration
-m single-user mode starts SQL Server
B. You can enter-c-f-m or input-m in the Control Panel-service-mssqlserver startup parameters, click Start
3. To restore the master database
A. Directly into the Query Analyzer, there is a hint to ignore it
Enter recovery statement for database recovery:
RESTORE DATABASE master from disk= ' c:\ specific backup file name '
B. Or use this to enter at a command prompt, note the case
Using Windows Authentication, enter: isql/e
Using SQL Server and Windows authentication, enter: isql/u "username"///"password"
Then enter at the prompt that appears (note that 1>,2> is a prompt):
1>restore DATABASE master from disk= ' c:\ specific backup file name '
2>go
2. Specific steps to restore the database:
1. Restore the most recent full backup
Enterprise Manager--right--"database"--All tasks--Restoring a database
--"Restore to Database Library" Enter the restored database name, set to: Test
---Restore Select "From Device"--Select device--add--Add your backup file
--OK, back to the database restore interface
--"Restore Backup Set", select "Database-Full"
--option--modifies the physical file name in "Move to physical file name" to the file name to be stored in your data file
--If the database you want to restore already exists, select Force Restore on existing database
--"Restore completion status", select "Make database no longer running, but can restore additional transaction log"
-Determine
--or with SQL statements:
RESTORE Database Database name
From DISK = ' C:\ your full backup filename '
With NORECOVERY
2. After restoring a full backup, the most recent differential backup (if any)
Enterprise Manager--right--"database"--All tasks--Restoring a database
--"Restore to Database Library" Select Database name: Test
---Restore Select "From Device"--Select device--add--Add your backup file
--OK, back to the database restore interface
--"Restore Backup Set", select "Database-diff"
--"Restore completion status", select "Make database no longer running, but can restore additional transaction log"
-Determine
--or with SQL statements:
--or with SQL statements:
RESTORE Database Database name
From DISK = ' C:\ your differential backup filename '
With NORECOVERY
3. All log backups, in chronological order, after the differential backup is restored (a full backup if there are no differential backups)
Enterprise Manager--right--"database"--All tasks--Restoring a database
--"Restore to Database Library" Select Database name: Test
---Restore Select "From Device"--Select device--add--Add your backup file
--OK, back to the database restore interface
--"Restore Backup Set", select "Transaction log"
--"Restore completion status"
If you are restoring the last log file, select "Make the database continue to run but cannot restore other transaction logs"
Otherwise select "Make database no longer running, but can restore additional transaction log"
-Determine
--or with SQL statements:
RESTORE Database Database name
From DISK = ' C:\ your log file name '
With RECOVERY
3. Resolve Orphaned Users:
1. View orphaned users of a database:
Use library name
EXEC sp_change_users_login ' the "
2. Automatically fix an orphaned user:
Use library name
EXEC sp_change_users_login ' auto_fix ', ' orphaned username ', NULL, ' password '
--password refers to the user's corresponding login does not exist, the system automatically set up logins, the password assigned to the login