SQL Server disaster recovery do you understand?

1. In many cases, we accidentally delete the file, update the file, or delete the database directly. What should we do? Next, let me teach you how to learn the current two tricks.
2. What should we do when we accidentally delete a table?

Before doing this, we need to set the database recovery mode:

 

Set to complete, not in simple mode.

First, create a table: insert several data records

Create Database test1

 

Create Table A1

(

Id int primary key

)

 

Insert into DBO. A1 (ID)

Values (0)

Insert into DBO. A1 (ID)

Values (2)

Insert into DBO. A1 (ID)

Values (3)

Insert into DBO. A1 (ID)

Values (4)

Insert into DBO. A1 (ID)

Values (5)

 

Select * From DBO. A1
Now we have five pieces of data. We have a backup of the data.

 

Make a full backup before performing any differential backup or log backup.

Insert another data record.

Insert into DBO. A1

(ID)

Values (6)

 

Select * From DBO. A1 let's see that there are now six data records.

Key: Not careful 

 

What should I do if I delete one of the data? 

Don't worry about backing up quickly. After backing up the data, we start to restore it. Remember to back up the data before it is restored. What we need to do here is log backup. After the log backup is completed, data will be restored.

 

Pay attention to the time. We need to select the recovery time, that is, the moment before you delete the data, and then we can see that the six data entries are back.

 

3. We can restore the deleted table data. What if you drop the Database Service and delete the database file?

Insert data into a table as follows:

 

Now there are five pieces of data, complete backup, and insert another piece of data.

 

Now there are 6 data records for a log backup

Insert a data record.

 

Then stop the database service and then remove the database file.

 

Then put the database file in a new folder, but the log file is still there. If the log file does not exist, please bless me, this is also one of the benefits of separating data files from log files.

 

Then start the service.

 

At this time, the test1 database is in, but you will certainly report an error when you open it.

Before the restoration, we need to back up the end of the log, because the database is not available, so we need to use it.

Backup log test1 to disk = 'C: \ test1.bak 'with no_truncate;

Let's take a look at our backups.

You need to check before restoring.

Check backup integrity

Restore verifyonly from disk = 'C: \ test1.bak ';

View the number of backup files

 

Restore filelistonly from disk = 'C: \ test1.bak ';

View the number of backup records

Restore headeronly from disk = 'C: \ test1.bak ';

We will find that the number of backups here is one more record than the backup folder. Why?

This is because we have performed a log tail backup.

Let's recover the data.

 

Then we recovered a database Test2 and then queried the data.

 

Here we only have six data records, instead of seven data records. What should we do if data is lost?

The reason is: we can see that we found three backups in the backend, but only two backup file records are returned. Isn't it because the last log is missing?

No, we need to manually back up the data.

Restore database [Test2] from disk = n'c: \ test1.bak 'with file = 1, norecovery, replace

Go

Restore log [Test2] from disk = n'c: \ test1.bak 'with file = 2, norecovery

Go

Restore log [Test2] from disk = n'c: \ test1.bak 'with file = 3, norecovery

Go

 

Note:

Restore headeronly from disk = 'C: \ test1.bak ';

 

The 1 in file = 1 above corresponds to the 1 2 3 we found.

 

Ah, why is Test2 still being restored?

Oh, the last one was norecovery. We changed it to recovery.

Restore database [Test2] from disk = n'c: \ test1.bak 'with file = 1, norecovery, replace

Go

Restore log [Test2] from disk = n'c: \ test1.bak 'with file = 2, norecovery

Go

Restore log [Test2] from disk = n'c: \ test1.bak 'with file = 3, recovery

Go

Oh, there are seven data entries.

Do you have any gains here?

Finally, let's take a look at the data.

 

Is this data quite regular?

We can see that the data in the databasebackuplsn field of log backup is the checkpoitlsn data of the first full backup.

If this value is not correct, let's have a blessing. It cannot be recovered ....