The article will explain you benefits of taking backups of your SQL Server Databases and how you can restore the SQL server database.
Taking SQL server backup and later restoring it, is very essential to protect your critical SQL database. To cope up from the drastic data loss, you need to take a backup of your data on regular basis. The proper and well-planned structure to restore the data helps you to protect your data from any kind of the losses occurred due to the variety of failures. So, prepare your steps to recover the database in order to respond effectively to any sudden disaster.
SQL server allows you to take backup and to restore the data from the Windows Azure Blob Storage Service.
Benefits of back up –
1) Backing up is the only plan that can protect your data from getting lost.
2) If you have a valid backup, then you can recover the data from various failures like:
(a) User errors
(b) Media failure error
(c) Hardware failures like the damage of server or any hard disk.
(d) Natural disaster.
Strategies for backup and restore SQL database –
A well-structured backup and restore strategy will maximize the data availability and also, minimize the data loss.
1) To plan the strategy, first, divide it into two parts one for backup and another one for restore planning.
The backup plan will have the strategy to define the type and nature of backups, where and how the backups are to be stored by considering the security and the duration for backup like after what time interval the backup should be done.
2) The restore part will define who will be responsible for taking up the backup. Also, the restoring of data should be performed in the way that the availability of database can be met. It is recommended that you should keep a copy of the backup and restore data in your run book.
Designing the recovery strategy should consist of carefully planning, implementation and testing of the backup data. Testing is essential as it helps you to find out if the data restored is in the right format as you wanted.
After planning the strategy, the next step would be to actually perform it. Follow the mentioned steps to restore the database from a backup file.
Method 1 –
Step 1: Open the MS SQL Server Management Studio. Get connected to your database.
Step 2 : Select your database and Right-Click on Tasks -> Restore -> Database
Step 3: You can see the ‘Restore Database’ window on your screen. On that select ‘From Device’ which is mentioned below the ‘Specify the source and location of backup sets to restore’ and then click the button in front of that to specify the file location.
Step 4: In front of the ‘Backup media’ drop button select File. Click Add button, in order to add the backup file location.
Step 5: Select the backup file that you want to restore and click on the OK button.
That’s it, you are done!
You will get the confirmation message for restoring SQL database server successfully – ‘The restoration of database ‘SQLDatabaseName’ completed successfully.
Related: How to Import and Export the MySQL Database with phpMyAdmin?
Method 2 –
You can also restore SQL Server database from the backup by using T – SQL commands.
As T – SQL commands can be worked anywhere as in SQL Server Management Studio via sqlcmd or also, you can run it from your program that’s why T-SQL command is a universal way to restore SQL server backups.
These command lines can be used in Windows Cloud VPS and Windows Dedicated Server.
By using the following commands you can restore three types of backups: full, differential and transaction log backups.
1) Command to restore full SQL server database backup –
A full backup contains all the information that is necessary to restore your database when the backup process has finished. If the same file name exists then the backup will overwrite or else it will create a new SQL database. Let’s say your full backup is stored in D:\Exampleworks_full.bak and you want to restore it to Exampleworks database, then you need to execute following command.
RESTORE DATABASE Exampleworks FROM DISK = ‘D:\Exampleworks_full.bak’
If you want to continue with the differential or transactional log backups then you need to add NORECOVERY option. This will leave your backup process in the restoring state so that you can restore the extra differential or transaction log backups.
RESTORE DATABASE Exampleworks FROM DISK = ‘D:\Exampleworks_full.bak’ WITH NORECOVERY
2) Command to restore Differential SQL server database backup –
Differential SQL backup will contain changes in the information that has taken place since the last full backup. There is no need to store all the previous backups, as the last differential backup will contain all the changes happened till the time. Before storing the differential backup, you first need to restore the last backup with NORECOVERY option and then take the last differential backup with RECOVERY option.
RESTORE DATABASE Exampleworks FROM DISK = ‘D:\Exampleworks_full.bak’ WITH NORECOVERY
GO
RESTORE DATABASE Exampleworks FROM DISK = ‘D:\ExampleWorks_diff.dif’ WITH RECOVERY
GO
3) Command to restore Transaction Log SQL server database backup –
Transaction Log backup contains all the information that has been added between the last transaction log backup, when the backup process is completed, you need to store all the transaction log backups that are made after the last differential backup in the sequence same as that they were made. Also, log backups are restored only after you are done with full and differential backups.
RESTORE DATABASE Exampleworks FROM DISK = ‘D:\Exampleworks_full.bak’ WITH NORECOVERY
GO
RESTORE DATABASE Exampleworks FROM DISK = ‘D:\ExampleWorks_diff.dif’ WITH NORECOVERY
GO
RESTORE LOG Exampleworks FROM DISK = ‘D:\Exampleworks_log1.trn’ WITH NORECOVERY
GO
RESTORE LOG Exampleworks FROM DISK = ‘D:\Exampleworks_log2.trn’ WITH RECOVERY
GO
So these are the commands that you can run to restore your SQL database server backup.
