Connect to the database

Go to SQL Server Management Studio

Connect to the database:

  • Server type: Database engine
  • Server name: localhost
  • Authentication: Windows authentication

Create the backup device

Go to Object explorer -> Server Objects -> Backup Devices and right-click on it to select New Backup Device

  • Name: sql-backup-device
  • Destination: File: C:\Program Files\Microsoft SQL Server\MSSSQL10.MSSQLSERVER\MSSQL\Backup\sql-backup-device.bak

If another destination is selected, check that the file ends with the .bak extension

Check the database allows to do full backups

Go to Object explorer -> Databases and right-click on it to select Properties -> Options

Check the value for Backup model is Full. Check the value for Compatibilty level is SQL Server 2008 (100).

Full backup

Go to Object explorer -> Databases and right-click on the database you want to back up to select Tasks -> Backup

General:

  • Backup type: Full
  • Backup component: Database
  • Destination: Add -> sql-backup-device

Options: [choose one]

  • Append to the existing backup set
  • Overwrite all existing backup sets

Transaction log backup

Go to Object explorer -> Databases and right-click on the database you want to back up to select Tasks -> Backup

General:

  • Backup type: Transaction Log
  • Backup component: Database
  • Destination: Add -> sql-backup-device

Options:

  • Append to the existing backup set

Check the backup

Go to Object explorer -> Server objects -> Backup Devices then right-click on sql-backup-device -> Properties -> Media content. In the backup set should appear the backup just done.

Restore the backup

Example: Full backup + Transaction log backup

1) Full

Go to Object explorer -> Databases and right-click on the database you want to restore to select Tasks -> Restore -> Database

General:

  • From device: sql-backup-device
  • Select all backup sets to restore

Options:

  • Restore options: Overwrite the existing database (WITH REPLACE)
  • Recovery state: Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

The database will now appear as ‘Restoring’ and will not be accessible for other applications.

2) Transaction Log

Go to Object explorer -> Databases and right-click on the database you want to restore to select Tasks -> Restore -> Transaction Log

General:

  • From device: sql-backup-device
  • Select up to the backup set you want to restore, usually all of them.
  • Check that you could potentially do a point-in-time recovery.

Options:

  • Recovery state: Leave the database ready to use by rolling back uncomitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)

The database will now be accessible for other applications.