General steps and methods for mssql database system crash

General steps for mssql database system crash
Description:
SQL Server 2000 crashes and the database is reinstalled.
Make the following preparations:
1. Complete backup of three system databases (master, msdb, model:
2 backup of two user databases (user01, user02) (full backup on Sunday + differential backup on every day except Sunday ):
The three system databases were backed up in the early stages, without updating account information or adding or removing jobs.
Two user databases were completely backed up on the last Sunday (user01.bak and user02.bak)
Prepare to reinstall the database, hoping to restore it to the status before the last backup of the user database at a time.
The general recovery process is roughly as follows:
1. Back up available data, including data files and log files of all system databases and user databases (*. mdf/ldf/ndf)
2. Uninstall the original installation
3. Search for and delete all MSSQLServer items in the system table
4. Delete all files generated by SQL SErver Installation on the disk
5. reinstall SQL Server. The patch version to be installed must be consistent with the patch version installed when backing up the system database.
6. Recover the master database in single-user mode
7. Restore other system databases
8. Restore the user database
Possible attempts
If the time is sufficient and you want to restore the data to the nearest time point as much as possible, you can try the following in the above steps:
1. Change step 6 and Step 7:
A. Stop the MSSQL service.
B. Use the data files and log files of the system database backed up in step 1 to replace the corresponding files of the system database generated after installation.
C. Create a directory for storing the same user database as before the SQL Server system crashed, and store the user database files in the original location.
D. Start the MSSQL service.
E. If the MSSQL service is successful, check whether the user database is in doubt in the enterprise management. If there is no doubt, no other operations are required and the data has been recovered.
Note:
Before performing step B, back up the files to be overwritten.
2. If the attempt in step 1 fails, make the following attempt and Change step 8 to the following:
A. Stop the MSSQL service.
B. Use the backup file to restore the overwritten File
C. Try to restore the user database with an additional Method
D. If the operation succeeds, the isolated users in each user database will be repaired.
Specific measures involved in the recovery process
1. Restore the system database:
In SQL Server databases, system information is stored in system databases. The main system databases include:
Master-controls user database and SQL Server operations as a whole. After creating any User-Defined Object, you must back up it.
Model-provides templates and prototypes for new databases
Msdb-contains information about jobs, alarms, and operators
If the media that contains the system database changes, you must re-build the system database. If you can still start the SQL Server service, you can use the RESTORE statement to RESTORE the database from the backup of the system database.
If the master node fails and the system cannot be started, follow the steps below to restore it.
1. Rebuild the system database and run c: \ mssql7 \ binn \ rebuildm.exe. Follow the prompts to proceed,
The path of the system database sample is required during the process, which can be found on the installation disc;
2. After rebuilding the system database, start the SQL Server service and use the backup of the system database to restore the database.
The recovery sequence is usually master-> msdb-> model.
When restoring the backup of the master node, note that the backup must be performed in single user mode.
To enter the single-user mode:
A. in command line mode, enter sqlservr-c-f-m or sqlservr-m.
Among them,-c can shorten the startup time, and SQL Server is not started as a Windows NT Service.
-F use the minimum configuration to start SQL Server
-Start SQL Server in m single-user mode
B. You can enter-c-f-m or-m in the startup parameters of control panel-service-MSSQLServer, and click Start.
3. Restore the master database
A. go directly to the query analyzer and have a prompt to ignore it.
Enter the recovery statement to restore the database:
Restore database master from disk = 'C: \ specific Backup Filename'
B. You can also enter it at the command prompt. Note the case sensitivity.
For "windows Authentication", enter isql/E
For "SQL server and windows Authentication", enter the isql/U User Name "/P" password"
Then input the following prompt (Note 1>, 2> yes ):
1> restore database master from disk = 'C: \ specific backup file name'
2> GO
2. perform the following steps to restore the database:
1. Restore the last full backup
Enterprise Manager -- Right-click "Database" -- all tasks -- Restore database
-- Enter the restored database name in "Restore to Database" and set it to test.
-- Restore select "from device" -- select device -- add your backup file
-- OK. Return to the database restoration page.
-- "Restore backup set", select "database -- full"
-- Option -- change the physical file name in "move to physical file name" to the name of the data file to be stored.
-- If the database to be restored already exists, select "Force restore on the existing database"
-- "Recovery completed", select "make the database no longer run, but can restore other transaction logs"
-- OK
-- Or use an SQL statement:
Restore database name
From disk = 'C: \ your full Backup Filename'
WITH NORECOVERY
2. After the full backup is restored, the latest differential backup (if any)
Enterprise Manager -- Right-click "Database" -- all tasks -- Restore database
-- In "Restore to Database", select database name: test.
-- Restore select "from device" -- select device -- add your backup file
-- OK. Return to the database restoration page.
-- "Restore backup set", select "database -- difference"
-- "Recovery completed", select "make the database no longer run, but can restore other transaction logs"
-- OK
-- Or use an SQL statement:
-- Or use an SQL statement:
Restore database name
From disk = 'C: \ your differential Backup Filename'
WITH NORECOVERY
3. All log backups after recovery (if there is no differential backup, it is a full backup) by Time
Enterprise Manager -- Right-click "Database" -- all tasks -- Restore database
-- In "Restore to Database", select database name: test.
-- Restore select "from device" -- select device -- add your backup file
-- OK. Return to the database restoration page.
-- "Restore backup set", select "transaction log"
-- "Recovery completed"
To restore the last log file, select "enable the database to continue running, but cannot restore other transaction logs"
Otherwise, select "make the database no longer run, but restore other transaction logs"
-- OK
-- Or use an SQL statement:
Restore database name
From disk = 'C: \ your log file name'
WITH RECOVERY
3. Solve isolated users:
1. View isolated users of a database:
USE Database Name
EXEC sp_change_users_login 'report'
2. Automatic Repair of an isolated User:
USE Database Name
EXEC sp_change_users_login 'Auto _ fix', 'isolated username', NULL, 'Password'
-- Password refers to the password allocated for logon when the user's logon does not exist and the system automatically creates a logon.