It is important to create database backups regularly so that our data won't get lost if the database gets corrupted.
In SQL, we can create database backups using the BACKUP DATABASE
statement. For example,
BACKUP DATABASE my_db
TO DISK = 'C:\my_db_backup.bak';
Here, the SQL command creates a backup file of the my_db database inside C drive, named my_db_backup.bak.
Note: It's a common convention to use the .bak file extension for database backup files, however, it's not mandatory.
Backup Only New Changes in SQL
In SQL, we can also backup only the new changes compared with previous backup by using the WITH DIFFERENTIAL
command. For example,
BACKUP DATABASE my_db
TO DISK = 'C:\my_db_backup.bak'
WITH DIFFERENTIAL;
Here, the SQL command appends only new changes to the previous backup file. Hence, this command may work faster.
Restore Database From Backup
To restore a backup file to the database management system, we can use the RESTORE DATABASE
statement. For example,
RESTORE DATABASE my_db
FROM DISK = 'C:\my_db_backup.bak';
Here, the SQL command restores the my_db_backup.bak file in the database named my_db.