Hi,
In this article, I will continue to explain you backup types in the SQL Server 2017.
Read first article before this post.
We said, There are three types of Database backups in the SQL Server. These are like following.
- Full
- Differential
- Transaction Log
now let’s go to examine these.
set of file groups or files, and also enough log to allow for recovering that data
Full Backup: This type of backup is the most important for critical databases. Taking Full backup of the database means backup of all parts of the database like set of file groups or files, and also transaction log to allow for recovering database.
After the Full backup is taken, nothing else is needed in the Restore phase and Full backup is also the basic step for operations such as taking Differential and transaction log backup. If the database has corrupted and you have the most up-to-date Full backup, you don’t have to worry about it, you can restore and recover all data from backup.
Transactions can be backed up during the execution. The last committed transactions are included in full backup.
Differential Backup: This type of backup refers to the Backup of the changed Block after the last full backup. Second incremental backups after the first incremental backup will take the backup of the changed blocks after the previous first incremental backup. In order to take a differential backup, you must have a full backup associated with this backup.
Transaction Log Backup: Transaction log backup is very important for us to recover database to the exact point of any disaster. For example: If our database is Crash today at 10 o’clock, the most recent full backup was taken yesterday, this full backup is restored first, we need to recover database until at 10 o’clock with Transaction logs.
If the last Transaction log backup is taken 1 hour ago, we can recover database before until one hour. As it is understood from the scenario that I have given, it is important to take Backup of Transaction log, such as take Database in Backup.
If the database uses the Full or Bulk logged recovery model, the Transaction log backup can be taken frequently to avoid data loss. Transaction log backup is very small compared to other backups.
You can full back up SQL Server database like following, using SQL Server Management Studio ( Right Click related database—> Tasks–> Back up )
Or you can full back up SQL Server database using T-SQL Code like following.
BACKUP DATABASE [TEST] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\TEST.bak' WITH NOFORMAT, NOINIT, NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
I will continue to explain SQL Server backup details in the next post.
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.
https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/