Hello friends, in my last post, we learn about different
types of database backups available in SQL Server 2008 and also saw how we can
restore a full backup of database. Today we are going to learn how we can
restore a differential backup and transaction log backup.
Differential Backup:
- First of all we will see how we can take differential backup. To take
differential backup we will follow below mentioned steps.
Click on “Back Up” menu that will bring a new screen as
shown in the below image.
In backup type combo list, we will select “Differential” and
click on “Add” button (encircled with blue color) as shown in the above image.
As soon as we click on “Add” button a new screen will appear(as shown in the
below image).
Click on the button (encircled with blue color in the above
image) that will further bring a new screen as shown in the below image.
We will locate the backup location where we want to take the
differential backup. Click on “OK” button that will close the current screen
and bring us back to the earlier screen. Click “OK” to close the screen current
screen and then click “OK” to take the backup.
We are done with the differential backup, now we can take
transaction log back up by following the same steps, except one change i.e. In
backup type combo box(as shown in the above image), we need to select
“transaction log” instead of
“differential”.
Restoring backups:- I
have three backups with me one is full backup, second is differential backup
and third is transaction log backup of same database “ETL” and backup was taken
in the below order.
Full backup-->Differential Backup--> Transaction Log Backup
To obtain a latest database copy, we need to restore all the
above databases in the same sequence in which the backup was taken.
First of all we will restore the full backup, as I have
already explained in my earlier blog, how we can restore a full backup, hence
not going to explain again. In the current situation, we have 3 backups to be
restored, hence we need to specify “Norecovery” clause while restoring the
first two database backups.
NORECOVERY:-
Option tells SQL Server that more database backups are going to be restored ,
this options keeps the database offline until all the backups are restored.
This option keeps the database consistent.
Below is the example of full database restoration with
“NoRecovery” Option. While restoring the full backup, we will select the
“Options” tab(indicated with red arrow),
which will bring the below mentioned screen. By default, database is restored
with “ Restore with recovery” option, But if we have multiple database to be
restored, we will select “Restore with norecovery” option as shown in the below
image.
Once will click on “OK” button, our full database will be
restored and will look like(as shown in the below image).
You can see in the above image that database is showing in
restoring mode and this database will not be available for use. Now we will restore
differential back, we will select “ETLRESTORE” database-->Right click-->Choose restore option--> and follow all the
steps those were taken during full database restore.
Since we have another database backup i.e. transaction log
backup after this database, we will restore differential backup with
“NORECOVERY” option.
Now we will restore the transaction log backup, but this
time we will choose “Recovery” option as we do not have any other backup set to
restore.
Click on “Transaction log” and follow the steps those we
followed in full and differential backup. We will locate the transaction log
backup file to restore the backup as shown in the below image.
Below is the screen
shot of successful restoration of transaction log backup.
Note:- We should
keep in mind while restoring multiple database backups that “Norecovery” option
should be selected while restoring the database backups and last backup should
be restored with “Recovery” option to
make the database ready for use.
No comments:
Post a Comment