Backup and Recovery

Instead of listing interesting facts about the importance of the backup and recovery process...
I have decided to list the lyrics of my song instead.
​Please see link below for song.

Backup and Recovery (Bits & Bytes)
 
 

{Chorus}
I’m putting your bits where your bytes should be…
 
{Verse 1}
Ooooo back it up – easy back it up – Killa DBA-let me explain to you what’s up.
The backup and restore are really crucial to survival
Minimize the data loss.  Protection is so vital.
 
Corruption, disasters, disgruntled employees.
I’m gonna cover some Backup and restore strategies.
Never put the data file and backup on the same device.
Just incase the drive fails also I/O will prevail.
 
So let’s consider- will the database get bigger?
What’s the nature of the data?  Backup now? Backup later?
How much space will it take?  Should I schedule for tomorrow?
Is this a simple, bulk-logged or a full recovery model?
 
Make sure you test it.  The data is invested
So recovery tests regularly - is suggested.
Maintenance plans help you really understand.
Scheduled jobs in place – to reclaim more space – so Lets go!
 
{Chorus}
I’m putting your bits where your bytes should be…
 
{Verse 2}
 
A full backup gets the data, t-logs, everything
A differential only gets extents that have changed.
The file backup will allow you to pick out the data file.
Filegroups are the same  Backup the groups by name.

Partial backups – with the groups and files you pick and choose.
With Copy-only backups there are no logs to include.
Mirror backups will ensure multiple copies.
Transaction logs for the point in time recovery.
 
Restore it with the GUI or with T-SQL.
Restore it like a boss by cracking open PowerShell.
From which media type? From the file or Device?
From a tape? URL?  Azure storage container?

The WITH option: REPLACE, KEEP_REPLICATION,
RESTRICTED USER to keep out other thread connections
RECOVERY, NO-RECOVERY or STANDBY
Please master this -Or you can kiss your data bye bye!
 
{Chorus}
I’m putting your bits where your bytes should be…
 

 

References: 
​
https://technet.microsoft.com/en-us/library/ms175477(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql