How To Backup And Restore MS SQL from the command line
How to Backup and Restore MS SQL
This tech doc refers to the command line process of backing up and restoring SQL databases without using an embedded cloud backup service such as BOBcloud.
Databases are by far the most important of all data sets for any sized business. Databases are used to store customer details, base crucial decisions upon, and to forecast the future by analysing the trends based on the stored data.
Databases can store anything from a few gigabytes to many terabytes of data. These databases are managed by Database Management Systems (DBMS) or Relational Database Management Systems (RDBMS). There are several RDMS available, and one of the most popular and widely used RDBMS is Microsoft SQL Server. We all know how important the data is, and there is always a chance that the data can be lost because of whatever reasons. The good thing is that RDBMS provides multiple ways of backing up the data.
In this article, we will talk about different backup types, how to take data backups and how to restore them in MS SQL.
Backup Types in MS SQL Server
There are multiple data backup types available in SQL Server. and these three are the most common backup types:
- Full Backup
- Differential Backup
- Transaction log Backup
This is one of the most common types of backup in SQL Server. When Full Backup is taken it includes everything in it i.e., table data, objects, and all the transactions. This is the base of all the other backup types, so whatever type of backup you are going to use you must first take a full backup of the database. If you have taken a full backup of your data, you can restore it completely to the state when the backup was taken. It also keeps the transactional log so that the database can be recovered.
As mentioned earlier, the full backup is the base for all other backups. You must take a full backup before any other type of backup. The problem with full backup is that it consumes a lot of space and the backup files are of huge size. If the database size is large, then you may need a lot of storage space for the backup files.
Differential Backup is based on the full backup and is not totally independent. In order to take the differential backup, there must be a full backup in place that can be used as a differential backup base. Because of this, it is also known as the superset of the full backup. A Differential Backup contains all the database changes made since the last full backup.
It is smaller in size and faster in speed when compared to the full backup, therefore it requires less storage space to store the backup files. But if there are a huge number of transactions after the last full backup, then the size of the differential backup can also be immense. Differential backup does not keep backup of everything which is the reason it is fast. This keeps the database history without storing a large number of files or data. The main advantage of using differential backup is that it occupies less storage and is fast, but this advantage can be lost if the size of the backup is huge. The strategy for using the differential backup is to create backups after intervals and delete the previous unwanted backups. Another good tactic is to take the full backup again which will be used as the base for differential backup.
Transaction Log Backup
A Transactional log backup uses a different method of backup, it keeps a record of all the transactions that have been performed or occurred after the last transactional log backup took place. A Transactional log backup is useful when you want to restore the database back to some specific point or date. It maintains all the history logs in the data. For instance, there is some garbage or unwanted data inserted into the database and you want to restore the database before this point, in this case, transactional log backup is quite useful.
A Transaction Log Backup is only possible with full or bulk-logged recovery models. Like the differential backup, transactional log backup is also based on a full backup.
To use the transactional log backup you must first take a full backup of the database and then you can use transactional log backup. This type of backup is fast and consumes less space compared to the full backup.
Creating A Backup and Restoring A Database in SQL Server
We have discussed in detail the different types of database backups, now let’s take a lot at some of the ways through which database backup can be taken and how we can restore a database in SQL Server.
There are two ways through which the database backup can be taken, first one is by using SQL Server Management Studio and the second one is by using T-SQL commands. Once you have taken a backup of the database it is quite an easy task to restore a database.
Backup SQL Database with SQL Server Management Studio
Follow the steps below to take a backup of your database using SSMS:
- Open SQL Server Management Studio and connect to the SQL Server
- Click on the databases option and expand it to see the list of available databases.
- Now select the required database.
- Right-click on the database >> Tasks >> Backup. A new popup window will appear on the screen.
- In the Back-Up Database window, select your desire database type e.g. Full.
- In the Destination section, select Backup to : Disk option.
- Click on Add button to select the destination and name for the database backup file
- Select the folder in which you want to store the backup file and enter the file name with a .bak extension.
- Click OK to complete the backup process.
Creating Database Backup Using T-SQL
You can also take the backup by using Transact SQL command.
To create a backup on a single file, use the below command:
BACKUP DATABASE “databasename”
TO DISK = “filepath”
If the size of the database is huge, you can create multiple backup files:
BACKUP DATABASE AdventureWorks
TO DISK =”filepath_1\filename.BAK”,
Restoring A Database using a BAK File with MS SQL Server
To restore your database using SSML follow the steps below:
- Open SQL Server Management Studio (SSMS).
- Connect with your MSSQL Server instance.
- Select the database and right-click on Tasks >Restore > Database
- In the opened window, select the From Device option under Source for restore section and click the Browse (…) button.
- A new Backup window will open, choose File in the backup media option, and click on Add.
- In the pop-up window, choose the backup file you want to restore and click on the OK button.
- After selecting the file, you have to select the type of restore. Click on Options on the left sidebar and select the desired Restore options and Recovery state and click OK when done.
Restoring A Backup with Transact-SQL
You can easily restore a backup with the following SQL command.
RESTORE DATABASE “Database Name”
FROM DISK = “FilePath”
An SQL Server provides different database backup options which can be used according to one requirement. The process of taking backup and restoring it is quite straight forward as we have seen in the above sections.