How to Backup And Restore Database In SQL Server? Step-By-Step Tutorial

Written By Ashwani Tiwari
Anuraag Singh
Approved By Anuraag Singh
Published On March 4th, 2024
Reading Time 11 Minutes Reading

backup & restore database in sql server

SQL Server is a widely used relational database management system. It is used to store and retrieve data in many large organizations. Microsoft SQL Server recommends that users periodically back up the database to prevent data loss or corruption. In this blog, we will describe the whole process of how to backup and restore database in SQL Server 2017, 2016, 2016, 2014, 2012, 2008, etc. Continue reading……..

MS SQL Server supports three files, Primary Database File (MDF), Secondary Database File (NDF), and Log File (LDF). Here, step-by-step, we will create SQL Server backup and restore with SSMS and the Transact-SQL command. Follow this guide to the end for learning how to backup database in SQL Server without any hassles at all.

Types of Backup in SQL

Well, generally there are three types of backups in SQL Server. Evidently, it’s better for users to go through them one to backup and restore SQL Server database effortlessly. These are:

  • Full Backup: This backup is the one that holds all of the information inside a database. Every bit of it in depth from scratch. This does not require any previous database backups. Moreover, it takes time as it backs everything from the start.
  • Differential Backup: As the name says, it stores the data that is the difference between the last backup and current database. However, it requires a full database backup already in place to modify it. It is generally faster & uses less storage due to the sole purpose of filling the gaps.
  • Transaction Log Backup: The transaction log backup is generally the one that stores all the transaction data. We can say that it stores the LDF files. Although this is not at all the primary database, it is still a crucial part of the SQL backup.

Now, before we move ahead further, it is quite significant for businesses to just go through the user query that most users are concerned about. Also, the answers of some of the most commonly asked queries will be mentioned in the FAQ section at the bottom.

User Query

Need & Benefits of SQL Server Backup and Restore Database Operation

There is a critical need/needs or we can say the significance of undergoing such complicated-looking procedures by users. It’s better to have a look at this before we dive into the procedure.

A backup of the database helps users in several cases like:

  • Disaster management
  • Hardware Failure
  • Data Corruption
  • Data Migration
  • Virus Attack

Moreover, along with the needs, one should also be updated about the benefits for the same. First of all, it can protect users from all of the potential threats. Secondly, creating a backup is always best to recover data in case of data corruption issues. As mentioned previously, SQL Server always recommends that users backup their database and there are two methods available for this provided by Microsoft. First, SQL Server management studio and second T-SQL command. If you have a backup of your database, you can easily restore the .bak file in SQL Server. Overall backup and restore is the best option to protect data from damage.

Also Read: Restore Database from Snapshot without Any Errors

How to Backup Microsoft SQL Server Database?

Here, we will discuss two methods to take backup of SQL Database using SSMS and T-SQL. Follow the steps that are mentioned in the below section.

Backup SQL Database With SQL Server Management Studio

Step 1. Open SSMS and connect to the SQL Server

Step 2. Expand Databases and select the required database

Step 3. Right click on the database >> Tasks >> Backup

Backup SQL Database

Step 4. In Back Up Database window, select the Backup Type as Full and under Destination, select Back up to : Disk

Backup SQL Database

Step 5. Select the Remove button

Step 6. Click on Add button to select the destination and name for the database backup file

Step 7. Select the required folder for the backup file and enter the file name with a .bak extension

Step 8. Click OK to end the backup process.

How Can We Schedule a Backup of SQL Server in Easy Steps?

Oftentimes, we come across situations when we do want to take the backup but aren’t available to do so at the right time. Therefore, in such cases, what we can really do is just schedule our backup & it’ll be executed on its own.

Yes, we can perform SQL backup and restore automatically by scheduling it for a specific period of time. Let’s understand the process to schedule:

Here, users need to just follow the below steps:

Step-1. Open SSMS >> Go to Script >> Script Action to Job Option.

schedule SQL Server backup and restore

Step-2. Under the new Job, navigate toward the Schedule pages & Hit New.

Step-3. Now, first create a backup job as required by the users.

Step-4. Here, simply schedule the job using the command mentioned below:

USE msdb;
EXEC msdb.dbo.sp_add_jobschedule @job_name='BackupDatabaseJob',
                                 @freq_type=4, -- Daily
                                 @freq_interval=1, -- Every day
                                  @active_start_time=0; -- Start time (midnight)


Step-5.  The very final step for users is to enable this scheduled job to run as needed. The command of scheduling SQL Server backup and restore is:

​​USE msdb;
EXEC msdb.dbo.sp_update_job @job_name='BackupDatabaseJob', @enabled=1;

Backup of SQL Server Database With Transact-SQL

  • Create Full SQL Backup to Disk

BACKUP DATABASE databasename
TO DISK = 'filepath'

  • Create Full Backup to Multiple Disk Files

TO DISK ='C:\AdventureWorks_1.BAK',
DISK ='D:\AdventureWorks_2.BAK',
DISK ='E:\AdventureWorks_3.BAK'

This is the complete process of creating SQL Server Database backup. Next, let’s discuss the process to restore .bak file in SQL Server by which you can learn how to backup and restore database in SQL Server easily.

How to Create a Differential Backup Rather than A Full Backup

Now, it’s very easy to create a differential backup instead of a Full backup. Also, in case users do have a backup of the SQL database but it’s not updated, they can opt for the differential backup. Moreover, The procedure for this is almost the same as as of the Full backup shown above with one minor change.

To know how to backup and restore in SQL Server specially in differential mode, follow the below steps:

Step-1. Make sure that there is a full backup already present.

Step-2. Right Click Database >> Tasks >> Back Up

Step-3. Under the Backup type option, Select “Differential” instead of Full as shown in the image.

differential backup

SQL Server Backup and Restore with SSMS using BAK File

With SSMS and the T-SQL command, we can easily restore backup file (.bak). Follow the steps listed below to know how to restore SQL Database from backup using SSMS utility.

Step 1. Open SSMS and connect to your database

Step 2. Select the database and right click >> Tasks >> Restore >> Database

Restore SQL Database

Step 3. In the Restore Database window, select From device under Source for restore section and click the Browse (…) button.

Restore Database From BAK File

Step 4. Specify Backup window will open, set Backup media as File and click Add button for learning how to backup database in SQL Server.

Restore SQL Server Database

Step 5. Select backup file which you want to restore and click OK.

Restore .bak file

Step 6. The .bak file will be list on the Restore Database window. Click OK

Step 7. Now, click on Options from the left side, select your desired Restore options and Recovery state

Restore Database SQL server

Step 8. In the end, click OK.

How to Restore Database in SQL Server from Backup Using CMD?

Now, that we have the backup in our SQL Database, it’s time that we proceed further towards the recovery task. Therefore, in order to how to restore database in SQL Server using CMD, users have two options available. Either they can go with the Full Database recovery option or they can opt for the NORECOVERY mode. Let’s have a look at both of them. 

  • Restore Full SQL Database Backup

FROM DISK = 'filepath'

  • How to Restore SQL Database from Backup With NORECOVERY


SQL Server backup and restore process of restoring .bak file is now completed. Now, it is clear that how to create a backup and restore database in SQL Server. Sometimes, the SQL backup file is corrupted due to virus attack, sudden system shut-down and many more reasons.

In such a case, users unable to learn how to restore database in SQL Server with .bak file because there is no inbuilt utility provided by MS SQL Server for SQL Server backup and restore task. In the next section, we will discuss the solution to fix corrupted SQL Server Backup file so that the backup file is easily restored.

Execute SQL Server Backup Task Automatically

As mentioned earlier, there is no manual solution to restore. backup file which is corrupted. Therefore, to repair corrupt SQL .bak file, SQL Backup Recovery software is the best solution. This application is smoothly repairing a highly corrupted backup file with their all database objects like tables, columns, triggers, etc.

The tool allows you to repair and restore multiple .bak files at once. After recovering corrupt SQL Server backup file, it provides an option to export .bak file to the SQL Server Database.

Download Now Purchase Now

Note: If you have corrupted SQL database files, you must use SQL Recovery software. This way, you can easily restore SQL  Server database  from corrupt MDF and NDF files without backup.

Final Words

In this write-up, we have discussed how to backup and restore database in SQL Server step-by-step. Backing up and restoring SQL databases with SSMS and T-SQL is easy if the backup is in a healthy state. If the backup file is corrupted, we recommend the best and tested software that allows you to repair corrupt backup file with all database elements.

Frequently Asked Questions
Q-1. How Can I Repair SQL Server Corrupt Backup File Step by Step?

Ans. Follow the steps to Repair SQL Server Corrupt Backup Step by Step

  • Launch Tool & Click on open to Add BAK file.
  • Select the SQL Server version of the BAK file.
  • After Scanning, Click on the Recover button.
  • Select the data files you want back in server.
  • Click Export for SQL Server backup and restore.

Microsoft SQL Server does not provide any inbuilt feature or utility to recover corrupted database backup. In order to repair damaged BAK file, you need to download the appropriate tool that SysTools offers.

Q-2. How Do I View SQL BAK File without SQL Server?

Ans. If you want to open and view BAK file without SQL Server, then you need to take the help of third-party tool i.e. SQL Backup Restore tool. This software allows you to open BAK file with all database objects.

Q-3. Whats is the process of restoring a BAK file?

Ans. SQL Server provides SSMS and T-SQL command that helps you to create a backup and restore the database. Use the below command to restore the backup:
FROM DISK = ‘filepath’

Q-4. Is there any free solution to recover corrupted data from BAK file?

Ans. You can download the free version of SQL backup restore tool. This will allow you to repair some data from the corrupted file. To recover complete data, you need to purchase the tool.

Q-5. What if we need to backup the Transaction Log Files using the automated tool?

Ans: Users can go for the SQL Log Analyzer utility for managing the transaction logs of SQL Server.

Q-6. What is the major difference between backup and restore in SQL Server?

Ans. The simple solution to this question is that users need to keep two things out. Backup is the process of taking the database files out of the database & saving it to some other location. Whereas, Restore is the task of bringing that saved data file back to the database in case of any malfunction or disastrous situation. In a nutshell, backup is data going out & restore is data coming back.

Q-7. What are the database backup methods in SQL Server?

Ans. There are basically a few types of database backups available. Let’s have a look at them here to learn how to backup database in SQL Server easily:

  • Full Backup
  • Copy Backup
  • Differential Backup
  • Incremental (Delta) Backup

Q-8. Is It necessary to use T-SQL Method for this SQL database backup & Restore Procedure?

Ans: No, The T-SQL method is generally used in order to make the process autonomous. However, in case users want the ease of technicalities, they should definitely check out the automated software.


By Ashwani Tiwari

Being a Chief Technical Analyst, I am aware of the technicalities faced by the user while working with multiple technologies. So, through my blogs and articles, I love to help all the users who face various challenges while dealing with technology.