BACKUPS
KDSSG B46 Level001 036 15Mar2024 Backups 1
FULL BACKUP SCRIPT: A full database backup backs up the whole database.
Backup database [AUTOQ_UNV] to disk='D:\Backups\AUTOQ_UNV_FULL.BAK'----FULL WE WILL
TAKE EVERY WEEK
DIFF BACKUP SCRIPT: A differential backup is based on the most recent full backup. In other
words, you only can create a differential backup once you have at least one full backup.
BACKUP DATABASE [AUTOQ_UNV] to disk='D:\Backups\AUTOQ_UNV_DIFF.BAK'WITH DIFFERENTIAL--
------ DIFF BACKUP WE WILL TAKE EVERYDAY
LOG BACKUP SCRIPT: A transaction log backup contains a transaction log of a database. Use
the BACKUP LOG statement to back up the transaction logs. Use the RESTORE LOG to
recover the database from the transaction log backups.
BACKUP LOG [AUTOQ_UNV] to disk='D:\Backups\AUTOQ_UNV_DIFF.BAK'-----------LOG WE WILL
TAKE EVERY ONE HOUER
File and file group Backups
KDSSG B46 Level001 037 18Mar2024 Backups 2
For this example I created a new database called TestBackup that has three data files and
one log file. Two data files are the PRIMARY filegroup and one file is in the ReadOnly
filegroup. The code below shows how to do a filegroup backup.
create table DB21 (SINO int,name varchar(10),age bit)on DB21
create table DB22 (SINO int,name varchar(10),age bit)on DB22
create table DB23 (SINO int,name varchar(10),age bit)on DB23
create table DB24 (SINO int,name varchar(10),age bit)on DB24
insert into DB21 values(21,'Durga',21)
insert into DB22 values(22,'Prasad',22)
insert into DB23 values(23,'Katta',23)
insert into DB24 values(24,'KDP',24)
select*from [dbo].[DB24]
BACKUP DATABASE [DB2] FILEGROUP = N'PRIMARY' TO DISK = N'D:\Backups\DB2_Primary.bak'
BACKUP DATABASE [DB2] FILEGROUP = N'DB21' TO DISK = N'D:\Backups\DB21.bak'
BACKUP DATABASE [DB2] FILEGROUP = N'DB22' TO DISK = N'D:\Backups\DB22.bak'
BACKUP DATABASE [DB2] FILEGROUP = N'DB23' TO DISK = N'D:\Backups\DB23.bak'
BACKUP DATABASE [DB2] FILEGROUP = N'DB24' TO DISK = N'D:\Backups\DB24.bak'
BACKUP DATABASE [DB2] TO DISK = N'D:\Backups\DB24_FULL.bak'
BACKUP DATABASE [DB2] FILEGROUP = N'DB21' TO DISK = N'D:\Backups\DB21.bak'with
differential
Split Backup
Split SQL database backups into multiple backup files in different locations using SSMS
backup database DB3 to disk='D:\Backups\DB3_Full-1.bak',
disk='D:\Backups\DB3_Full-2.bak',
disk='D:\Backups\DB3_Full-3.bak',
disk='D:\Backups\DB3_Full-4.bak',
disk='D:\Backups\DB3_Full-5.bak'
Mirror backups
Mirrored backups are actually identical copies of the same backup. Administrators usually
store them on multiple backup devices,
backup database DB3 to
disk='D:\Backups\DB3_Full.bak',
disk='D:\Backups\DB3_Full1.bak'
mirror to disk='E:\BACKUPS\DB3_FULL.bak',
disk='E:\BACKUPS\DB3_FULL1.bak'
with format
COPY-ONLY BACKUP KDSSG B46 Level001 038 19Mar2024
Backups 3
FULL copy only backup: A copy-only backup can't serve as a differential base or
differential backup and doesn't affect the differential base.
WITHOUT COPY-ONLY FULL BACKUPS
backup database DB4 to disk='D:\Backups\DB4_Full.bak'
backup database DB4 to disk='D:\Backups\DB4_DIFF_mon.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_DIFF_tues.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_DIFF_wed.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_Full_Adhoc.bak'
backup database DB4 to disk='D:\Backups\DB4_DIFF_fri.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_DIFF_sat.bak'with differential
restore headeronly from disk='D:\Backups\DB4_Full.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_mon.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_tues.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_wed.bak'
restore headeronly from disk='D:\Backups\DB4_Full_Adhoc.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_fri.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_sat.bak'
WITH COPY-ONLY FULL BACKUPS
backup database DB4 to disk='D:\Backups\DB4_Full.bak'
backup database DB4 to disk='D:\Backups\DB4_DIFF_mon.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_DIFF_tues.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_DIFF_wed.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_Full_Adhoc.bak'with copy_only
backup database DB4 to disk='D:\Backups\DB4_DIFF_fri.bak'with differential
backup database DB4 to disk='D:\Backups\DB4_DIFF_sat.bak'with differential
restore headeronly from disk='D:\Backups\DB4_Full.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_mon.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_tues.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_wed.bak'
restore headeronly from disk='D:\Backups\DB4_Full_Adhoc.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_fri.bak'
restore headeronly from disk='D:\Backups\DB4_DIFF_sat.bak'
Log copy only backup: A copy-only log backup preserves the existing log archive point
and, therefore, doesn't affect the sequencing of regular log backups.
WITHOUT COPY_ONLY TLOG:
create database DB4
use DB4
create table DB4(Sino int,name varchar(10),age bit)
insert into DB4 values(22,'Durga',22)
Backup database DB4 to disk='D:\Backups\DB4_FULL.bak'
insert into DB4 values(22,'Prasad',22)
Backup log DB4 to disk='D:\Backups\DB4_log1.bak'
insert into DB4 values(22,'Katta',22)
Backup log DB4 to disk='D:\Backups\DB4_log2.bak'
insert into DB4 values(22,'KDP',22)
Backup log DB4 to disk='D:\Backups\DB4_log3.bak'
insert into DB4 values(22,'Babu',22)
Backup log DB4 to disk='D:\Backups\DB4_log_Adhoc.bak' --without copy_only log backup
insert into DB4 values(22,'Raju',22)
Backup log DB4 to disk='D:\Backups\DB4_log4.bak'
restore headeronly from disk='D:\Backups\DB4_FULL.bak'
restore headeronly from disk='D:\Backups\DB4_log1.bak'
restore headeronly from disk='D:\Backups\DB4_log2.bak'
restore headeronly from disk='D:\Backups\DB4_log3.bak'
restore headeronly from disk='D:\Backups\DB4_log_Adhoc.bak'
restore headeronly from disk='D:\Backups\DB4_log4.bak'
WITH COPY-ONLY TLOG:
create database DB4
use DB4
create table DB4(Sino int,name varchar(10),age bit)
insert into DB4 values(22,'Durga',22)
Backup database DB4 to disk='D:\Backups\DB4_FULL.bak'
insert into DB4 values(22,'Prasad',22)
Backup log DB4 to disk='D:\Backups\DB4_log1.bak'
insert into DB4 values(22,'Katta',22)
Backup log DB4 to disk='D:\Backups\DB4_log2.bak'
insert into DB4 values(22,'KDP',22)
Backup log DB4 to disk='D:\Backups\DB4_log3.bak'
insert into DB4 values(22,'Babu',22)
Backup log DB4 to disk='D:\Backups\DB4_log_Adhoc.bak' with copy_only
insert into DB4 values(22,'Raju',22)
Backup log DB4 to disk='D:\Backups\DB4_log4.bak'
restore headeronly from disk='D:\Backups\DB4_FULL.bak'
restore headeronly from disk='D:\Backups\DB4_log1.bak'
restore headeronly from disk='D:\Backups\DB4_log2.bak'
restore headeronly from disk='D:\Backups\DB4_log3.bak'
restore headeronly from disk='D:\Backups\DB4_log_Adhoc.bak'
restore headeronly from disk='D:\Backups\DB4_log4.bak'
Tail Log Backup: KDSSG B46 Level001 039 20Mar2024 Backups 4
Tail log backup is a Tlog backup, that can be attempted during a crash situation. Tail log also
takes backups of active log. Tail log can fail if log file gets corrupted.
Backup Log database to disk='C:\Backups\DB4_Tail.trn'with no_truncate
Partial Backup:
Partial backups are useful whenever we want to exclude read_only filegroups. A partial
does not contain all the filegroups.
Commend:
Backup database DB4
READ_WRITE_FLIEGROUPS, FLIEGROUP='DB22'
TO DISK='C:\Backups\DB4_Pbak.bak'
Partial Differential Backup:
Is a differential backup for partial backup?
Commend:
Backup database DB4
READ_WRITE_FLIEGROUPS
TO DISK='C:\Backups\DB4_Pbak.bak'
with differential
KDSSG B46 Level001 040 21Mar2024 Backups 5
Restore FilelistOnly: SQL Server documentation tells us that Restore
FilelistOnly "… returns a result set containing a list of the database and log files
contained in the backup set in SQL Server." This is particularly useful if we need to
use the MOVE clause with the restore command or we need to replicate the drive
structure of the source instance at the destination.
backup database [DB4] to disk='C:\Backups\DB4_FUll.bak'
restore filelistonly from disk='C:\Backups\DB4_FUll.bak'
The RESTORE VERIFYONLY statement, combined with the WITH CHECKSUM
clause, will also add a check that the backup set is complete and the header fields of
the database pages are restorable and also that there is sufficient space in the
restore path for the restored database.
restore verifyonly from disk='C:\Backups\DB4_FUll.bak'
The RESTORE HEADERONLY option allows you to see the backup header
information for all backups for a particular backup.
restore headeronly from disk='C:\Backups\DB4_FUll.bak'
The RESTORE LABELONLY option allows you to see the backup media information for the
backup device. So if a backup device, such as a backup file.
SQL server backup buffer method
The BACKUPBUFFER wait type is generated whenever there is a backup
operation happening in an SQL Server instance. When a backup runs, the SQL
Server allocates a buffer for the backup process. The wait type will occur
regardless of any backup method used. It can be a full, differential, or log backup.
How many Backup buffers= 7 buffers
How many Restore buffers= 6 buffers
What is buffer size=1mb
Commend:
backup database [DB4] to disk='C:\Backups\DB4_FUll.bak'
with buffercount=14,maxtransfersize=4194304
Trace flags in sql server
Enable traceflag 3213 to get actual backup parameters SQL server internally choses to
complete backup operation.
Commend:
DBCC traceon (3213) i am only able to see
DBCC TRACEON (3213,-1) everyone can see
DBCC TRACEON (3604,-1)
DBCC TRACEOFF (3604,-1)
DBCC tracestatus (we can see trace flag status)
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-
transact-sql?view=sql-server-ver16
