Transcript SQLSaturday_Slide_Deck_Rohanx

Curacao SQL Saturday
June 11, 2016
SQL Server Backups and Restores
What are we going to learn today







Why we need to backup databases?
What are the backup types?
How to backup databases?
How to restore databases?
Tips and Tricks of Backup and restore
Demonstration
Questions
Why we need to backup databases?
• Database architecture helps to keep track of changes.
• Everything in life can fail
• Disaster can happen at anytime
• Backup helps to move the database
• Using backups we initiate other SQL Server disaster
recovery features such as the log shipping,
replication and always on
What are the main backup types?
 Full backup
A full backup contains all the data in a specific database or set of file groups or files, and also enough
log to allow for recovering that data. It is the base of both differential backup and transaction log
backup.
Only a full database backup can be performed on the master database
 Differential backup
A differential backup is not independent and it must be based on the latest full backup of the data. That
means there should have a full backup as a base. A differential backup contains only the data that has
changed since the differential base. Typically, differential backups are smaller and faster to create than
the base of a full backup and also require less disk space to store backup images.
 Transaction Log Backups (Full and Bulk-Logged Recovery Models Only)
The transaction log is a serial record of all the transactions that have been performed against the
database since the transaction log was last backed up. With transaction log backups, you can recover
the database to a specific point in time (for example, prior to entering unwanted data), or to the point of
failure.
The transaction log backups are only valuable under the full recovery model or bulk-logged recovery
model. Each log backup covers the part of the transaction log that was active when the backup was
created, and it includes all log records that were not backed up in a previous log backup. An
uninterrupted sequence of log backups contains the complete log chain of the database, which is said
to be unbroken. Under the full recovery model, and sometimes under the bulk-logged recovery model,
an unbroken log chain lets you to restore the database to any point in time.
How to backup databases
Using the SQL Server GUI- Demo
Using a SQL Server query –Demo
By creating a job or a maintenance plan
Using a backup tool such as Redgate backup pro or
Idera SQL Safe backup
How to restore databases?
 GUI and SQL Query
 Using a SQL job to automate
 Log shipping methodology
Tips and Tricks of Backup and Restore
-Compression
-Restore and backup percentage hint
-Multiple files of backup and restore
-Timestamp restore
-Restore with recovery and no recovery
Demonstration of taking a backup and
restoring it with advance options
1) Using the GUI
2) Using the SQL Query
3) Tips and tricks
4) Restore the backup using no recovery
Questions
My contact info: [email protected]