Full Backup
A full database backup backs up the whole database. It includes some part of the
transactional log so that you could restore your database to the point when the
full backup was finished. Usually, files with full database backup have ‘.bak’
extension. It is recommended to periodically create full backups, but since it
contains transaction log along with whole database data it takes significant
space. To create full backup use the following command:
BACKUP DATABASE Adventureworks TO DISK = 'adventureworks.bak'
Full Database backup internals
1. Force a database checkpoint and
make a note of the log sequence number at this point. This flushes all
updated-in-memory pages to disk before anything is read by the backup to help
minimize the amount of work the recovery part of restore has to do.
2. Start reading from the data
files in the database.
3. Stop reading from the data files
and make a note of the log sequence number of the start of the oldest active
transaction at that point
4.
Read
as much transaction log as is necessary.
Backup Start LSN: This is basically
"Checkpoint LSN"( As mentioned by Paul Randal in Understanding SQL Server Backups) this is recorded when backup starts and checkpoint is
fired.
Backup End LSN: This
is basically reading the transaction log again and recording 2 things
1.
The LSN of oldest active
transaction.
2.
The LSN when backup operation
started, For this I am not sure whether this happens or not IMHO this should be
same as Checkpoint LSN or Backup start LSN but may be SQL Server is doing this
2nd time to confirm. I believe it does only what I have mentioned in point 1.
Minimum LSN: The
would be minimum of ( LSN of oldest active transaction, Checkpoint/backup start
LSN ). The definition in the BOOK is little bit confusing.
Differential Backup
A differential backup contains only the data that
has been changed since the last full database backup was created. Creating
differential backup usually takes less time than a full backup, because you
back up only modified data instead of backing up everything.
SYNTAX: BACKUP DATABASE TestDB TO DISK= ‘PATCH’ WITH DIFFERENTIAL;
GO
Transaction Log Backup
A transaction log (T-log) backup is the most
granular backup type in SQL Server because it backs up the transaction log
which only contains the modifications made to the SQL Server database since the
last transaction log backup. It’s effectively an incremental backup.
Transaction Log
Backup
Backup
log <Your database name> to disk = '<Backup file location + file
name>'
SQL Server Backup Options
Backup compression
In addition, it
is possible to compress your backup using the option WITH COMPRESSION. This
option will compress your backup:
BACKUP DATABASE Adventureworks TO DISK = 'c:\backup\full.bak' WITH FORMAT, COMPRESSION
Copy-Only Backup
Use the COPY_ONLY option if you need to make additional full or transaction log
backups which will occur beyond the regular sequence of SQL Server backups. To
perform copy-only backup simply add the “COPY_ONLY” clause:
BACKUP DATABASE Adventureworks TO DISK = 'full.bak' WITH COPY_ONLY
File and Filegroup Backups
These backup
types allow you to backup one or more database
files or filegroups. To execute file
backup use the following command:
BACKUP DATABASE Adventureworks FILE = 'File' TO DISK = 'File.bck'
Use this command
to perform filegroup backup:
BACKUP DATABASE Adventureworks FILEGROUP = 'Group' TO DISK = 'Group.bck'
Partial Database Backup
Typically partial backups are used in a simple
recovery model to make backups of very large
databases that have one or more read-only filegroups. However, SQL Server also
allows making partial backups with full or bulk-logged recovery models. Use the following T-SQL command to create a partial backup:
BACKUP DATABASE Adventureworks READ_WRITE_FILEGROUPS TO DISK = 'partial_backup.bak'
No comments:
Post a Comment