Backups and Recovery Options on SQL server

Backup Types

There are 5 backup types. All these backups can be carried out online. databases. Offline backups require SQLSERVER be stopped. Offline backups are useful for the master, msdb and model databases

1. Full : This takes a complete copy of the database and logs.

2. Differential : This takes a copy of all changed pages since the last full backup (for database and logs.)

3. Transaction Log : This takes a copy of the transactions logs and clears its contents on completion.

4. File or File group : This copies only the specified file or group.

5. File differential : This takes a copy of all changed pages in file since the last file backup. In situations when the backup is too big for a backup window.

Backup Options :

BACKUP LOG – by default, it will backup and clear logs.
BACKUP LOG with NO TRUNCATE – just does backup
BACKUP LOG with TRUNCATE_ONLY – clears the logs without taking backup. (this can be done in T-SQL only)

Recovery Models

There are three choices
FULL : This is the default option for standard and enterprise editions and the preferred option for production databases. It also has the least risk of losing work if a database file is damaged.

It supports recovery to any point in time, including point of failure unless the current transaction file is damaged. It completely logs bulk operations and index creation. The actual index creation is logged, not only CREATE INDEX. It makes restore much faster. The transaction logs tend to be enormous and therefore making log backups can take longer than with any previous release.A backup of the log will be required even if the data files are damaged.

SIMPLE : This is not recommended on production databases as the log is deleted every 3 minutes.

BULK-LOGGED : It allows bulk copy operations, it logs the fact that the bulk operation occurred. The SQLserver course instructor described this as hell so beware if this option is chosen

Minimal log space used for bulk operations, however it runs the risk of losing the latest transaction as it works in 64K chunks). When you backup the transaction log in the BULK_LOGGED model, it scans the database and copied all the modified extents to the file. It takes more time to back up and the log backup file can be huge.

It’s a good idea to make a transaction log backup after bulk operations.

Backing up system databases

Use the same tools that you use for user databases.

master

– default is simple recovery model.
– No reason to change.
– Full backups of master are appropriate since it’s not a transactional database

backup master when you
– create or delete databases
– add login or make login security-related changes
– change server or database configuration options

model

– no need to backup unless you have created objects in it.
– Rebuild of master recreates model in its initial state

msdb

– default is simple recovery model
– No reason to change

Backup msdb when you
– Schedule tasks
– Save DTS packages
– Configure replication
– rebuild of master destroys msdb
– backup the at the same time as master

Recovery scenarios

What you do after a crash depends on
– what is damaged
– what recovery model you are using

SIMPLE recovery model

– When the transaction log only is damaged,

1. Restart server, a new transaction log will be created, the new log will be 25% of the database size,
2. Expand if you need more space.

– Database file(s) damaged
1. Restore most recent full backup
2. Restore most recent differential backup, if any

FULL or BULK-LOGGED recovery model

When the transaction log only is damaged,
1. Restore latest full backup
2. Restore latest differential backup, if any
3. Restore all transaction logs since the last full or differential in sequence.
Note that changes since the last transaction log backup will be lost. If the data loss it too huge, try sp-attch to attach the database file with a log, it will create a new blank log.

Database file(s) damaged
1. Backup current transaction log with NO-TRUNCATE.
2. Restore latest full backup.
3. Restore latest differential backup, if any
4. Restore all transaction log since last full or differential in sequence.
5. Database will be current up to the point of failure or the last transaction log backup.

Restore Options

1. FILE

– Number of the backup to be restored
– Backups are numbered sequentially in the file
– Find out what files are on the backup device with
RESTORE HEADERONLY FROM <backupdevice>

2. REPLACE

Overwrite an existing database with a backup made from a different database.

3. RESTART

Allows SQLserver to restart a restore from tape.

NORECOVERY

Use this option on all transaction logs except the last transaction log backup. For example:
RESTORE DATABASE <dbname> FROM DISK = <full path> WITH FILE = 1, NORECOVERY
RESTORE DATABASE <dbname> FROM DISK = <full path> WITH FILE = 2, NORECOVERY
RESTORE DATABASE <dbname> FROM DISK = <full path> WTH FILE =3, NORECOVERY
RESTORE DATABASE <dbname> FROM DISK = <full path> WITH FILE = 4