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

This article introduces the relevant knowledge of "the general steps and methods of dealing with the crash of mssql database system". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mssql database system crash after the general processing steps description: SQLServer2000 crash, reinstall the database. There are the following preparations: 1, full backup of three system libraries (master,msdb,model): 2 backup of two user databases (user01,user02) (full backup on Sunday + differential backup every day except Sunday): the three system databases are early backups, no account information has been updated, no jobs have been added or subtracted, and so on. The two user databases were fully backed up (user01.bak and user02.bak) last Sunday night and are ready to reinstall the database, hoping to restore the state before the last backup of the user database at one time. The regular recovery process is roughly as follows. Back up the available data, including data files and log files for all system and user databases (* .MDF / ldf/ndf) 2. Uninstall the original installation 3. The system table looks up and deletes all MSSQLServer entries 4. Delete all files generated by installing SQLSErver on disk 5. To reinstall SQLServer, the version of the patch you need to install should be the same as the version of the patch installed when backing up the system database. Restore master database in single user mode 7. Restore other system databases 8. An attempt to restore a user database if you have enough time and want to recover the data to the nearest point in time, you can try the following in the above steps. Change the two steps of 6 and 7 to: a. Stop the MSSQL service b. Replace the corresponding files of the system database generated after installation with the data files and log files of the system database backed up in step 1. Set up the same storage directory of the user database as before the crash of the SQLServer system, and store the user database files in the original location. Start the MSSQL service e. If the MSSQL service is successful, check the user database in the enterprise management to see if there is no doubt. If there is no doubt, there is no need to do anything else. The data has been restored. Note: before doing step b above, back up the files that are ready to be overwritten. If the attempt at step 1 is unsuccessful, make the following attempt and change step 8 to the following: a. Stop the MSSQL service b. Restore the overwritten file c. Try to restore the user database d. If successful, there are some specific processes involved in repairing the orphaned user recovery process in each user database. Restore the system database: in the SQLServer database, the system information is stored in the system database. The main system databases include: master- controls the user database and SQLServer operations as a whole, and after creating any user-defined objects, back it up. Model- provides templates for the new database and prototype msdb- contains information about jobs, alarms, operators, etc. If the media including the system database has changed. Then you must rebuild the system database, and if you can still start the SQLServer service, you can restore the database from the backup of the system database through the RESTORE statement. If the master is broken and the system cannot be started, you can follow the steps below to restore 1. To rebuild the system database, run c:\ mssql7\ binn\ rebuildm.exe and follow the prompts. The path to the system database sample is required, which can be found on the installation CD. 2 after rebuilding the system database, start the SQLServer service and restore the database with the backup of the system database. Usually, the recovery order is master- > msdb- > model. When restoring the backup of master, we should pay attention to: the method of entering single user mode must be carried out in single user (singleuser) mode: a. Enter: sqlservr-c-f-m or sqlservr-m in command line mode, where:-c can shorten the startup time, SQLServer does not start as a service of WindowsNT-f starts SQLServer-m single-user mode with minimum configuration to start SQLServer b. You can enter-c-f-m or-m in the startup parameters of Control Panel-Services-MSSQLServer, and click start 3. Restore the master database a. Go directly to the query analyzer and have a prompt to ignore it and enter the recovery statement for database recovery: RESTOREDATABASEmasterfromdisk='c:\ specific backup file name'b. Or use this, enter at the command prompt, note the case using "windows authentication", enter: isql/E uses "sqlserver and windows authentication", enter: isql/U "user name" / P "password" and then enter at the prompt that appears (note 1 >, 2 > is the prompt): 1 > RESTOREDATABASEmasterfromdisk='c:\ specific backup file name'2 > GO 2. Specific steps to restore the database: 1. Restore the last full backup enterprise manager-- right-click "Database"-- all tasks-- restore the database-- enter the restored database name in "restore to Database" and set it as: test-restore Select "Slave device"-- Select device-- add-- add your backup file-- OK, return to the database restore interface-- "restore backup set" Select "Database-complete"-option-change the physical file name in "move to physical file name" to the file name of your data file to store-- if the database you want to restore already exists, select "Force restore on existing database"-"restore complete state", select "make the database no longer running" But can restore other transaction logs "- OK-- or use the SQL statement: RESTOREDATABASE database name FROMDISK='c:\ your full backup file name 'WITHNORECOVERY 2. After restoring the full backup, the most recent differential backup (if any) Enterprise Manager-right-click "Database"-- all tasks-- restore Database-- Select Database name in "restore to Database Library": test-restore Select "from device"-- Select device-- add-- add your backup file-- OK, return to the interface for database restore-- "restore backup set" Select "Database-difference"-"restore complete status", and select "make the database no longer running" But can restore other transaction logs "--OK-- or use the SQL statement:-- or use the SQL statement: RESTOREDATABASE database name FROMDISK='c:\ your differential backup file name 'WITHNORECOVERY 3. In chronological order, all log backups enterprise manager after restoring differential backups (or full backups if there is no differential backup)-right-click "database"-- all tasks-- restore database-- Select database name in "restore to database library": test-restore Select "from device"-- Select device-- add-- add your backup file-- OK Go back to the interface of database restore-- "restore backup set", select "transaction log"-- "restore completed status". If you are restoring the last log file, select "so that the database can continue to run." But other transaction logs cannot be restored "otherwise select" so that the database is no longer running, but other transaction logs can be restored "--OK-- or use the SQL statement: RESTOREDATABASE database name FROMDISK='c:\ your log file name 'WITHRECOVERY 3. Solve isolated users: 1. View the orphaned user of a database: USE library name EXECsp_change_users_login'Report' 2. Automatically fix an isolated user: USE library name EXECsp_change_users_login'Auto_Fix',' orphaned user name', NULL,' password'- the password means that when the corresponding login of the user does not exist, the system automatically establishes a login. The password assigned to the login, "General handling steps and methods of mssql database system crash", is introduced here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.