;

How to Backup and Restore MSSQL Database

Try it in our public cloud & Get $5 Credit
CLAIM NOW
How to backup and restore MsSQL database

Backing up your SQL Server databases regularly is a good practice for any database administrator to prevent data loss or corruption. Backing up is the only way to protect and recover your data from many failures including, Media failure, Hardware failures, User errors and many more.

Moreover, a database backup is also useful for day-to-day administrative tasks such as, database mirroring, archiving and copying a database from one server to another.

MSSQL is the most widely used database management system for Windows-based systems developed and marketed by Microsoft. It is used to store website data like, blog and user information.

In this tutorial, we will show you how to backup and restore the MSSQL database on Windows operating system.

Prerequisites

  • A server running Windows server 2016.
  • SQL Server and SQL Server Management Studio (SSMS) installed in your system.

Create a New Database

First, we will create a new database using the SQL Server Management Studio (SSMS) for testing purposes.

Follow the below steps to create a new database:

Step 1 – Open SQL Server Management Studio (SSMS) from the Start menu as shown below:

MsSQL Management Studio - Login prompt.

Step 2 – Click on the connect button to connect with your MSSQL Server instance. You should see the following page:

MsSQL Management Studio - Home.

Step 3 – Click on the New Query button to open a new Query window as shown below:

MsSQL Management Studio - New query.

Step 4 – Type the following query in the above query window to create a new database named TESTDB and create a new table named TABLE1:

USE [master]
GO
CREATE DATABASE [TESTDB]
GO
USE [TESTDB]
GO
CREATE TABLE TABLE1 (
ID INT NOT NULL PRIMARY KEY,
a1 VARCHAR(100) NOT NULL,
dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

Next, click on the Execute button to run the query. Once the query has been executed successfully, you should see the following page:

MsSQL Management Studio - Execure query.

Take a Backup with SSMS

In this section, we will learn how to take a backup of a database named TESTDB using the Microsoft SQL Server Management Studio (SSMS).

Follow the below steps to back up the TESTDB database:

Step 1 – Launch the MSSQL Server Management Studio (SSMS) and expand the Databases as shown below:

MsSQL Management Studio - Expend databases.

Step 2 – Right-click on the TESTDB and click on the Tasks => Back Up. You should see the following page:

MsSQL Management Studio - Select databases, right click and select "back up".

Step 3 – Select all required information like, Database, Backup type, Backup path and click on the OK button. Once the backup has been created successfully, you should see the following page:

Select  all required informations and then click "OK" to schedule the backup.

Step 4 – If the backup is successfull, you get the confirmation message “The backup of database “DBTEST” completed successfully, as follow:

Database backup successfully completed confirmation pop-up.

Take a Backup with Transact-SQL

You can also take a backup of your database with Transact-SQL command from the query window.

Follow the below steps to backup your TESTDB database with Transact-SQL command:

Step 1 – Launch the Microsoft SQL Server Management Studio (SSMS).

SSMS Home.

Step 2 – Click on the New Query button to open a new Query window as shown below:

SSMS - New Query.

Step 3 – Type the following query in the above query window to take a backup of TESTDB database:

BACKUP DATABASE [TESTDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TESTDB.bak'
WITH NOFORMAT, NOINIT,
NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 4 – Click on the Execute button to create a backup.

Restore a Backup

You can also restore a backup using the SQL Server Management Studio (SSMS).

Follow the below steps to restore your database named TESTDB:

Step 1 – Launch the SQL Server Management Studio (SSMS).

SSMS - Select a DB and right click to Tasks, Restore and Database.

Step 2 – Right-click on the TESTDB and click on the Tasks => Restore => Database. You should see the following page:

SSMS - Select Device, and then click on "..." to browse hour devices.

Step 3 – Select Device and click on the ““. You should see the following page:

SSMS - Select "backup media type" to file and then click "Add".

Step 4 – Click on the Add button. You should see the following page:

SSMS - Browse your files and then choose your database dump file that you want to restore and then click "OK".

Step 5 – Select your database backup file and click on the OK button. You should see the following page:

SSMS - On the next window, click on "OK" once your finished to add all databases dump file.

Step 6 – Click on the OK button again to close the “Select backup devices window”. You should see the following page:

SSMS - On the next window, check the databses you want to restore and then click "OK".

Step 7 – Next, click on the OK button to restore the backup of your database. Once the backup completed successfully, you should see the following page:

SSMS - Once the databases are successfully restore you'll get the confirmation message "Database TESTDB restored successfully".

Conclusion

In this guide, you learned how to backup and restore the MSSQL database in Windows server 2016. I hope this will help you to recover data in case of data corruption issue.