Restore Database using T-SQL

You can also restore your SQL Server database using T-SQL instead of SSMS.  Today I will show you how to do an easy recovery with restoring just a full backup and another scenario of restoring a full backup and any available transaction logs.

If you remember from my previous posting:  Restoring your SQL Server database , being able to restore your database is the most important task a DBA is responsible for.  Lets get started.

 

Scenario 1: Restore Full Backup

Development wants a copy of the production database so they can test some new code, they do not need the most current data, just from anytime yesterday.  We look in our backup directory and we see our full database backup from last night at 11:00pm:

WideWorldImporters_backup_2017_02_25_230001_3393655.bak

That meets their criteria for the data requested, so we will restore that.

 

In SSMS we connect to the instance that we want to restore the database to and run the following command:

restore database WideWorldImporters
from disk =’E:\Data\Backup\Backup\WideWorldImporters_backup_2017_02_25_230001_3393655.bak’

This will restore the full backup that we have on disk.  This will run with the default parameter of RECOVERY, which means “roll back should be performed after roll forward is completed for the current backup”.  Or thinking about it another way….We are done with recovery options with this database, please recover it.

You can read more about the restore database command here:  Restore Command

 

After the database is restored, we can check the database properties and see it was last backed up on 2/25/2017 at 11:01pm, which is the backup file that we used.  The date created is now 2/26/2017 which is when we restored this database.

 

Scenario 2:  Restore full backup and transaction logs

Support needs to reproduce an issue with the application with some data that got entered into the system after the last full backup.  Looking at your backup directory, you see you have transaction logs got created after the full backup and include the data that is in question.  You can restore the full backup and then apply the transaction logs and recover the required information.

First thing you need to do is restore the full backup.  Using the restore database command with the parameter NORECOVERY this time.  NORECOVERY “specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.”  Or thinking about it another way…We have more commands we want to apply to this recovery situation, do not recover the database until we are done.

restore database WideWorldImporters
from disk =’E:\Data\Backup\Backup\WideWorldImporters_backup_2017_02_25_230001_3393655.bak’
with norecovery;

 

After the full backup is restored, we can look at the database in SSMS and see that is in a “Restoring” state, which means it is still available to run recovery statements against it or it is in the middle of being restored.

 

We now need to restore the transaction logs to recover up to the last log we have available.  Running the RESTORE LOG command with the NORECOVERY parameter,  allows us to restore multiple files.  If we left off the NORECOVERY parameter, we would not be able to restore any further log files.

restore log WideWorldImporters
from disk =’E:\Data\Backup\Backup\WideWorldImporters_backup_2017_02_25_233001_1494384.trn’
with norecovery;

restore log WideWorldImporters
from disk =’E:\Data\Backup\Backup\WideWorldImporters_backup_2017_02_26_000000_4804777.trn’
with norecovery;

restore log WideWorldImporters
from disk =’E:\Data\Backup\Backup\WideWorldImporters_backup_2017_02_26_003000_9335817.trn’
with norecovery;

 

After all of the logs that we have available are restored, we now can restore the database with the RECOVERY option which finalizes the restore process.

restore database WideWorldImporters with recovery;

 

 

Looking at the databases in SSMS, we see now that the Restoring designation is no longer there and the database is now available to be used.

 

Looking at the database properties, we see that the last backup was 2/25/2017 at 11:01pm and the last log backup was 12:30am which was the last log that we applied during our recovery process.

 

Summary

This week I showed you how to restore your databases using T-SQL.  When the pressure is on and you need to restore, you will have the skills to accomplish the task!  Now go practice, practice, practice.