Summary -

In this topic, we described about the below sections -

Why to take database backup?

A database always created for purpose either it may be as per requirement or it may be created while we are learning. There might be a chance that system gets failed and database can get corrupted. The most important data may get lost due to the system failure. To avoid this kind of situations, the database backup should be taken on time to time basis.

BACKUP DATABSE statement fulfils this requirement. BACKUP DATABASE statement is used in SQL Server.

How to take a database backup?

BACKUP DATABSE is used to create complete back up of an existing database. It is always suggestable to save backup file in different drive because when disk crash occurred the database may losses data but backup file is safe.

Backup of an existing database can be done in two ways as shown in below –

  1. Full backup
  2. Partial backup of database with changed parts from the last full backup

Full Backup

The syntax to create the full backup of the database as follows -

Syntax -

BACKUP DATABASE database_name TO DISK = 'filepath';

Note :- The backup file should always have an extension of .bak.

Example-

Consider that we are going to take a backup of existing databse emptestdb to the file etdb.bak. The code for renaming the database is -

BACKUP DATABASE emptestdb TO 'D:\backups\etdb.bak'

After executing the above query, emptestdb full backup has been created at location D:\backups\etdb.bak. We should have admin privileges to rename the database.

Partial backup of database with changed parts from the last full backup

The syntax to create the partial backup of database with changed parts from the last full backup is -

Syntax -

BACKUP DATABASE database_name  TO DISK  = 'filepath' WITH DIFFERENTIAL;

Note :- The backup file should always have an extension of.bak .

Example -

Consider that we are going to take a partial backup of existing database emptestdb to the file etdb.bak. The code for renam ing the database is -

BACKUP  DATABASE  emptestdb  TO  'D: \backups \ etdb.bak' DIFFERENTIAL;

After executing the above query, emptestdb partial backup has been created at location D: \ backups \ etdb.bak . We should have admin privileges to rename the database.

What happen if try to take the backup of the nondatabase. existing database?

When trying to backup a database that does not exist, system throws the error: "Can't backup database 'database_name'; database doesn't exist."