For an instructor lead, in-depth look at learning SQL click below.
In the world of databases, there is nothing as crucial as backing up your data to prevent data loss, and restoring it whenever required. This process becomes remarkably efficient and seamless with SQL Server. This article aims at introducing the essential backup and restore techniques using SQL Server.
Backup in SQL Server
Backup is the process of creating a copy of the data in a database. SQL Server provides different types of data backup:
- Full Backup
- Differential Backup
- Transaction Log Backup
SQL Code for Full Backup
A full backup contains all the data in a specific database. The following example shows how to take a full backup of a database named ‘testDB’.
|
1 2 3 4 |
BACKUP DATABASE testDB TO DISK = 'D:\Backups\testDB.bak'; |
SQL Code for Differential Backup
A differential backup only includes the data that has changed since the last full backup. Here’s an example:
|
1 2 3 4 5 |
BACKUP DATABASE testDB TO DISK = 'D:\Backups\testDB.bak' WITH DIFFERENTIAL; |
SQL code for Transaction Log Backup
A transaction log backup allows you to backup the active part of the transaction log. So, after you backup the transaction log, it becomes inactive and available for reuse.
|
1 2 3 4 |
BACKUP LOG testDB TO DISK = 'D:\Backups\testDB_log.bak'; |
Restore in SQL Server
The restore operation is used to restore data from the backups when needed, such as when a failure occurs. SQL server allows you to restore your database using the following tactics:
SQL Code for Full Restore
The following command restores full backup from a disk file.
|
1 2 3 4 |
RESTORE DATABASE testDB FROM DISK = 'D:\Backups\testDB.bak'; |
SQL Code for Differential Restore
For the differential restore, you first need to restore the full backup WITH NORECOVERY. After that, a differential backup can be restored.
|
1 2 3 4 5 6 7 8 9 |
RESTORE DATABASE testDB FROM DISK = 'D:\Backups\testDB.bak' WITH NORECOVERY; RESTORE DATABASE testDB FROM DISK = 'D:\Backups\testDB_diff.bak' WITH RECOVERY; |
SQL Code for Log Restore
You can restore the transaction log backup after a full restore.
|
1 2 3 4 5 |
RESTORE LOG testDB FROM DISK = 'D:\Backups\testDB_log.bak' WITH RECOVERY; |
Backup and restore operations are a fundamental part of the database management and auditing process. SQL Server provides an extremely powerful, yet straightforward, set of tools that make the process easier. It is crucial for database administrators to be familiar with these command sets.
Note:
Ensure that you replace ‘testDB’ and ‘D:\Backups\testDB.bak’ with your actual database name and backup file path respectively.