MS SQL Server - Creating Backups

Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log.

Backup database can be done using either of the following two methods.

Method 1 – Using T-SQL

Full Type

Backup database <Your database name> to disk = '<Backup file location + file name>'

Differential Type

Backup database <Your database name> to 
   disk = '<Backup file location + file name>' with differential

Log Type

Backup log <Your database name> to disk = '<Backup file location + file name>'

Example

The following command is used for full backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_Full.bak'

Backup database TestDB to disk = 'D:\TestDB_Full.bak'

The following command is used for differential backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_diff.bak'

Backup database TestDB to disk = 'D:\TestDB_diff.bak' with differential

The following command is used for Log backup database called 'TestDB' to the location 'D:\' with backup file name 'TestDB_log.trn'

Backup log TestDB to disk = 'D:\TestDB_log.trn'

Method 2 – Using SSMS (SQL SERVER Management Studio)

Step 1 − Connect to database instance named 'TESTINSTANCE' and expand databases folder as shown in the following snapshot.

Step 2 − Right-click on 'TestDB' database and select tasks. Click Backup and the following screen will appear.

Step 3 − Select backup type (Full\diff\log) and make sure to check destination path which is where the backup file will be created. Select options at the top left corner to see the following screen.

Step 4 − Click OK to create 'TestDB' database full backup as shown in the following snapshot.