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 help 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 log.
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 to restore database in SQL Server using the SQL Server Management Studio (SSMS). For this, open SSMS, right-click the database, and select Tasks > Back Up.
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 the Script > Script Action to job option.
In the new job, go to the Schedules page and press the New button to create a new schedule.
You can schedule the job to run daily, hourly, etc.
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.
In the Backup type, make sure that the Differential backup type is selected.
How to Create Differential Backups Using T-SQL
Alternatively, you can 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.
Select the Files and filegroups option.
Select the files and filegroups that you want to back up and press OK.
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.
In the Restore Database window, select the database and the backup sets to restore, and press OK.
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.
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.
Select the source and destination database to restore and select the Filegroup set.
Select the source and destination database to restore and select the Filegroup set.
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 also discussed the ways 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 Repair for MS SQL Technician. It contain 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 handing 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 SQL 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.
- What to do if the SQL database file is corrupt? You can use Database Console Command (DBCC) CHECKDB to check & repair your corrupt database.
