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-cpck on TestDB database and select tasks. Cpck Backup and the following screen will appear.

Step 3 − Select backup type (Fulldifflog) 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 − Cpck OK to create TestDB database full backup as shown in the following snapshot.

Previous Page Print Page Next Page   Advertisements