SQL FAQ: How do I use logs to restore SQL databases?

Data | database | questions

Restore with Log Explorer, if Log Explorer is not available for point-in-time recovery

Will lose all database changes after this point in time

Example
Log backups of the database first

In Query Analyzer
1.use Northwind
Insert Employees (Lastname,firstname) VALUES (' AAA ', ' BF ')
Make a note of this time

2. After 3 minutes of input
Use Northwind
Insert Employees (Lastname,firstname) VALUES (' BBB ', ' CD ')

3. At this point select * FROM employees will be able to see these two records

4. Make log backup, select Northwind database, all Tasks, backup database
In the dialog box, select the transaction log backup as the backup type, select an existing backup device, and append to the media
Click OK to make a backup. This generates two new records of log backups

5. The following demo restores
All Tasks Restore Database
dialog box, select a point-in-time restore, enter the time just recorded, click OK to restore

6. Perform select * FROM employees, only the first newly inserted record can be seen at this time

How to recover from a corrupted database file (log file intact)

How to recover when a database file is corrupted
1 Build a test database testing (database type is complete)
2 Build a table, insert point record
CREATE TABLE A (C1 varchar (2))
Go
Insert into a values (' AA ')
Go
Insert into a values (' BB ')
Go
3 for full backup, to file Test_1.bak
4 Making a little change
Insert into a values (' CC ')
Go
CREATE TABLE B (C1 int)
Go
Insert into B values (1)
Go
Insert into B values (2)
Go
5 Shutdown Database Server
6 Edit Database file Test_data.mdf with UltraEdit, modify dot byte content casually
7 Start the database, and run Enterprise Manager, point to open the database, see test turned gray, and display
Suspected.
8 Running Isql-slocalhost-usa-p
1> BACKUP LOG test to disk= ' D:\Program Files\Microsoft SQL
Server\mssql\backup\t
Est_2.bak ' with No_truncate
2>go
Processed 2 pages that belong to the file ' Test_log ' of the Database ' Test ' (located on file 1).
The BACKUP LOG operation successfully processed 2 pages and took 0.111 seconds (0.087 Mb/sec).

9 to restore the oldest full backup
1> RESTORE DATABASE test from disk= ' D:\Program Files\Microsoft SQL
Server\mssql\
Backup\test_1.bak ' with NORECOVERY
2> Go
Processed 96 pages that belong to the file ' Test_data ' of the Database ' Test ' (located on file 1).
Processed 1 pages that belong to the file ' Test_log ' of the Database ' Test ' (located on file 1).
The RESTORE DATABASE operation successfully processed 97 pages and took 0.107 seconds (7.368 mb/sec).

10 Restore the most recent log
1> RESTORE LOG test from disk= ' D:\Program Files\Microsoft SQL
Server\mssql\backu
P\test_2.bak ' with RECOVERY
2> Go
Processed 2 pages that belong to the file ' Test_log ' of the Database ' Test ' (located on file 1).
The RESTORE LOG operation successfully processed 2 pages and took 0.056 seconds (0.173 mb/sec).

The data is fully recovered and ready for use.
SELECT * from a
Go

SELECT * from b
Go
C1
----
Aa
Bb
Cc

There's an old backup.

1 BACKUP LOG db to disk= ' E:\db2.bak ' with no_truncate

2 Recovery of the oldest full backup
RESTORE DATABASE db from disk= ' E:\db1.bak ' with NORECOVERY

3 Restore the most recent log
RESTORE LOG db from disk= ' E:\db2.bak ' with RECOVERY