How to Backup and Restore SQL Server Database: Full and Differential

A while ago, I wrote an article here on how to restore a complete database with a different database name. That is actually restoring a database from a full backup. This time, in addition to that, I want to show you how to backup a SQL Server database and restore it, both in full and differential mode.

A bit theory first.

Full and Differential Backup

The name should be self-explanatory: when you backup a database in a full mode, it means you create the complete database backup. It is OK (kind of) to do this everyday, if your database is only a few GBs. Even then, your storage will be full someday.

Here is when you should consider a differential backup. In a differential mode, you backup only the changes occured AFTER your full backup. Some people might also refer to it as a delta backup. Consequently, the differential backup is usually much smaller to a full backup (unless, in a rather rare case, your database received a lot of data after you run a full backup).

Since a differential backup is a delta on a full backup, you must run a full backup first, so that the differential backup has a base. An old-fashioned database administrator runs a full backup once a week, i.e. on Sunday, and a differential backup everyday i.e. from Monday to Saturday.

Let’s do an example.

Run Backup In SQL Command

You can do this with a GUI in SQL Server Management Studio. But if you want to create and automate a database backup job (and impress your colleagues at the same time), T-SQL command the way to go.

Say you have a table called city and fill it with some data on day 0 (yes, we IT profesionals like to start everything with 0):

/* Day 0 */
USE ansaworks
GO
CREATE TABLE dbo.city (
    id INT IDENTITY(1,1) NOT NULL,
    city NVARCHAR(100)
)
GO
INSERT dbo.city VALUES('Hamburg')
INSERT dbo.city VALUES('Berlin')
GO

By the end of the day 0, you want to do a full backup with this command:

-- Full backup
BACKUP DATABASE ansaworks
TO DISK = N'C:\Temp\ansaworks_full.bak' 
WITH FORMAT, NAME = N'ansaworks Full Database Backup'   
GO

On the next days (day 1 and 2), business goes as usual, and you decided to do a differential backup once a day (good call!):

/* Day 1 */
INSERT dbo.city VALUES('London')
GO
-- Differential backup
BACKUP DATABASE ansaworks 
TO DISK = N'C:\Temp\ansaworks_diff_1.bak' 
WITH FORMAT, NAME = N'ansaworks Diff Database Backup', Differential  
GO

/* Day 2 */
INSERT dbo.city VALUES('Paris')
GO
-- Differential backup
BACKUP DATABASE ansaworks 
TO DISK = N'C:\Temp\ansaworks_diff_2.bak' 
WITH FORMAT, NAME = N'ansaworks Diff Database Backup', Differential  
GO

On day 3, Mike from the Marketing department was somehow able to corrupt the database so bad, that it needs to be restored. As the database administrator in the company, this is your chance to shine.

Restore Database With Full and Differential Backup

All you need to do is just first restore the full backup WITH NORECOVERY, and then the latest differential backup (ansaworks_diff_2.bak):

USE master
GO
RESTORE DATABASE ansaworks FROM DISK = 'C:\Temp\ansaworks_full.bak' WITH NORECOVERY, REPLACE
GO
RESTORE DATABASE ansaworks FROM DISK = 'C:\Temp\ansaworks_diff_2.bak' WITH RECOVERY
GO

Please note: in this case, you don’t care about the first differential backup file (ansaworks_diff_1.bak), because you want to restore the database to the latest state.

The scenario and the codes can also be found in my github channel.

Leave a Reply