Tag Archives: MSSQL DB Backup scripts

Database Backup-FULL-DIFFERENTIAL-TLOG

MS SQL – Database Backup

Table of Contents
___________________________________________________________________________________________________

1. What is Full Database Backup

2. What is Differential Database Backup

3. What is Transaction Log Backup

4. Verify Recovery Model for Database

5. How to Take Full Database Backup

a) Using SSMS (GUI)
b) Using T-SQL

6. How to Take Differential Database Backup

a) Using SSMS (GUI)
b) Using T-SQL

7. How to Take Transaction Log Backup (T-LOG)

a) Using SSMS (GUI)
b) Using T-SQL

8. List Backup

9. Validate Backup – RESTORE VERIFYONLY

10. Check contents of backup – RESTORE HEADERONLY

___________________________________________________________________________________________________


1. How to Take Full Database Backup

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

Syntax:

BACKUP DATABASE [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TEST.bak' WITH NAME = N'FULL DATABASE BACKUP - DBNAME',STATS 
GO


2. What is Differential Database Backup

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

Syntax:

BACKUP DATABASE [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TEST.bak' WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP DBNAME DATABASE',STATS 
GO


3. What is Transaction Log Backup

It takes complete transaction log file 

Syntax:

BACKUP LOG [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TLOG.bak' WITH NAME= N'T-LOG BACKUP FOR DATABASE DBNAME',STATS 
GO


4. Verify Recovery Model for Database

SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases
GO



5. How to Take Full Database Backup

a) Using SSMS (GUI)

      


b) Using T-SQL

BACKUP DATABASE [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak' WITH NAME = N'FULL DATABASE BACKUP - TEST',STATS 
GO




6. How to Take Differential Database Backup

a) Using SSMS (GUI)










b) Using T-SQL

BACKUP DATABASE [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak' WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP TEST DATABASE',STATS 
GO




7. How to Take Transaction Log Backup (T-LOG)

a) Using SSMS (GUI)















b) Using T-SQL

BACKUP LOG [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak' WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS 
GO




8. List Backup

-- List all database backups from last week.

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 




9. Validate Backup – RESTORE VERIFYONLY

RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak'; 
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak'; 


10. Check contents of backup – RESTORE HEADERONLY

RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak'; 
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak'; 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/