Skip to main content
Our Tech Ideas

Database Backup & Restore in SQL server

Introduction

Backing up a database is one of the most important things you need to do when having a database depended application. It’s only all of your data in there, right? But often developers and management don’t realize the importance of backups and overall proper backup strategy for the most important side of the business – data and it’s consistency.

How backup works

When you run a backup database command SQL Server performs a Checkpoint on the data pages in memory. Checkpoint means that all transactionally committed dirty pages are written to disk. Dirty pages are simply changed pages in memory that haven’t been written to disk yet. After this, the data on the disk is backed up in one or multiple files depending on your requirements. A backup must be able to be restored to a transactionally consistent state which means that it will also contain the data from the transaction log needed to undo all of the transactions which are running while the backup is taken.

Types of Backups

Full database Backup

This backs up the whole database. In order to have a further differential or transaction log backups, you have to create the full database backup first.

Script

-- Back up the OurTechIdeas.com database as full backup
BACKUP DATABASE [OurTechIdeas.com] 
TO  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak'
GO

Result

Differential database backup

Differential database backups are cumulative. This means that each differential database backup backs up all the changes from the last Full database backup and NOT last Differential backup.

Script

-- Back up the OurTechIdeas.com database as differential backup
BACKUP DATABASE [OurTechIdeas.com] 
TO  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Diff Database Backup.dif' 
GO

Result

Transaction log backup

Transaction log backup isn’t possible under the Simple Recovery model. Two transaction logs can’t contain the same transactions which means that when restoring you have to restore every backup in the order, they were taken.

Script

-- Back up the OurTechIdeas.com database as Transaction log backup
BACKUP LOG [OurTechIdeas.com] 
TO  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Trn Database Backup.trn' 
GO

Result

Tail log backup

There seems to be a lot of confusion about this one since it’s a new term in SQL Server 2008 (I haven’t heard it being used in SS2k). A Tail log backup is the last Transaction log backup that you make prior to restoring a database. What this means is that if your DB crashes for whatever reason, you have to backup your transaction log so that you can do point in time recovery. This last backup is called Tail log backup. If your data file (MDF) is unavailable you need to use WITH NO_TRUNCATE option. # If your database is in OFFLINE or EMERGENCY state then tail log backup isn’t possible.

Script

-- Back up the OurTechIdeas.com database as tail-log backup
BACKUP LOG [OurTechIdeas.com] 
TO  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-tail-log Database Backup.trn' WITH  NO_TRUNCATE
GO

Result

Mirrored backup

Mirrored backups simply write the backup to more than one destination. You can write up to four mirrors per media set. This increases the possibility of a successful restore if a backup media gets corrupted. The following statement gives us two backups that we can restore from.

Script

-- make one backup on d: disk and a mirror on e: disk
BACKUP DATABASE [OurTechIdeas.com]
TO DISK = 'F:\SQL DATABASE\Gama\Mirror bkp\Mirror01\OurTechIdeas.com_Mirror_1.bak' -- 1st location
MIRROR
TO DISK = 'F:\SQL DATABASE\Gama\Mirror bkp\Mirror02\OurTechIdeas.com_Mirror_2.bak' -- 2nd location
-- create a new mirrored backup set
WITH FORMAT
GO

Result

Copy-only backup

Copy-only backups are new in SQL Server 2008 and are used to create a full database or transaction log backup without breaking the log chain. A copy-only full backup can’t be used as a basis for a differential backup, nor can you create a differential copy-only backup.

Script for the full copy-only backup

-- Back up the OurTechIdeas.com database as copy only
BACKUP DATABASE [OurTechIdeas.com] 
TO  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Copy-only backup.bak' WITH  COPY_ONLY 
GO

Script for the transaction copy-only backup

-- Back up the OurTechIdeas.com database as transaction log copy only
BACKUP DATABASE [OurTechIdeas.com] 
TO  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Copy-only backup.trn' WITH  COPY_ONLY 
GO

Result for the full copy-only backup

Result for the transaction copy-only backup

Types of Restore

Recovery states

To determine the state of the database after the store operation, you must select one of the options of the Recovery state panel.

RESTORE WITH RECOVERY

Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Recovers the database after restoring the final backup checked in the Select the backup sets to restore grid on the General page. This is the default option and is equivalent to specifying WITH RECOVERY in a RESTORE statement (Transact-SQL).

Script

-- RESTORE WITH RECOVERY
USE [master]
RESTORE DATABASE [OurTechIdeas.com_new] 
FROM  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2016_Data' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new_Data.mdf',  
MOVE N'AdventureWorks2016_Log' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new_Log.ldf'
GO

Result

RESTORE WITH NORECOVERY

Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. —Used in Mirroring. Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path. To recover the database, you will have to perform a restore operation by using the RESTORE WITH RECOVERY option (see the preceding option). This option is equivalent to specifying WITH NORECOVERY in a RESTORE statement. If you select this option, the Preserve replication settings option is unavailable.

Script

-- RESTORE WITH NORECOVERY
USE [master]
RESTORE DATABASE [OurTechIdeas.com_new2] 
FROM  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2016_Data' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new2_Data.mdf',  
MOVE N'AdventureWorks2016_Log' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_new2_Log.ldf',  NORECOVERY 
GO

Result

RESTORE WITH STANDBY

Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. () —Used in log-shipping. Leaves the database in a standby state, in which the database is available for limited read-only access. This option is equivalent to specifying WITH STANDBY in a RESTORE statement. Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone. Standby file: Specifies a standby file. You can browse for the standby file or enter its pathname directly in the text box.

Script

-- RESTORE WITH STANDBY
USE [master]
RESTORE DATABASE [OurTechIdeas.com_2019] 
FROM  DISK = N'F:\SQL DATABASE\Gama\OurTechIdeas.com-Full Database Backup.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2016_Data' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_2019_Data.mdf',  
MOVE N'AdventureWorks2016_Log' TO N'F:\SQL DATABASE\Gama\OurTechIdeas.com_2019_Log.ldf',  
STANDBY = N'F:\SQL DATABASE\Gama\OurTechIdeas.com_RollbackUndo_2019-10-02_10-28-03.bak'  
GO

Result