Database Emergency Recovery Process __java

When the database fails, record the current time, and then follow the steps below to recover.

1. Backing Up the database

Successful, restore the database using the backup file, and go to step 2nd.

Fail, turn to step 3rd.

2. Restore the database (operate on another server)

(1) DECLARE @backup_device nvarchar (128), @log_backup_device nvarchar (128)

Set @backup_device = N ' F:/database/mydb.bak '

Restore Database MyDb

From disk = @backup_device

With REPLACE, file=5

(2) Restore to a specific point in time according to the transaction log

--First start a transaction log restore based on a full backup point, note [NORECOVERY] parameter

RESTORE database (database name) from full backup device name with Replace,norecovery,

Move ' MyDb ' to ' d:/fmydb_0606.mdf ',

Move ' fmydb_log ' to ' d:/fmydb_log_0606.ldf '

--Start transaction log

A. View transaction log points: RESTORE headeronly from disk= file full path of backup of transaction log or backup device name '

B. Commencement of recovery

Restore log database name from transaction log backup device name with file=1, NORECOVERY

Restore log database name from transaction log backup device name with file=2, NORECOVERY

Restore log database name from transaction log backup device name with File=3,norecovery--penultimate backup log

Restore log database name from transaction log backup device name with File=4,recovery--Last backup log

If execution fails, go to the next step.

3. Copy database MDB, LDF file

Execute net stop MSSQLSERVER stop SQL Server service, copy Database MDB, ldf file.

Successfully copied 2 files already other filegroups, go to step 4th.

You can only copy MDB files and go to step 5th.

You can only copy LDF files and go to step 8th.

4. Additional database (database file and log file)

CREATE DATABASE [MYDB2] On

(FILENAME = N ' F:/back/mydb.mdf ')

, (FILENAME = N ' F:/back/mydb_log.ldf ')

For ATTACH

If a failure occurs, go to step 5th.

5. Additional databases (only database files)

CREATE DATABASE [MYDB2] On

(FILENAME = N ' F:/back/mydb.mdf ')

For Attach_rebuild_log

If it fails, you can try it under SQL2000 (2005 version does not have this feature)

DBCC REBUILD_LOG (' MyDb ', ' f:/mydb_log.ldf ')

Go to step 6th.

When the server fails due to a hardware failure, the following error may occur, and then step 6th:

File activation failed. The physical file name ' F:/database/mydb/mydb_log.ldf ' may not be correct.

The log could not be rebuilt because the database was not completely shut down.

6. Force additional databases

A create a new database with the same name

CREATE DATABASE [Mydb_temp]

On (name=mydb_temp, FILENAME = N ' F:/temp/mydb.mdf ')

LOG on (name=mydb_temp _log, FILENAME = N ' F:/temp/mydb_log.ldf ')

(b) Stop SQL Server

(c) Overwrite the backed up database F:/temp/mydb.mdf

d Start SQL Server, (Mydb_temp will become unavailable)

View database Status

SELECT * from SYS. DATABASES

SELECT * from Sys.database_recovery_status

(e) Conversion of the database into an emergency recovery model

ALTER DATABASE MyDb SET Emergency

f) Repair of the database

DBCC CheckDB (' MyDb ')

(g) Change the database to but user mode

ALTER DATABASE MyDb SET single_user

h) Re-fix the database with parameters

DBCC CheckDB (' MyDb ', repair_rebuild)

i) change the database to normal mode

ALTER DATABASE MyDb SET ONLINE

If you cannot create a database log, step I will fail, and then if you can see the database table and select it, go to step 7th.

7. Extract Database Data

A to create a new staging database

b) Export Data

Execute SELECT ' SELECT * Into MyDb2 ... ' +name+ ' from ' + name from sys.objects WHERE TYPE = ' U ' and name!= ' Sysdiagrams '

Generate data export statements, execute them, and copy data to a new database

c) Export stored procedures: to Be continued

d) export triggers: To Be continued

e) Export function: To be continued

f) Export custom data types: To Be Continued

g) Export users: to be Continued

h) Restore the new database to the target server

Backup/restore This new database to the target server

i) Repair orphaned users (SQL 2005 version)

A) EXEC sp_change_users_login ' Auto_Fix ', ' gameserver ';

(b) EXEC sp_change_users_login ' Auto_Fix ', ' BackOffice ';

(c) EXEC sp_change_users_login ' Auto_Fix ', ' adminsoft ';

J Repair the database owner

EXEC sp_changedbowner ' sa '

Summary: or regular backup good ...

-The end-