Skip to main content
Our Tech Ideas

SQL Server Restoration Tutorial

SQL Server Restoration Tutorial

Restoration is a process of using backups to recover the exist database (or) to create a new database in another server.

Situations for Restoration

  1. Planned restoration
  2. Unplanned (or) Accidental restoration

Planned restoration, when we have planned activities like migration, upgradation, database refresh from prod to other servers, configuring log shipping and other high availability we use planned process in restoration. Here we will take fresh copy of backup and restore on the other server. Restorations are limited in this unplanned restoration.

In accidental restoration, restoration depends on crash time and available backups at that time. Restorations are more here.

Restoration will be done in 3 steps,

  1. Pre restoration
  2. Actual restoration
  3. Post restoration

1. Pre Restoration in SQL Server

Before restoring we need to perform certain validations like what type of backup file it is, on which version it has taken, whether existing backup will be supportable on new server (or) not.

 

To check restoration

Restore verify only from disk = E:\backup\sqltest.bak’

Restore verify allows sql server to validate database backup without actually performing the restore operation. It gives a output whether backup file is valid (or) not. If the value is 1, it means backup is valid we can go ahead and restore this backup.

Restore File List Only

File list only gives details of backup files like how many files it is going to create, what is default location of backup, size of backup  file, logical file name.

 

Using these details if we want to change path according to new server and drive we can modify.

Restore filelist only from disk = ‘E:\backup\sqltest.bak’

2. Actual Restoration in SQL Server

Restoration is the process, from the backup files we are coping the data to another server. We need to specify backup file name and location and as we are copying from data storage location specify the command “From disk”. Full backup contains Mdf file and Ldf active portion. While restoring the backup it follows 3 steps.

  1. Schema initialization database structure will be created.
  2. Performs data copy into structure from backup.
  3. Performs recovery process on the database files.

Restoration command

Restore database sql test 2014 from disk = ‘E:\backups\sqltest-full.bak’

For restoring log backups there is no separate restore command we use same command for all backups sql server identifies with same syntax. What type of backup it is restores data accordingly.

3. Post Restoration in SQL Server

After restoring we have to verify,

  1. Size of the database
  2. Who is the owner
  3. What is compactability model

We are comparing these details from source and destination with whatever login we are restoring the database it becomes owner of the new database we have to change the owner after restoration by using command

SP - change db owner ‘sa’

Restoring Backups using SSMS (GUI) in SQL Server

Right click on the database → Go to tasks → select Restore → select database

Source database: we can select the database list for copying the database

Source device:  provide the backup file path

Destination database:  what should be the name of database that is going to be create. Incase database already exist if we want overwrite select the database from dropdown.

Restore to: to stop restoration till previous time we can specify time

Restore options,

  • Overwrite existing database
  • Preserve replication setting
  • Prompt before each backup
  • Restrict access to restored database

Overwrite, it replaces old data with new backup data preserve replication. If we enable this option along with user tables some replication objects will automatically come with backup.

Prompt before backup, when one backup completes we get notification using this option.

Restrict access, to provide high security while restoring we enable this.

SQL Server Restoration Options

  1. With replace
  2. With move
  3. With stop-at

1. With Replace – SQL Server Restoration Option

There is a database on the server, if we want replace the data on existing database we use this option. It will delete on existing database and copies a fresh copy of data on existing database.

Through command

Restore database databasename from disk = ‘path’ with replace

Through GUI

Right click on database → Tasks → select restore under database → go to options

  • Select overwrite the existing database

This overwrite the old data with new backup data when ever we want to replace old data we can use replace option and when ever existing Mdf and Ldf not working properly due to some issues here also we can use replace old one with new backup data.

2. With Move – SQL Server Restoration Option

Backup copies the filename and properties and path, while restoring we can change the paths according to new server drives using with move option.

Situations:

  1. On source server drive letters are different and on destination server drive letters are different, in this we change with move option and specifies drives according to new server.
  2. On destination, if we don’t have space on default backup path then we use option.
  3. As per new database server requirement.

Using command

RESTORE DATABASE databasename
FROM DISK = 'path'
WITH MOVE 'logicalname' TO 'path',
MOVE 'logical name-log' TO 'path';

Restore database databasename from disk = ‘path’

With move ‘logicalname’ to ‘path’

Move ‘logical name-log’ to path

When ever we use with move while restoring, first need to collect information of logical name and number of files in restoration using file list only command.

From GUI – Change default path in options provide new paths.

3. With Stop-at – SQL Server Restoration Option

This we call as point-in-time restoration.

Whenever we want to restore the backup at particular time only. It can used only for log backups.

Situations:

  1. Whenever any client/user ask to restore data at particular time we use this option.
  2. Whenever any data deleted accidentally, we have to restore backup before 1 minute to recover the deleted data from existing backup.

Steps in restoring:

  1. Restore recent full and differential with no recovery
  2. Restore all T-log backup in same sequential order
  3. Restore last T-log with ‘stop-at’ clause

Using LSN and timestamp we are specifying the restoration to stop before deletion (or) specific data and time. It validates timestamp whenever it reaches stop-at time it stops restoration, the remaining part of the backup will be ignored.

Through command

Restore log dbname from disk = “path” with stop-at ‘timestamp’

SQL Server Restoration Situations

SQL Server Restoration Situation – 1

If we forget to keep recovery option with last restoration what will happen, Database will never come online. If we forget to keep last log back recovery option. We have to use recovery command to bring database online restore database sql test 2012 with recovery.

SQL Server Restoration Situation – 2

If we lost recent differential backup. How will you recover we can restore the data with full and followed transaction log backups. Differential will not impact LSN chain number of restorations will be minimize using differential.

SQL Server Restoration Situation – 3

Errors while restoring,

  • Whenever insufficient disk space restoration fails
  • Operating system 302 error when drive (or) path not exist
  • Access denied error when we don’t have sufficient permissions while restoration.
  • Restoration terminated abnormally with error:

‘Unable to get an exclusive access on the database’ as lock is there it not allow restoration.

States and Steps of Restoration

  • While restoring it perform copying the structure of backups (MDF and LDF file structure)
  • Dataload, copies data into backup file structure.
  • Performs recovery
    1. Roll forward
    2. Roll back

SQL Server Roll Forward

Full backup contains active LDF portion while restore it copies the data, after completion while starting of database sql server performs recovery process it verifies whether any pending transactions are there on the server, those will make a move to MDF.

SQL Server Roll Back

Uncommitted data will flush as part of recovery. Once this recovery process complete then only database will start.

SQL Server No-recovery

Whenever data load is not complete we will restore backup with No-recovery option. When there is no backup left for restoration we use recovery option.

Restoring Enhancements in SQL Server 2012

In sql server 2012 microsoft introduced database restore enhancements

  1. Point-in-time
  2. Page restore

Point in time restore has now a visual timeline that allows us to select the target time and we can perform restore. Earlier we can do this only through script.

SSMS → Right click on database → select restore database

Select ‘sql server 2012’ backups available on servers now all the backups of sql server 2012 database will display in list. There is new button called ‘TIMELINE’ click on it to open timeline interface.

Choose a backup and select option specific data and time.

With new Timeline feature we can scroll to restore time that we want. We can which type of backup it is also. Once selected correct time click OK. It generates restore till requested time.

Backup Timeline

SQL Server Page Restore

We have page restore option in sql 2005 and sql 2008 also but it has new user interface. It allows us to check database for corrupt pages and restore them from a good backup file.

Right click on database → Select tasks → Restore → Page

In restore page window, the database and the pages grid will show the damaged pages by DBCC ‘check database pages’.

The repair pages grid displays records that appear in suspect pages table in msdb database.

We can execute DBCC CheckDB with physical – only command against database to populate the grid. By clicking the Add button.

Below topics further included in this series