SQL Server Database Backup Examples

By:   |   Updated: 2021-11-18   |   Comments (2)   |   Related: > Backup


Problem

You may be new to SQL Server and need to know how to backup a SQL Server database and what the different backup types are. In this article we will cover these topics and how to create backups using the GUI and T-SQL scripts.

Solution

While there are a number of important functions a SQL Server Database Administrator performs, backing up user databases is the most important. Without backups there is nothing to restore from. It's important to understand the differences between backup types.

We'll review the three basic types of backups (full, differential, and transaction log), what they are, when to use them and how to take them.

SQL Server Backup Types

  • Full SQL Server Database Backup
    • As the name implies, this is a full backup of the entire database
    • Database recovery model can be full, bulk-logged, or simple
    • Foundation for restoring Differential and Transaction Log backups
    • Used to restore a database to the point in time the backup finished
  • Differential SQL Server Database Backup
    • Backup of the data that has changed since the last successful backup (Diff Backup)
    • Database recovery model can be full or bulk-logged only
    • Restored after a Full Backup restore to restore a database to the point in time the Differential Backup was taken
    • Can make restoring Transaction Log Backups quicker
  • Transaction Log SQL Server Database Backup
    • Backup of the transaction log (sequential record of the changes made to the database) since the last successful full or transaction log backup (Incremental Backup)
    • Database recovery model can be full or bulk-logged only
    • Restored, in the order they were taken, after a Full (and optionally a Differential Backup) to restore to a point in time of during or at the end of a Transaction Log Backup

Full SQL Server Database Backup - SQL Server Management Studio (SSMS)

Open SSMS Object Explorer.

  1. Expand the SQL Server tree
  2. Expand Databases
  3. Right click on database to backup
  4. Select Tasks
  5. Back Up…
SSMS Object Explorer
  1. Select 'Full' in 'Backup type' dropdown
  2. Select 'Disk' in 'Backup to' dropdown
  3. Add…
General
  1. Leave at default directory location or change if desired
  2. … button
Backup Destination
  1. Name backup file (.bak extension indicates full backup)
  2. Click OK
Locate Destination Files
  1. Click OK
Select Backup Destination
  1. Select 'Media Options' page
  2. Check 'Overwrite all existing backup sets' radio button
  3. Check 'Perform checksum before writing to media' box
  4. Select 'Backup Options' page
Media Options
  1. Name backup
  2. Select 'Compress backup' from 'Set backup compression' dropdown
Backup Options
  1. Click OK
Backup Complete

Full Backup - Directly Executing SQL

We can accomplish the same by just running the following SQL. 

BACKUP DATABASE [MyDatabase] -- database name
TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MyDatabase_FullBackup.bak' -- backup file name
WITH INIT -- overwrite backup file 
   , NAME = N'MyDatabase-Full Database Backup' -- backup name
   , COMPRESSION -- compress backup regardless of server default
   , STATS = 10 -- display backup progress
   , CHECKSUM -- perform checksum before writing to disk
GO
Backup Output

Differential Backup - SQL Server Management Studio (SSMS)

Taking a Differential Backup looks almost the same as a Full Backup.

  1. Instead of the default of Full, select 'Differential' from the 'Backup type' dropdown
  2. Add…
General

The rest of the steps will look almost exactly the same.

  1. Leave at default directory location or change if desired
  2. …
Backup Destination
  1. Name backup file (.dif extension indicates full differential backup)
  2. Click OK
Locate File
  1. Select 'Media Options' page
  2. Check 'Overwrite all existing backup sets' radio button
  3. Check 'Perform checksum before writing to media' box
  4. Select 'Backup Options' page
MEdia Options
  1. Name backup
  2. Select 'Compress backup' from 'Set backup compression' dropdown
  3. Click OK
Backup Options
  1. Click OK
Backup Complete

Differential Backup - Directly Executing SQL

You will notice here the SQL is almost the same as with a Full Backup with the exception of names and the additional 'DIFFERENTIAL' option.

BACKUP DATABASE [MyDatabase] -- database name
TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MyDatabase_DifferentialBackup_1.dif' -- backup file name
WITH DIFFERENTIAL -- tells BACKUP DATABASE you want a differential backup
   , INIT -- overwrite backup file
   , NAME = N'MyDatabase DifferentialBackup_1.dif'  -- backup name
   , COMPRESSION -- compress backup regardless of server default  
   , STATS = 10 -- display backup progress
   , CHECKSUM -- perform checksum before writing to disk
GO
Backup Complete

Transaction Log Backup - SQL Server Management Studio (SSMS)

Taking a Transaction Log backup also looks almost the same as a Full or Differential.

  1. Instead of the default of Full, this time, select 'Transaction Log' from the 'Backup type' dropdown
  2. Add…
General
  1. Leave at default directory location or change if desired
  2. … button
Select Backup Destination
  1. Name backup file (.trn extension indicates transaction log backup)
  2. Click OK
Locate Files
  1. Select 'Media Options' page
  2. Check 'Overwrite all existing backup sets' radio button
  3. Check 'Perform checksum before writing to media' box
  4. Select 'Backup Options' page
Media Options
  1. Name backup
  2. Select 'Compress backup' from 'Set backup compression' dropdown
  3. Click OK
Backup Options
  1. Click OK
Backup Complete

Transaction Log Backup - Directly Executing SQL

The difference you'll see here is in addition to the names is a Transaction Log backup is run with a 'BACKUP LOG' rather than a 'BACKUP DATABASE'.

BACKUP LOG [MyDatabase] -- database name
TO  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MyDatabase_TransactionLogBackup_1.trn' -- file location and backup file name
WITH INIT -- overwrite backup file
   , NAME = N'MyDatabase-Transaction Log Backup 1' -- backup name
   , COMPRESSION -- compress backup regardless of server default
   , STATS = 10 -- display backup progress
   , CHECKSUM -- perform checksum before writing to disk
GO
Backup Complete
Next Steps

In this tip we've seen the three basic SQL Server backup types and an example of how to run each backup operation with SSMS and SQL. While manually backing up databases isn't often done, it's helpful to understand the differences and understand what the automation is doing.

Here are some links to more tips on backups and restores.

Backups

Restores



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-11-18

Comments For This Article




Friday, July 19, 2024 - 2:44:11 PM - Joe Gavin Back To Top (92401)
Good catch Mahdi. While it's probably unlikely someone would be doing diffs without tran log backups, you are correct. I just backed up and restored from a full and diff and a database is in simple recovery model. I wrote that blindly based on the documentation.

Friday, July 19, 2024 - 5:52:55 AM - MahdiKhalilNejad Back To Top (92400)
I think this sentence is not correct (in differential backup): "Database recovery model can be full or bulk-logged only"
in simple mode also can take differential backup.














get free sql tips
agree to terms