SQLServerLog.com

SQL Server 2012 Backup Types Step by Step with SSMS and T-SQL

by · Published · Updated

Backup of database is routine work in DBAs life and it is very important to have latest backup so that at the time of disaster or failure we can achieve point in time recovery or at least minimise data loss. In this article we are going to discuss different types of database backups with SQL Server Management Studio (SSMS) and by T-SQL. We are going to use dummy database named as ‘Inventory’.

Following are Database Backup Types, we will cover only top three here as they are most commonly used.

  1. Full Backup
  2. Differential Backup
  3. Transaction Log Backup
  4. Copy-only Backup
  5. Files and Filegroups Backup

Kindly refer Figure-1 as below:

BackupTypes

Figure 1: BackupTypes

Full Backup

Full database backup also called complete backup which includes data files with currently running transaction log. Many database users think that to truncate the transaction log we should take Full Backup but it is not the case.

– Right click on Database (which you want to backup)

– Go to Task and click ‘Back Up…’ [refer Figure-2]

Figure 2: Backup Steps

Figure 2: Backup Steps

Backup Database window will pop up where you have to select Backup type as ‘FULL’, you may see default path already mentioned in Destination (Back up to:) which you can remove by clicking ‘Remove’ button  and click ‘Add..’ button to select your Destination path and navigate to your disk location for taking backup as shown in Fugure-3.

Full Backup

Figure 3: Full Backup

Now on left top corner of window click Options, where you can find many options. You may tick ‘Verify backup when finished’ in Reliability section and set backup compression to ‘Compress backup’ if you want to save the storage cost and time.

Backup Options

Figure 4: Backup Options

Now click OK, once process is completed you will get message by SSMS as ‘The backup of database ‘Inventory’ completed successfully’.

Backup Cofirmation

Figure 5: Backup Cofirmation

Click OK and navigate to your disk drive to cross check backup file.

If you want to take the same Full backup by T-SQL then follow as:

BACKUP DATABASE [Inventory] TO DISK = N'C:\Backup\FullBackup_Inventory.bak' 
WITH NOFORMAT, NOINIT, NAME = N'Inventory-Full Database Backup', SKIP, NOREWIND, 
NOUNLOAD, COMPRESSION, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset 
where database_name=N'Inventory' and backup_set_id=(select max(backup_set_id) 
from msdb..backupset where database_name=N'Inventory' )
if @backupSetId is null 
begin raiserror(N'Verify failed. Backup information for database ''Inventory'' not found.', 16, 1) 
end
RESTORE VERIFYONLY FROM DISK = N'C:\Backup\FullBackup_Inventory.bak' 
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Also you can simply take Full backup as below query:

BACKUP DATABASE [Inventory] TO  DISK = 'C:\Backup\FullBackup_Inventory.bak' WITH COMPRESSION

Differential Backup

What is differential backup? Over all changes in database transaction which happened after last Full backup is called differential and those backups are called Differential Backups. Differential files are nothing but data files only, it is not possible every time to take full backup due to many reasons as size of database, total backup time, cost of data storage and performance impact etc. Steps for taking differential backup is same as mentioned above except you have to select ‘Differential’ in Backup type.

Kindly refer T-SQL as below:

BACKUP DATABASE [Inventory] TO DISK = 'C:\Backup\Differential_Backup_Inventory.bak' 
WITH DIFFERENTIAL
GO

Transaction Log Backup

Transaction log backup maintains log change records, all the changes which happens in logs during user transaction are kept as log records and backup of those records maintained in Transaction log backup. One point to note here is that Transaction log backup is not possible in Simple Recovery model. Steps for Transaction Log backup is same as mentioned earlier for full backup except you have to set ‘Transaction Log’ in Backup types.

BACKUP LOG [Inventory] TO DISK = 'C:\Backup\TranLog_Backup_Inventory.trn' 
GO

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

You may also like...

Leave a Reply