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
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)
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)
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)
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
-- 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/