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.
Contents
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.
- 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.
- 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?
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.
- 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.
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
T-SQL is generally used to automate the process.
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.
Yes, you can use the backup in this scenario.
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.
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.
