SQL Server Restoration Tutorial from Coding compiler | SQL Server DBA Tutorial – Part 4, this blog is the fourth tutorial in the series, here you are going to learn about how SQL server restoration works. Before going to learn this tutorial, we recommend you to go through the previous SQL Server Backups DBA Tutorial – Part 3.
Topics Covered in this SQL Server DBA Tutorial
- SQL Server Restoration
- Situations for Restoration
- Restoring Backups using SSMS (GUI)
- Restoring Enhancements in SQL Server 2012
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
- Planned restoration
- 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,
- Pre restoration
- Actual restoration
- 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.
- Schema initialization database structure will be created.
- Performs data copy into structure from backup.
- Performs recovery process on the database files.
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,
- Size of the database
- Who is the owner
- 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
- 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
- With replace
- With move
- 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.
Restore database databasename from disk = ‘path’ with replace
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.
- 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.
- On destination, if we don’t have space on default backup path then we use option.
- As per new database server requirement.
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.
- Whenever any client/user ask to restore data at particular time we use this option.
- Whenever any data deleted accidentally, we have to restore backup before 1 minute to recover the deleted data from existing backup.
Steps in restoring:
- Restore recent full and differential with no recovery
- Restore all T-log backup in same sequential order
- 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.
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
- 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.
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
We can execute DBCC CheckDB with physical – only command against
Related SQL Server Tutorials and Interview Questions