Backing up and restoring databases is a critical task for SQL Server administrators to ensure data integrity and recovery in case of failures. SQL Server Management Studio (SSMS) provides an intuitive interface for managing backups, while T-SQL offers automation and scripting capabilities. This guide explores different backup types and recovery methods using SSMS and T-SQL.
What is SQL Server Management Studio?
SQL Server Management Studio (SSMS) is a tool provided by Microsoft for managing SQL Server databases. It integrates a graphical user interface with a powerful query editor, making it convenient for database administrators and developers to perform database management, queries, debugging, backups, restores, and other tasks. SSMS supports writing and executing T-SQL statements, provides an Object Explorer for intuitive database object management, such as tables, views, stored procedures, and security settings. Additionally, it includes built-in performance monitoring and tuning tools to help optimize database queries and indexes. SSMS is tightly integrated with SQL Server databases, compatible with Azure SQL databases, and regularly updated to provide a stable database management experience.
How to Create a Full Backup Using SSMS?
You can create a full backup using SSMS to restore a database in SQL Server. To do this, open SSMS, right-click on the database, and select Tasks > Backup.
In the Back Up Database window, select Full backup type.
Creating a Full Database Backup Using T-SQL
If you want to automate backups, you can use T-SQL code. T-SQL is the SQL Server language for automating 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 DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'AdventureWorks2019-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
How to Schedule Automatic Backups?
You can schedule backups to run at a specific time. To do this, create a backup in SSMS and then select Script > Script Action to Job.
Create the backup in SSMS and write the script action to a job.
In the new job, go to the Schedules page and click the New button to create a new schedule.
You can schedule jobs to run daily, hourly, etc.
How to Create a Differential Backup Using SSMS?
First, ensure that you already have a full backup.
Next, right-click on the database and select Tasks > Backup.
In the Backup type, make sure to select Differential backup type.
Creating a Differential Backup Using T-SQL Commands
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 on the database and select Tasks > Backup.
Select the Files and Filegroups option.
Choose the files and filegroups to back up, then click OK.
Creating a File or Filegroup Backup Using T-SQL Commands
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 a backup using SSMS, go to Object Explorer, right-click on the database, and select Restore Database.
In the Restore Database window, select the database and backup set to restore, then click OK.
Restoring a Database Using T-SQL Commands
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 a database using SSMS, go to Object Explorer, right-click on the database, and select Restore Database.
In the Restore Database window, select the database to restore, choose the full backup, and then select the differential backup set to restore, then click OK.
Restoring a Differential Backup Using T-SQL Commands
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 Object Explorer, right-click on the database, and select Restore Files and Filegroups.
Select the source and target databases to restore, then choose the filegroup set.
Specify the source database and target database for restoration.
Restoring Database Files or Filegroups Using T-SQL Commands
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
More Comprehensive Data Protection Strategy for SQL Server
To ensure the long-term security and efficient recovery of SQL Server databases, in addition to using SSMS and T-SQL, you can also use Vinchin Backup & Recovery to provide smarter automated backup and fast recovery solutions.
Vinchin Backup & Recovery offers an automated backup and recovery solution specifically designed for VMs and databases like SQL Server, supporting full, incremental, and differential backups. With built-in data deduplication and compression technologies, it significantly reduces storage consumption and backup time. Additionally, Vinchin’s backup solution eliminates the need for complex manual intervention, enabling automatic database protection while supporting cross-platform migration and cloud storage. In the event of an SQL Server failure, Vinchin helps administrators quickly restore databases, preventing data loss and prolonged downtime, thus offering a more comprehensive disaster recovery guarantee for enterprises.
To create SQL Server database backup jobs, please go to Physical Backup > Database Backup > Backup page:
1. Select the databases which need to be backed up.
2. Select a backup node on which you want the backup data to be processed and stored.
3. Configure backup strategies according to your needs.
4. Review and confirm the settings.
Click the button below to try Vinchin's 60-day free trial to experience an efficient and reliable data backup and recovery solution!
SQL Server Management Studio Backup Database FAQs
1. What if the backup is corrupted?
If the backup file is corrupted, you can use SQL Toolkit for MS SQL. The software includes a module called Stellar Backup Extractor for MS SQL that can extract the database from a corrupted backup (.bak) file.
2. What if the SQL database file is corrupted?
You can use the Database Console Command (DBCC) CHECKDB to check and repair a corrupted database.
Conclusion
In this blog, we discussed different types of backups in SQL Server and how to create these backups. We also covered methods for restoring database backups using SSMS and T-SQL commands. If a database backup is corrupted, third-party tools can be used to recover an SQL database from a damaged backup file (.bak).
