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.