Database Emergency Recovery Documentation

Database Emergency Recovery documentation

Revision history

Date Date	Revision revision version	Modify Description Change Description	Author Author
2008-07-06	1.0	Formatting	Ultrasql
2009-06-14	1.1	Correction	Ultrasql

Directory

First, rebuilding and recovering the master database in SQL Server 2000

Second, Recovering the msdb database

Third, recover data to Instant point

Four, recovering data to a point of failure

Five, Roll back instant modifications with Mark Mark

First, rebuilding and recovering the master database in SQL Server 2000

1. Description:

The master database records all system-level information for the SQL Server system. It records all login accounts and system configuration settings. The master database is a database that records all other databases, including the location of the database files. The master database records the initialization information for SQL Server, and it always has a backup of the most recent master databases available.

Restore the master database because the master database is corrupted and causes SQL Server 2000 to fail to start. First, we rebuild the master database, and then we restore it through a pre-failure backup, and finally, the other production databases.

2. Requirements:

There is always a current backup available for the master database.

After you do the following, back up the master database as soon as possible.

o Create, modify, or delete a database

o Change the configuration values for a server or database

o Modify or add a login account

Do not create user objects in master. Otherwise, you must back up master more frequently.

3. steps:

1) Copy all files from the SQL Server installation CD \x86\data directory to a specified directory on your hard disk, such as C:\masterrecover. At this point the file is read-only and we need to modify it to read-write mode so that the Rebuild Master tool can operate. Select all files, right-click Properties, and remove the "Read only" tick option.

2) start rebuilding the master database and restore the earlier backup. Go to the system directory of Microsoft SQL Server\80\Tools\Binn and run Rebuild.exe.

650) this.width=650; "title=" clip_image002 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M00/57/28/wKioL1STiRbi98PlAABELeWCZCM919.jpg "border=" 0 "height="/>

3) pop up the Rebuild Master database tool interface, enter the server name and the directory where you copied the files C:\masterrecover, click "Rebuild".

650) this.width=650; "title=" clip_image004 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M02/57/2B/wKiom1STiHfhlbWGAAFE7daSJW4047.jpg "border=" 0 "height=" 372 "/>

4) you will be prompted for replication to occur and the server configuration, the last prompt is as follows:

650) this.width=650; "title=" clip_image006 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M01/57/2B/wKiom1STiHuj8i-7AAAziSjeBcc047.jpg "border=" 0 "height="/>

5) next restart the MSSQLSERVER. Right-click the SQL Server icon in the system tray and select "Mssqlserver–start". Start Enterprise Manager and browse the database. The production database was found missing. All system databases are directly copied and generated through our copy of the development directory. The master database contains only the newly installed information. The msdb database is also rebuilt, the backup devices involved, the backup sequence, and the jobs are lost. The production database and backup device are then retrieved from the backup of master and msdb.

650) this.width=650; "title=" clip_image008 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M00/57/28/wKioL1STiSDxklsbAABMb_OidE8179.jpg "border=" 0 "height=" 143 "/>

6) Exit Enterprise Manager and Query Analyzer to start SQL Server in the following single-user mode. Sqlservr.exe is located in the Mssql\Binn directory. Start the database with the –c and –m parameters. Minimizes DOS windows, but does not close.

650) this.width=650; "title=" clip_image010 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image010 "src=" http://s3.51cto.com/wyfs02/M01/57/28/wKioL1STiSCxNkTIAABDCo17vg4989.jpg "border=" 0 "height="/>

O-C Shorten start-up time

O-m start the database in single-user mode. The master database must be restored in single-user mode.

7) Open Enterprise Manager, right-click the master database and select "All Tasks" and "Restore database".

650) this.width=650; "title=" clip_image012 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image012 "src=" http://s3.51cto.com/wyfs02/M02/57/28/wKioL1STiSHSiXo6AADSDUROTa0696.jpg "border=" 0 "height="/>

8) pop up the Database recovery screen, click the "From Device" button, select the "Select Devices ..." button.

650) this.width=650; "title=" clip_image014 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image014 "src=" http://s3.51cto.com/wyfs02/M00/57/28/wKioL1STiSKibelIAAFIMZDvj48972.jpg "border=" 0 "height=" 456 "/>

9) pop-up Select Recovery Device Interface, select recover from disk, click "Add". 650) this.width=650; "title=" clip_image016 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image016 "src=" http://s3.51cto.com/wyfs02/M00/57/2B/wKiom1STiICS8zikAAEZWxbLKlc435.jpg "border=" 0 "height=" 390 "/>

) pop-up Select the recovery target interface, because the msdb database is not recovered and does not recognize any backup devices. Click "File name:" And look for the backup file for the master database.

650) this.width=650; "title=" clip_image018 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image018 "src=" http://s3.51cto.com/wyfs02/M01/57/2B/wKiom1STiICDTmzBAACscZ-O9is406.jpg "border=" 0 "height=" 249 "/>

One ) Locate the backup file that you created earlier in the master database, and click the OK button.

650) this.width=650; "title=" clip_image020 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image020 "src=" http://s3.51cto.com/wyfs02/M02/57/2B/wKiom1STiIKR_gSVAAEnbG7UYQM458.jpg "border=" 0 "height=" 405 "/>

Return to the Select Recovery target interface, click the "OK" button.
650) this.width=650; "title=" clip_image022 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image022 "src=" http://s3.51cto.com/wyfs02/M00/57/2B/wKiom1STiIOjzxIfAACpxLW-xQ8495.jpg "border=" 0 "height="/>

Return to the Select Recovery device Interface, click the "OK" button.

650) this.width=650; "title=" clip_image024 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image024 "src=" http://s3.51cto.com/wyfs02/M01/57/28/wKioL1STiSfgsODuAAETYZFSTYg669.jpg "border=" 0 "height=" 392 "/>

return to the Recovery database interface and select the Options page.

650) this.width=650; "title=" clip_image026 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image026 "src=" http://s3.51cto.com/wyfs02/M02/57/28/wKioL1STiSqSG22lAAFjcpWBQFs186.jpg "border=" 0 "height=" 456 "/>

Check the path of the data and log files to be recovered and the existing files will be overwritten. Click the "OK" button.

650) this.width=650; "title=" clip_image028 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image028 "src=" http://s3.51cto.com/wyfs02/M02/57/28/wKioL1STiSvSOajQAAHDr9mdx6c991.jpg "border=" 0 "height=" 456 "/>

you will see the recovery process interface. Until the recovery is complete, the following message pops up, which is not an error message. The master database has been successfully restored and is no longer connected in single-user mode. You will also receive a message indicating that your connection to the Enterprise Manager has been terminated. You can avoid annoying error messages only by shutting down the Enterprise Manager completely.

650) this.width=650; "title=" clip_image030 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image030 "src=" http://s3.51cto.com/wyfs02/M02/57/2B/wKiom1STiIuzjKb9AAB49mxXtSI069.jpg "border=" 0 "height="/>

Select "Mssqlserver-start" by right-clicking the system Tray MSSQLServer button.

Open Enterprise Manager, browse the database, the production database appears.

650) this.width=650; "title=" clip_image032 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image032 "src=" http://s3.51cto.com/wyfs02/M00/57/28/wKioL1STiS-AHrewAABJEKOIDWI081.jpg "border=" 0 "height=" 134 "/>

Next ) to restore the msdb database, see below. Restoring the msdb database is similar to recovering the master database, but does not require a single-user mode login. After the msdb database is restored, the next step to recover the other databases is easier because the backup devices have been restored.

Second, Recovering the msdb database

1. Description:

The msdb database is used by SQL Server Agent to dispatch alerts and jobs, and to record operators.

The msdb database stores all backup information and job information.

2. Requirements:

There is always a currently available backup of the msdb database.

During the recovery process, be sure to shut down the SQL Server Agent.

3. steps:

1) Stop the SQL Server Agent before resuming. The recovery database must have exclusive access, and for the msdb database, the SQL Server Agent destroys this exclusive access.

2) first get the file number of the restore database, connect to the master database, and through the restore HEADERONLY, find the backup file we need to recover.

RESTORE headeronly from Msdbbackupdev;

3) Connect to the master database in Query Analyzer and perform the recovery.

RESTORE DATABASE msdbfrom Msdbbackupdevwith FILE = 1;

Third, recover data to Instant point

1. Description:

Restores the database to an immediate point in time through the transaction log.

2. Requirements:

Database Select the full recovery model.

There is always a backup available that can be restored to the immediate point.

3. steps:

1) by restoring the backup of the production database via restore HEADERONLY, sometimes in multiple combinations, we choose the fastest: Full backup + log backup.

RESTORE headeronly from Prodbackupdev;

2) Restore the data to the immediate point via the STOPAT parameter.

--Restore full backup restore DATABASE proddbfrom prodbackupdevwith FILE = NORECOVERY; go--Recovery log backup restore log proddbfrom prodbackupdevwith FILE = NORECOVERY; go--Restore to Instant point restore LOG proddbfrom prodbackupdevwith FILE = RECOVERY, STOPAT = ' 2006-06-06 06:06:06 '; GO

Four, recovering to a point of failure

1. Description:

To complete the recovery of the point of failure, a tail-log backup must be completed. The log file backed up by the tail-log backup contains all the transactions for the last successful log backup to the current point of failure. If you cannot complete a tail-log backup, you can only restore the database to the point in time when the transaction log backup was last created. Changes made to the database since the last transaction log backup are lost and must be manually re-made.

A tail-log backup captures all transaction log records that have not been backed up. If the database is damaged or offline, you can try a tail-log backup. A tail-log backup succeeds only if the journal file is not corrupted and the database does not contain any bulk-logged changes. If the database contains bulk-logged changes that are to be backed up and executed during the recording interval, the tail-log backup succeeds only if all the data files are present and undamaged.

2. Requirements:

Database Select the full recovery model.

There is always a backup available when the tail-log backup fails, you can revert to the last point in time when the transaction log backup was created.

3. steps:

1) First, do a tail-log backup. Back up the transaction log with the NO_TRUNCATE clause when the production database is damaged.

--Tail log backups backup log proddbto prodbackupdevwith NAME = ' tail-log backup ', no_truncate; GO

2) Review the backup status.

RESTORE headeronly from Prodbackupdev;

3 ) back to the point of failure.

--Restore full backup restore DATABASE proddbfrom prodbackupdevwith FILE = NORECOVERY; go--Recovery log backup restore log proddbfrom prodbackupdevwith FILE = NORECOVERY; go--Restore tail log backup to the point of failure restore log proddbfrom prodbackupdevwith FILE = RECOVERY; GO

Five, Roll back instant modifications with Mark Mark

1. Description:

Revert to the immediate point by Stopatmark and stopbeforemark clauses. Allows the database to be restored to a personal transaction or before a given transaction. Production environments may rarely be used, typically in testing or development environments, but also for major modifications and operations. These restore clauses can be conveniently restored to a point in time before a transaction that did not achieve the desired effect.

STANDBY specifies a fallback file that allows the recovery effect to be undone. The STANDBY option can be used for offline restores (including partial restores), but not for online restores. Attempting to specify the STANDBY option for an online restore operation will cause the restore operation to fail. If you must upgrade the database, you are not allowed to use the STANDBY option.

The alternate file is used to retain a "copy-on-write" pre-image for pages modified during the undo process of the RESTORE with STANDBY. The standby file allows the user to access the database read-only during transaction log restore and allows the database to be used in a standby server scenario, or for a special recovery scenario where a database needs to be checked between log restore operations. After the restore with STANDBY operation is performed, the next restore operation automatically deletes the undo file. If you manually delete the standby file before the next restore operation, you must restore the entire database again. When the database is in the STANDBY state, you should treat the standby file as important as any other database file. Unlike other database files, the database engine continues to open the file only during an active restore operation.

2. Requirements:

Database Select the full recovery model.

The mark tag is used in the transaction.

There is always a backup available that can be restored to the immediate point.

3. steps:

1) assume that the test database has done a full backup and a transaction log backup.

--Full backup copy DATABASE testdbto testbackupdevwith name = ' fully backup ';--Log backups backup log testdbto testbackupdevwith name = ' Transaction Log Backup 1 ';

2) do a transactional operation by setting the mark Mark. For marked transactions, you must explicitly end with COMMIT and ROLLBACK.

INSERT Shippers (CompanyName, Phone) VALUES (' Company1 ', ' (027) 11111111 '); Goinsert Shippers (CompanyName, Phone) VALUES (' Company2 ', ' (027) 22222222 '); Gobegin TRANSACTION timetomarkwith MARK ' setmarker ' Goinsert shippers (CompanyName, Phone) VALUES (' Company3 ', ' (027) 33333333 '); Goinsert Shippers (CompanyName, Phone) VALUES (' Company4 ', ' (027) 44444444 '); Gocommit TRANSACTION Timetomarkgoinsert Shippers (CompanyName, Phone) VALUES (' Company5 ', ' (027) 55555555 '); GO

3) If the transaction operation does not achieve the desired effect, a transaction log backup is made immediately before or after the token can be restored.

--Log backups backup logs testdbto testbackupdevwith NAME = ' Transaction log backup 2 ';

4) connect to TestDB through the Query Analyzer, use EXEC sp_helpfile to decide which file to delete, delete the data file and the transaction log file.

EXEC sp_helpfile;

5) close the database.

SHUTDOWN with NOWAIT;

6) Delete the files found in step 4th.

7) Right-click the SQL Server icon in the system tray and select "SQL Server-start".

8) Find the file number we need by backing up the device.

RESTORE headeronly from Testbackupdev;

9) first two restores, then the stopbeforemark tag recovery, with the STANDBY statement to keep the database in read-only mode, but still be able to implement further transaction log recovery.

RESTORE DATABASE testdbfrom Testbackupdevwith FILE = 1, NORECOVERY; Gorestore LOG testdbfrom Testbackupdevwith FILE = 2, NORECOVERY; Gorestore LOG testdbfrom Testbackupdevwith FILE = 3,standby = ' D:\testrestore ', Stopbeforemark = ' Timetomark ';

before the data is restored to the Timetomark transaction via Stopbeforemark, let's say that we need to revert to the transaction and have the database operational after the recovery.

RESTORE LOG testdbfrom Testbackupdevwith FILE = 3,recovery,stopatmark = ' Timetomark ';

One ) Stopatmark restores all the rows that were committed during the Stopatmark transaction, but all changes after the transaction are not resumed.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1591629

Database Emergency Recovery document