How to Backup and Restore Database in SQL Server?

Written By Bharat Bhushan
Approved By Kuljeet Singh
Updated on 30 May, 2024
Min Reading 6 Min
Share

Table of Content

Summary: If database gets damaged or corrupted, backups come in handy. In this article, we’ll discuss how to create different types of backups in SQL Server. We’ll also share the process to restore backups using SSMS and T-SQL. In addition, you’ll know about a SQL recovery toolkit that can help recover data in case the SQL database backups get corrupt.

Free Download100% secure

In SQL Server, the database can be stored in different files and filegroups. If your database is small (100 MB or less), you do not need to worry too much about files and filegroups. But if you have a large database (in several GBs or TBs), separating the data into different files helps you to optimize the performance. You can store the data files in different disks. This will also help to backup and restore the information faster because you do not need to restore the entire database but only the files or the filegroups selected.

Types of Backups

In SQL Server, there are different types of backups:

Full Backup: It contains the entire database information.
Differential Backup: It requires a full backup and then it stores the differences between the previous backup and the current database. This backup requires less information because it stores only the differences.
Transaction Log Backup: It stores the information about the Transaction logs.

Why is it important to have a backup?

Your database may get damaged due to several reasons. A backup will help you to restore the database lost in case of disasters and problems, like hardware failure, virus attack, or others.

How to create a full backup using SSMS?

You can create a Full Backup in SQL Server using the SQL Server Management Studio (SSMS). For this, open SSMS, right-click the database, and select Tasks > Back Up.

Image of how to backup a database using SSMS

In the Back Up Database window, select the Full backup type.

How to create full database backup using T-SQL?

If you want to automate the backup, you can use the T-SQL code. T-SQL is the SQL Server language used to automate SQL Server tasks.

The following example shows how to create a Full Backup using T-SQL.

BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\backups\AdventureWorks2019.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

How to schedule a backup automatically?

You can schedule to run a backup at a certain time. To do that, create a backup in SSMS and select Script > Script Action to Job.

Image of how to schedule to run a backup. Create a backup in SSMS and select Script > Script action to Job

In the new job, go to the Schedules page and press the New button to create a new schedule.

Image of how to create a new schedule by going to Schedules Page and Pressing the New Button in the New Job window.

You can schedule the job to run daily, hourly, etc.

image of how to schedule a job to run daily, hourly, etc. in the new job schedule window.

How to create differential backups using SSMS?

First, make sure that you already have a Full Backup.

Next, right-click the database and select Tasks > Back Up.

Image of how to backup a database in SSMS

In the Backup type, make sure that the Differential backup type is selected.

Image of how to set backup type to differential

How to create differential backups using T-SQL?

You can also use the T-SQL commands to create a differential backup.

BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\backups\AdventureWorks2019.bak’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

How to create a file or filegroup backup using SSMS?

In SSMS, right-click the database and select Tasks > Back Up.

Image of how to backup a database from SSMS

Select the Files and filegroups option.

Image of selecting the backup component to Files and filegroups option

Select the files and filegroups that you want to back up and press OK.

Image of checked files and filegroups that you want to back up

How to create a file or filegroup backup using T-SQL?

Alternatively, you can use the T-SQL commands to create files or filegroups backup.

BACKUP DATABASE [Northwind] FILEGROUP = N’PRIMARY’ TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Northwind.bak’ WITH NOFORMAT, NOINIT, NAME = N’Northwind-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

How to restore a backup using SSMS?

To restore the backup using SSMS, go to the Object Explorer, right-click the database, and select the Restore Database option.

Image of how to restore a database from Object Explorer in SSMS

In the Restore Database window, select the database and the backup sets to restore, and press OK.

Image of which backup sets to restore from Restore Database window

How to restore a backup using T-SQL?

Alternatively, you can use the T-SQL commands to restore a database.

USE [master]
RESTORE DATABASE [AdventureWorks2019] FROM DISK = N’C:\backups\AdventureWorks2019.bak’ WITH FILE = 10, NOUNLOAD, STATS = 5
GO

How to restore a differential backup using SSMS?

To restore the database using SSMS, go to the Object Explorer, right-click the database, and select the Restore Database option.

In the Restore Database window, select the database to restore, choose the FULL backup, and select the differential sets to restore, and press OK.

Image of how to select the database to restore from the Restore database window

How to restore a differential backup using T-SQL?

Alternatively, you can use the T-SQL commands to restore differential backup.

USE [master]
RESTORE DATABASE [AdventureWorks2019] FROM DISK = N’C:\backups\AdventureWorks2019.bak’ WITH FILE = 10, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE DATABASE [AdventureWorks2019] FROM DISK = N’C:\backups\AdventureWorks2019.bak’ WITH FILE = 11, NOUNLOAD, STATS = 5
GO

How to restore files and filegroups using SSMS?

Open SSMS, go to the Object Explorer, right-click the database, and select the Restore Files and Filegroups option.

Image of how to restore Files and filegroups from Object Explorer in SSMS

Select the source and destination database to restore and select the Filegroup set.

Image of how to select the backup sets to restore from Restore Files and Filegroups window

How to restore files and filegroups using T-SQL?

Alternatively, you can use the T-SQL commands to restore a database file or filegroup.

RESTORE DATABASE [Northwind] FILE = N’Northwind’ FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Northwind.bak’ WITH FILE = 7, NOUNLOAD, STATS = 10
GO

Conclusion

In this article, we’ve discussed different types of backups in SQL Server and how to create these backups. We’ve also discussed the steps to restore database backups using SSMS and T-SQL commands. In case the database backup gets corrupted, you can use a third-party tool, such as Stellar Toolkit for MS SQL. It contains a specialized tool, known as Stellar Backup Extractor for MS SQL that can recover SQL database from corrupt backup file (.bak).

FAQ

Why should I use T-SQL to backup and recover a database if the SSMS is easier?

T-SQL is generally used to automate the process.

What type of backup should I use for my machine?

It depends on the data and the size of your database. If you are handling a small database, you could use a full backup. However, if your database is big, combine full backups with differential and transactional logs.

If my database is corrupt. Can I use the backup to restore the database?

Yes, you can use the backup in this scenario.

What to do if the backup is corrupt?

If the backup file is corrupted, you can use Stellar Toolkit for MS SQL. This software contains a module named Stellar Backup Extractor for MS SQL which can extract database from corrupt backup (.bak) file.

Are there any third-party tools to back up database?

Some third-party solutions to back up SQL Server databases are:
SQLBackupAndFTP: It is used to back up the database and store it in an FTP, SFTP, FTPS, NAS, Network, Google Drive, Dropbox, Box, Amazon S3, Azure Storage, Backblaze, or other locations.

Microsoft SQL Server Backup and Recovery Solution from Vembu: This software can help to back up multiple databases from multiple servers.

Was this article helpful?

YES0

About The Author

Bharat Bhushan

Bharat Bhushan is an experienced technical Marketer working at Stellar Data Recovery - expertise in data care. He is skilled in Microsoft Exchange Database, MSSQL Database troubleshooting & data warehousing. He is a Management Post Graduate having a strong grip in Technology & certified in SAP-SD, Oracle 10g & Informatica Powercenter 9.1.