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.
1,477 total views, 2 views today