Everyone who works with SQL Server databases first or last faces the need to protect their information. That implies making database backups. So what is a database backup? Database backup is a process of saving your database in its present condition. It allows making a copy of your database in case you made some unnecessary changes and want to roll back or your primary database is corrupted.
Types of SQL Server Backups
There are three main types of backup:
- Full database backup creates a backup copy containing all data files and active part of the transaction log.
- Differential database backup creates a backup copy containing all changes made to the database since the last full backup and active part of the transaction log.
- Transaction log backup contains all log records that haven’t been backed up, up to the last log record that exists at the time of backup completion. It has to be admitted, that transaction log backup can be carried out only if you use a full or bulk-logged recovery model.
How Often Should You Backup Your Databases?
Keep in mind that in order to maintain your databases safe and sound you should make backups regularly. The question here is: “How often should I backup my databases?” The answer is: it depends on how large your database is, how often you make changes to your database and how important your data is, etc. For example, you can make a full backup every 24 hours, differential backup every 6 hours and backup your transaction log every hour.
There are many ways of making database backups. However, in this article we are going to explore only three of them.
How to Backup SQL Server using T-SQL Commands
For making a full backup execute “BACKUP DATABASE” command:
BACKUP DATABASE your_database TO DISK = 'full.bak'
For differential backup you need to add “WITH DIFFERENTIAL” clause:
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
For transaction log backup use “BACKUP LOG” command:
BACKUP LOG your_database TO DISK = 'log.bak'
How to Backup SQL Server using SSMS
If SQL Management Studio is installed on your computer, you can backup your database in just a few simple steps as explained in the video below:
How to Backup SQL Server using SQLBackupAndFTP
Perhaps the easiest way of making database backups regularly is to use SQLBackupAndFTP utility. This short video explains how to do it in details:
