Restoration is a process of using backups to recover the exist database (or) to create a new database in another server.
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,
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.
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’
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.
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.
After restoring we have to verify,
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’
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, 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.
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
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.
Backup copies the filename and properties and path, while restoring we can change the paths according to new server drives using with move option.
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.
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.
Steps in restoring:
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’
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.
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.
Errors while restoring,
‘Unable to get an exclusive access on the database’ as lock is there it not allow restoration.
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.
Uncommitted data will flush as part of recovery. Once this recovery process complete then only database will start.
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.
In sql server 2012 microsoft introduced database restore enhancements
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.
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.