DBA Hub

📋Steps in this guide1/10

MS SQL – Database Backup

MS SQL – Database Backup

sqlserver backupintermediate
by MSSQL
17 views
1

Overview

MS SQL – Database Backup 1. How to Take Full Database Backup

Code/Command (click line numbers to comment):

1
2
3
4
5
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

Section 2

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

Code/Command (click line numbers to comment):

1
2
3
4
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

Section 3

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

Code/Command (click line numbers to comment):

1
2
3
4
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

Section 4

4. Verify Recovery Model for Database 5. How to Take Full Database Backup a) Using SSMS (GUI) 5. How to Take Full Database Backup a) Using SSMS (GUI)
Step 4

Code/Command (click line numbers to comment):

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

Section 5

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) 6. How to Take Differential Database Backup
Step 5

Code/Command (click line numbers to comment):

1
2
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

Section 6

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)
Step 6

Code/Command (click line numbers to comment):

1
2
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

Section 7

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
Step 7

Code/Command (click line numbers to comment):

1
2
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

Section 8

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
Step 8

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 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

Section 9

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
Step 9

Code/Command (click line numbers to comment):

1
2
3
4
5
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';

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';
10

Section 10

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. 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.
Step 10

Comments (0)

Please to add comments

No comments yet. Be the first to comment!