Restoring a Database sample

One: There are several ways of database backup, there are several ways to restore the database

1: Restore of full backups

2: Restore of differential backups (you need to restore the most recent full backup, then restore the most recent differential backup, and if you have a transaction log backup after a differential backup, you should also restore the transaction log backup)

3: Restore of the transaction log (you need to restore the most recent full backup, then restore the most recent differential backup, and then restore all transaction log backups after the most recent differential backup)

4: Restore of data files and filegroups (usually only one file or filegroup in a database is damaged)

Second: Things to be aware of before restoring a database

1: Check the backup file box to restore is valid (Restore VERIFYONLY from disk= ' or restore VERIFYONLY from backup device name)

2: View the usage status of the database

If someone else is using it at this point, you cannot restore

Third: Restore DATABASE commands

1: Restore the database with the backup device (restore full backup)

RESTORE database name

From backup device name

-Note: If you do not specify which backup device set to use to restore, the default is to restore with the first backup set. If you want to specify, you need to use the with file parameter

Such as:

RESTORE database name

From backup device name

With File=n;

2: Restore the database with the backup file (restore full backup)

RESTORE database name

From disk= ' backup file path name '

3: Restore Differential backups

Needs to be done in two steps.

The first step is to restore the full backup,

The second step is to restore the differential backup.

In addition to the last restore operation, other actions must be norecovery or standby parameters

4: Restore Transaction log Backups

Three-step operation is required.

The first step is to restore the full backup,

The second step is to restore the differential backup,

The third step is to restore all transaction log backups.

In addition to the last restore operation, other actions must be norecovery or standby parameters.

Note: The final restore transaction log backup can be the Restoredatabase database name from backup device name with File=n.database can also be replaced with log.

5: Restore file and filegroup backups

You must add file or filegroup between the database name and from to specify the files or filegroups that you want to restore.

Typically, after you restore a file and filegroup backup, you also restore additional transaction log backups to obtain the most recent database state.

Such as:

RESTORE Database Database name file\filegroup= '

From backup device

--All transaction log backups after backup

RESTORE LOG Database name

From backup device

6: Restore the data to a point in time

If the database, at 8 o'clock in the morning made a full backup, 10 points to do a transaction log backup, now found that the 9:15 data update when the error, then can restore to the 9:14 when the state of the database, or can only restore to 10 points to do when the transaction log backup state.

The role of the transaction log is to record each modification of the database so that it can revert to the state before any one operation. The 9:15 record is wrong, and it can be restored to the 9-point 14 o'clock data.

Four: Specific backup restore example

PS: There are several reduction examples below, and are consistent, please look at the order, easy to understand.

For example: I made a full backup of the test library in 2013-12-12 13:34:13.000 (

RESTORE headeronly from my backup device 1 inside Backupstarttime can see time here.

）。

Use TEST;
SELECT *
From T2;

Now the table data is empty, as follows:

Insert two data into table

INSERT into T2 (ID2) VALUES (1), (2);

Query results:

At this point, make a differential backup

Backup DATABASE TEST to my back-up device 1 with differential;

Next, do an operation to clear the T2 data.

DELETE
From T2;

Now the table data is empty, as follows:

Then do a transaction log backup

Backup LOG TEST to my back-up device 1

Insert two more records

INSERT into T2 (ID2) VALUES (3), (4);

SELECT *
From T2;

Now, do one more transaction log backup

Backup LOG TEST to my back-up device 1

How to recover the two data that had been deleted before?

Recovery steps:

1: View backup Set information

RESTORE headeronly from my backup device 1

See the full backup number you just made is 8, the differential backup number is 9. Two transaction log backups are 10,1

2: Check whether two backup sets are valid

RESTORE verifyonly
From my backup device 1
With File=8

RESTORE verifyonly
From my backup device 1
With File=9

3: Restore full backup

Use master;
RESTORE DATABASE TEST
From my backup device 1
With File=8,norecovery,replace

4: Restore Differential backups

Use master;

RESTORE DATABASE TEST

From my backup device 1

With File=9

5: Now look again

Use TEST;
SELECT *
From T2;

Come back, quack ...

If, I now again want to return to 3,4 that two data, what should do?

Then you need to restore the transaction log backups.

Recovery steps:

1: View backup Set information

RESTORE headeronly from my backup device 1

This step can be omitted, because the above has been checked, know that the transaction log backup number is 10, 11.

2: Check whether two backup sets are valid

RESTORE verifyonly
From my backup device 1
With file=11

RESTORE verifyonly
From my backup device 1
With file=10

3: Restore full backup

Use master;
RESTORE DATABASE TEST
From my backup device 1
With File=8,norecovery,replace

4: Restore Differential backups

Use master;

RESTORE DATABASE TEST

From my backup device 1

With File=9

5: Restore Transaction log Backups

RESTORE DATABASE Test
From my backup device 1
With File=10,norecovery

RESTORE DATABASE Test
From my backup device 1
With file=11

Now check it out and come back.
Use TEST;
SELECT *
From T2;

--------------test to restore the database to a certain point in time----------

I made the following operation in 2013-12-12 18:16.

DELETE

From T2

WHERE id2=3

And did a transaction log backup.

Backup LOG TEST to my back-up device 1

Now, I want to restore this data for ID2 3.

Steps:

1: View backup Set information

RESTORE headeronly from my backup device 1

--The transaction log backup number is 12.

2: Check that the backup set is valid (the previous one has been validated, so verify this altogether is OK)

RESTORE verifyonly
From my backup device 1
With file=12

3: Restore full backup

Use master;
RESTORE DATABASE TEST
From my backup device 1
With File=8,norecovery,replace

4: Restore Differential backups

Use master;

RESTORE DATABASE TEST

From my backup device 1

With File=9

5: Restore Transaction log Backups

RESTORE DATABASE Test
From my backup device 1
With File=10,norecovery

RESTORE DATABASE Test
From my backup device 1
With File=11,norecovery

--due to the deletion of 18:16 points, so revert to the state of 18:15 points

Use master
RESTORE DATABASE Test
From my backup device 1
with File=12,stopat = ' 2013-12-12 18:15 '

6: Now check it out for 3 of that piece of data.

Use TEST;
SELECT *
From T2;

The result is omitted ...

Five: Restore the file to a new location

If the disk fails, I want to put the test library's data files and log files in another location. Limitations and Limitations

The system administrator who restored the file must be the only person currently using the database to be restored.

RESTORE is not allowed in an explicit or implicit transaction.

In the full or bulk-logged recovery model, you must back up the active transaction log (called the tail of the log) before you can restore the file. For more information, see Backing Up the transaction log (SQL Server).

To restore an encrypted database, you must have access to the certificate or asymmetric key used to encrypt the database. If there is no certificate or asymmetric key, the database cannot be restored. Therefore, the certificate used to encrypt the database encryption key must be retained as long as the backup is required. For more information, see SQL Server certificates and asymmetric keys.

Steps:

Optionally, execute the RESTORE filelistonly statement to determine the number and name of the files in the full database backup.

Executing the RESTORE DATABASE statement restores a full backup of the databases while specifying:

The name of the database to restore.

The backup device from which to restore the full database backup.

Specify a move clause for each file you want to revert to the new location.

NORECOVERY clause.

If the file was modified after the file backup was created, the RESTORE log statement is executed to apply the transaction log backup, specifying the following:

The name of the database to which the transaction log will be applied.

The backup device for the transaction log backup to be restored.

Specifies the NORECOVERY clause if additional transaction log backups are applied after the current transaction log backup is applied, otherwise the RECOVERY clause is specified.

Transaction log backups (if applied) must contain the time when files and filegroups are backed up.

Example:

Restore database test to location: d:\ Danny \my SQL server\ data files and log files

1:restore filelistonly from my backup device 1

2:restore headeronly from my backup device 1

You need to 8,9,10,11,12 these backups when you see the restore.

3:

Use master
RESTORE Database test--Note: This can also be renamed for databases
From my backup device 1
With File=8,norecovery,replace,
Move ' test ' to ' d:\ Danny \my SQL server\ data file and log file \test01.mdf ',
Move ' test_log ' to ' d:\ Danny \my SQL server\ data file and log file \test_log01.ldf '

RESTORE DATABASE Test
From my backup device 1
With File=9,norecovery

RESTORE DATABASE Test
From my backup device 1
With File=10,norecovery

RESTORE DATABASE Test
From my backup device 1
With File=11,norecovery

RESTORE DATABASE Test
From my backup device 1
With file=12

4: Verify that the new location is moved

Use TEST;
SELECT *
From Sysfiles;

5: Then make a full preparation for the current library

Backup DATABASE TEST to my back-up device 1