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

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.

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 of this backup and restore in SQL Server guide.

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

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

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.

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;
GO
EXEC msdb.dbo.sp_add_jobschedule @job_name='BackupDatabaseJob',
                                  @name='BackupDatabaseSchedule',
                                  @freq_type=4, -- Daily
                                  @freq_interval=1, -- Every day
                                  @active_start_time=0; -- Start time (midnight)

GO

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;
GO
EXEC msdb.dbo.sp_update_job @job_name='BackupDatabaseJob', @enabled=1;
GO

Backup of SQL Server Database With Transact-SQL

• Create Full SQL Backup to Disk

BACKUP DATABASE databasename
TO DISK = 'filepath'
GO

• Create Full Backup to Multiple Disk Files

BACKUP DATABASE AdventureWorks
TO DISK ='C:\AdventureWorks_1.BAK',
DISK ='D:\AdventureWorks_2.BAK',
DISK ='E:\AdventureWorks_3.BAK'
GO

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.

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

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

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.

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

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

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

RESTORE DATABASE databasename
FROM DISK = 'filepath'
GO

• How to Restore SQL Database from Backup With NORECOVERY

RESTORE DATABASE databasename
FROM DISK = 'filepath' WITH NORECOVERY
GO

backup and restore SQL Server database 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.

Enhancing Your Guide for SQL Server Backup and Restore- Tips

To enhance the experience of the SQL database backup and restore process, simply move ahead and just follow the tips mentioned below:

Advanced Strategies:

• Backup Compression – Make strategies to compress backup files & save storage spaces. This way, managing backup and database administration also becomes easier than before.
• Backup Encryption – Always apply encryption to sensitive or confidential data files. This plays a major role in backup of important data and further database maintenance operations.
• Restore to a Different Server – Always keep a disaster recovery plan including detailed instructions guide to restore DB to a different server.
• Point In Time Recovery – Prepare a strategy to restore the database to a specific point in time with the help of the log backups.

Troubleshooting Errors:

• Backup Failures – Prepare ways to tackle issues like common backup backup failures and related errors.
• Restore Errors – Logical corruption and media failure errors are some major defaults where users must make a guide to enhance performance optimization.
• Performance Bottlenecks – Make unique optimization techniques to counter potential bottlenecks during the process of backup as well as restore.

Best Practices:

• Regular Testing: Regularly test the backups to make sure they will work at the time of a disaster & if they work how well they’ll perform. 
• Retention Policy: Discuss and create a retention policy for the backups to always balance the storage space & requirement of SQL backups.  
• Security Consideration: Prepare a checklist to prioritize the security of data that might include several layers of protection like encryption, firewall, etc.

Automation & Scheduling:

• SQL Agent Jobs – Provide assistance on creating agent jobs & configuring them to schedule auto-backups seamlessly. 
• Powershell Scripting – Discuss using Powershell scripts to automate the process of backup of data files as well as restoration.

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