How to create a copy of data in MS SQL Server

Backup is a data backup / database. Backing up databases in MS SQL Server is very important to protect data before losing databases. There are 3 main backup types in MS SQL Server: Full / Database, Differential / Incremental, Transactional Log / Log.

Here are 2 ways to create a backup of databases in MS SQL Server.

Method 1: Use T-SQL

Full / Database

 Backup database to disk = '' 

Differential / Incremental

 Backup database to 
  disk = '' with differential 

Transactional Log / Log

 Backup log to disk = '' 

For example: The following command is used to backup the entire database (Full / Database) named TestDB to the address D: with the name of the backup copy is TestDB_Full.bak

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

The following command is used to back up changes since the last backup on the database (Differential / Incremental) named TestDB to address D: with the name of the backup copy is TestDB_Full.bak

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

The following command is used to back up the order of all transactions since the last transaction Log backup on the database (Transaction Log / Log) named TestDB to the address D: with the name of the backup copy is TestDB_Full.bak

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

Method 2: Use SQL Server Management Studio (SSMS)

Step 1 : Connect to the database installation named TESTINSTANCE and open the database directory as shown below.

Open the database directory

Step 2: Right-click on the Testdb database and select Backup backup task , then the screen below will appear.

Information to select the backup form for the database

Step 3: Select the type of database backup and choose the correct path for the copy to be saved. Select Options in the upper left corner of the screen, then the following dialog box will appear.

Select to backup another database

Step 4: Select OK to create a backup for TestDB as shown in the image below.

Create a successful backup

Check in the selected directory path, you will see the backup file.

The backup file of the TestDB database has just been created

Previous article: How to delete the database in MS SQL Server

Next lesson: How to recover the database in MS SQL Server