SQL BACKUP DB

The SQL BACKUP DATABASE Statement

In SQL Server, the BACKUP DATABASE statement is employed to generate a comprehensive backup of a pre-existing SQL database.

Syntax

BACKUP DATABASE databasename
TO DISK = ‘filepath‘;

The SQL BACKUP WITH DIFFERENTIAL Statement

A differential backup selectively backs up the sections of the database that have been altered since the previous full database backup.

Syntax

BACKUP DATABASE databasename
TO DISK = ‘filepath‘
WITH DIFFERENTIAL;

BACKUP DATABASE Example

The following SQL statement generates a complete backup of the current database “testDB” to the D disk:

Example

BACKUP DATABASE testDB
TO DISK = ‘D:\backups\testDB.bak’;

Tip: Always back up the database to a different drive than the actual database. Then, if you get a disk crash, you will not lose your backup file along with the database.

BACKUP WITH DIFFERENTIAL Example

The following SQL statement initiates a differential backup of the database “testDB”:

Example

BACKUP DATABASE testDB
TO DISK = ‘D:\backups\testDB.bak’
WITH DIFFERENTIAL;

Tip: A differential back up reduces the back up time (since only the changes are backed up).