SQL Server Backup and Restore

SQL Server Backup and Restore

Backup is the most important process to protect your database from unintentional/intentional users behavior – user may delete all data from a table accidentally or update it – if one of your
disk driver fails, and sometimes you need a version of your operational data on test server, etc.

Recovery Modes

SQL Server provide three recovery mode, any one will provide a recovery method against failures, to decide which recovery model you should use first you have to understand your environment and your needs for recoverability of your database e.g. if you use full recovery mode you need more desk space than if you use Bulk-logged or simple recover mode. And also if you need point in time recovery simple and bulk-logged recovery won’t help.

Types of backup you may run against your database will be determined by the recovery mode you need e.g. if your database in simple recovery mode you can’t perform transaction log backup.

·         Full Recovery mode:

In full recovery mode all transactions that run against database will be recorded in the transaction log file (.ldf) and the transaction log won’t be truncated even if the transaction has been committed nor full backup has been performed.

Full recovery mode provide restore to the point of failure, if there is a failure in your database the SQL server when restart against it will redo all committed transaction and undo all uncommitted transactions.

Full recovery mode support point-in-time recovery.

·         Bulk-logged Recovery mode:

In bulk logged most bulk operation, index creation will be minimally logged, this will help to minimize size of the log file.

This type of recovery frequently use when there is a full recovery mode and you have bulk operation and you want to minimize the log operation you will switch form full recovery to bulk logged. This operation will increase performance and minimize the log file size because of the minimum log operation.

Bulk-logged doesn’t support point-in-time recovery.

·         Simple Recover Mode:

Simple recovery mode write all active transactions to transaction log file (.ldf) and after the transaction committed it will remove the transaction for transaction log file this will minimize the administrative effort for transaction log file and the log file will be small any won’t have many growth.

Simple recovery mode doesn’t support point-in-time recovery.

Which recovery mode should I use?

There is no correct or wrong answer for this question. All you can say it depends.

If you have a lot of concurrent user using your database and your database is very critical and you need point-in-time recovery there is no solution other than full recovery mode.

If you have limited resources and the point-in-time recovery isn’t important for you go ahead with simple recovery mode.

Backup Types

·         Full Backup

Full backup contains all data till the time the full backup was performed.

Every time you run full backup your whole database will be backed up.

Full backup is considered the base for all other backup types.

·         Differential Backup

Differential backup (also called incremental backup) contains all data since the last full backup.

e.g if you have a full backup on Friday (last full backup) and you perform differential backup on Saturday your full backup contains whole database till Friday but your differential backup contains all data between Friday and Saturday.

And if you run another differential backup on Sunday this backup contains data between Friday and Sunday.

When you perform full backup against your database SQL Server put mark to indicate which data has been backed up and your differential backup will backup for this mark till the time the differential backup has been performed.

·         Copy-only backup

Copy-only backup similar to full backup except it doesn’t mark backed up data.

If you perform full backup on Friday, copy-only backup on Saturday, and differential backup on Sunday your differential backup will contain data between Friday till Sunday.

·         Transaction Log Backup

Transaction log backup is a process of backing up the transaction log which contains log of all transactions run against the database you can truncate the file at the end of the backup or not, but I believe it is better to backup transaction log with truncate (it is the default option) this will help you to maintain your transaction log file size and also you can get small size of log backup file and this also affect your restore time and the point to restore. For instance if you schedule a backup of transaction log file each five minutes this mean you can lose not more than five minutes in case of failure in your system.

The database should be in a full recovery model or bulk-logged recovery mode.

Database restore

It always depends on your database and the acceptable time of data loses and time to recovery. The small time interval backup means you need more space to store these files e.g. if you do a daily full backup (e.g. 100 GB) and differential backup (2 GB) each four hours and hourly transaction log backup (1 GB) this means you need more space than if you have a weekly full backup and daily differential backup and hourly transaction log backup and you have data lost of one hour in each cases but the time to restore is different the second configuration need more time to restore than the first one.

To restore each database first you should have a full backup of database the full backup is the base backup to restore your database.

If your database in full recovery mode you should first backup the tail log (run a transaction log backup) and then restore your full backup, last differential backup, all transaction log backups since the last differential backup and finally the tail log backup.

For example if you have

Backup Type	Frequency	Start Time of backup
Full	Daily	11:00 PM
Differential	Each for hour	1:30 AM
Transaction log	Hourly

If your database fails on 10:59 AM on Sunday to restore your database:

·         First you should backup the tail of the log.

·         Second restore the full backup taken on Saturday at 11:00 PM with no recovery option.

·         Third restore the last differential backup (it will be the backup taken at 9:00 AM on Sunday) with no recovery option.

·         Forth restore the transaction log since the last differential backup (transaction log taken after 9:00 AM on Sunday) it will be the transaction log taken at 9:30 and 10:30 and restore it with no recovery option.

·         Fifth restore the tail of the log (look at First point) and restore it with recovery.

Notice we only lose about 29 minute of data the data between the last transaction log backup and the failure and if our database fails at 11:29 we also can only restore to 10:30 because this is the last transaction log backup and we lose about an hour of data.

The with no recovery option is used to put the database in the restoring state and it will prevent the database from being accessed by the user and the you will restore all of your file and after that make the database available to the user and recover it.

Please refer to Microsoft online book and see more details about how to do this and you can google to fine more scenarios available.

Written by Elmozamil Elamir