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. Show
Types of BackupsIn SQL Server, there are different types of backups:
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 SSMSYou 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. Choose Full Backup from SSMSIn the Back Up Database window, select the Full Backup Type. Backup database windowHow to Create Full Database Backup Using T-SQLIf 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 AutomaticallyYou 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. Create a backup in SSMS and 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 SSMSFirst, 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. Backup type differential selectedHow to Create Differential Backups Using T-SQLAlternatively, 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 SSMSIn 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-SQLAlternatively, 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 SSMSTo restore the backup using SSMS, go to the Object Explorer, right-click the database, and select the Restore Database option. Restore a backup using SSMSIn the Restore Database window, select the database and the backup sets to restore, and press OK. How to Restore a Backup Using T-SQLAlternatively, 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 SSMSTo 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. Choose the FULL backupHow to Restore a Differential Backup Using T-SQLAlternatively, 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 SSMSOpen 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. And select the Restore Files and Filegroups optionSelect the source and destination database to restore and select the Filegroup set. Source and destination database to restore.How to Restore Files and Filegroups Using T-SQLAlternatively, 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 ConclusionIn 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). |