I’m going to discuss one important job of Database administrators, that is Disaster management and recovery. This is a critical aspect of the life of a database because there are lot of things that can cause a database to cease to exist. For example, a hacker can inject a SQL code into your front end application that can drop/delete the entire database. There are lot of security issues when it comes to data and access. To prepare for the unforeseeable future, the Database administrator must find ways to make sure there is always a means of restoring the database to normal when it becomes corrupt or it ceased to exist. The most acceptable and widely used technique to accomplish this is the almighty database backup and restore procedures that comes with a database system. As the word implies, backup is simply keeping a copy of the database and restoring it means restoring the original database from the copy. This is done to make the data highly available. Have you heard of MTBF? It means mean time before failure, it states that after every 40,000 hours, there is something bad going to happen to our hardware. So that’s just how life is when it comes to hardware, software and life itself. So we have to be prepared for this failure when it comes to a database as well. So what makes up the database itself? its just a file with the .mdf extension and it has another file called the transaction log that keeps all actions that are performed on the data file. Everything that is done to the data file is written to the transaction file before the action is carried to the data file. To make things work, there are recovery models which defines how the back up is done. There are 3 recovery models allowed by Microsoft SQL Server;
Simple Recovery Model: In this model, there is only full backup of the database. The transaction log is not backed up. That means, every time we plan to do our back up, we’ll just go ahead and back up the entire database. So, if we do our backup on every Monday, then if there is a crash to our database on Tuesday, we will not be able to get the data after the Monday backup to Tuesday. This recovery model is easy to maintain because we are only backing up to a single file and we can restore the database faster but it will lead to data loss.
Full Recovery Model: In this model, there is no data loss. All the back up types are supported and every piece of data can be recovered. I’ll be talking about the back up types in a minute.
Bulk Logged Model: In this recovery model, there is minimal logging done, that means, transactions are logged at very high level. For example, if you have to bulk insert from a file containing millions of records, you really do not want to log every single record that is going to be inserted into the database in the transaction log. This will make the transaction log very huge so to prevent that from happening, you can switch the database recovery model to bulk logged model before performing bulk operations so that the transaction log will log only important stuff from the bulk operation such as creating indexes, stored procedures etc.
So those are the 3 Recovery models available in Microsoft SQL Server. Now, lets take a look at the types of back ups we can do. Note that, base on the recovery model of the database, you can only perform some of the back up types. To find out the recovery model that the database is using, go into management studio, right click on the database and go to the properties window. Click on options in the left pane and you will see the recovery model being used by the database. You can switch the recovery model using the drop down arrow and selecting a different one. There are 4 major back up types;
Full Backup: Everything is backup up. It takes a long time because it backs up everything in the database.
Differential Backup : Only changes after the full back up is performed are backed up. That means, you have to perform at least one Full back up before doing the differential back up. So lets say we do our full back up on Mondays, then on Wednesday, we do our first Differential backup. This will back up only changes from Monday to Wednesday. Let say we do another differential back up on Friday, this will back up all changes from Monday to Friday. Lets say we do another differential back up on Sunday, this will back up all changes from Monday to Sunday. Let say now, on Monday, we do our full back up again. Then, the differential backup will totally be gone because, we have all our data in the full back up. Then the process continues again.
Transaction Log Backup : It takes everything in the transaction log and back it up. After backing it up, the transaction log is truncated. This is the only back up that will automatically truncate the transaction log back up. All the other mentioned back ups will not automatically truncate the transaction log but you can specifically truncate the transaction log after doing any of the other backups. If you do not keep the transaction log clean, it will grow to become a monster and this may bog down the system so its good to keep it neat and clean when necessary.
Filegroup backup: This is backing up specific files in a file group. The data in a database are stored in filegroups, just like folder on a windows pc. So you can specifically backup a particular filegroup. This type of backup is used mostly in VLDB(Very Large Database), which contains humongous amount of data in terabytes or exabytes. So the idea is, you do not want to be backing up the entire monster at a time so you chose to back up specific filegroup at a time.
Now lets look at some back up strategies you can use to keep your data secure and available. There are lot of these strategies but lets discuss just the common ones that l know.
Full only: In this strategy, you do only full back ups. For example, you can do full backup every other day. The transaction log is never truncated and the back up is really slow. This is good for small databases that are not mission critical. Those databases that can allow little data loss. The restore process is very fast because we have only one file to restore from. Its easy to manage.
Full Back up + Differential Backup : In this strategy, we do the full back up and then do only differential back ups, we continue doing differential backup until it’s time to do another full back up. This is good for medium to large databases. There is fast backups because we’ll only be backing up the differences each time after a full back up. The disadvantage to this is the transaction log is not truncated too and the restore process is slow because we have to restore the database from two files. First restore the full back up, then the differential back up.
I’ve been saying the transaction log is not truncated automatically when you do full back up or differential back up, but there is an option you can turn on to truncate the transaction each time you full back up or differential backup. You can use the option TRUNCATE ONLY for those back ups to get the transaction log truncated after the back up. The reason why the transaction log is not automatically truncated when you do the full back up or differential back up is that, if something went wrong, the DBA can quickly log into the server and back up the transaction log in order to recover the database. But it will be nice to clear it out after a full back up because, now, everything is in the full back up file. There is an option at the database level that can actually clear the transaction log file every time after writing to the data file but that is so not advisable. This option is called truncate log on checkpoint and when it is turned on, it clears the transaction log after every write to the data file. It will become an overhead and you might even lose data when something went wrong. Now, because every time you do a transaction log back up, the transaction log is truncated, you can also use an option to back up the transaction log without truncating it. That is NO TRUNCATE option. These are all available if you are using the full recovery model for the database.
Full Back up + Transaction Log Back up : This strategy is good for small to large databases. In this strategy, we do full back ups and after the full back up, there is immediately a transaction log back up which clears the transaction log. In this way, we are keeping the transaction log clean and also we can recover our database any time there is a problem because the full back up have all past records and the transaction log keeps all the changes up to date.We have fast backups, transaction logs truncated but there is a slower restore because we have to restore from the full back up, then from the transaction log back up.
Full Backup + Differential Backup + Transaction Log Backup : This is the best of of it all and the most complicated. The idea is to do the full back ups on regular schedules say every Monday, perform transaction log back ups on regular schedule say after every 4 hours, perform differential backups at their regular schedules say aevery other day. So lets say, we do our full back up on Monday, then we continue doing the transaction log back ups till Wednesday then we do a Differential back up, then continue doing transaction log back up till Thursday evening and a hacker came and delete the database. The DBA will get into the server, first perform a quick transaction back up. Restore the database from the full back up without recovery, restore from Wednesday’s differential back up without recovery, then restore from each transaction log back up after differential backup in order, until the last transaction log to fully recover the database. There is also the Filegroup back up strategy but l’m not talking about that because its more advanced and used mainly for VLDBs. Lets talk about the restore types now.
Standard Restore:
In this type of restore, you simply restore the database from the back ups available. Use the NO RECOVERY option when you are restoring from multiple back ups, and use RECOVERY when you are restoring the last backup or restoring from a single backup file. You can also use the MOVE option when you are restoring the database to another physical location. What that means is that, lets say you back up the database on the server at work, but now, you are trying to restore the database to your home computer, maybe you want to keep a copy of that database and work on it at home. Because when you back up the database, the SQL server writes the physical path of the database at the header of the backup file, any time you try to restore, it will try to restore to that physical location. So if you specify a different path, the Server will yell at you, to avoid that yelling, use the MOVE option to tell the server you are restoring the database to a different physical location and there will be peace.
Point -In-Time Restores:
This is typical to transaction logs. You can restore a transaction log to a specific point in time. Say, you have the transaction log that spans from 1pm to 6pm but you want to restore to only 3pm. You can use the STOPAT option to tell the server to restore to that time. But be careful, when you use the STOPAT option, everything after the time you specified will be erased from the transaction log back up. That means all log information from 3p to 6pm in our example above will be erased. You can also use marking to restore to a point. For example, lets say you have a store procedure that is so long like from California to New York lol. You can create marks in the store procedure and when restoring from the transaction log, you can tell the transaction log to restore to one of those marks.
Piece Meal Restore:
I’m not serving any food here, so if you just got hungry by hearing that name, l’m sorry but we are closed lol. Ok, this Restore type is used to restore filegroups. So what you do is restore the primary filegroup, optionally restore the secondary filegroup after the primary filegroup is restored. This is meant for restoring part of the database and can be done online. That means the database can still be accessed by users when this restore is going on but the users are not going to be accessing this filegroup being restored, they can access other parts of the database. For all other types of restores, the database will not be available when the restore is going on.
Now lets look at simple codes for back up and restore. You can decide to create restore devices that you can use for your back ups either using TSQL or management studio or simply specify the path to back up the database to. Back up devices are stored under server objects. So you can use the management studio to quickly create one or use a system stored procedure, SP_ADDUMPDEVICE. Now lets see code for the back ups. Remember the scope of a backup of data can be a whole database, or a set of files or filegroups.
TYPES
–Full Backup
A full backup contains all the data in a specific database or set of filegroups and also log information
It captures all the data that is stored in the database.
The backup engine extracts every extent in the database that is allowed to an object.
Syntax
BACKUP DATABASE <db_name> TO DISK = ‘<PATH>’
WITH NAME = <name of the file>
–Differential backup
A differential back is based on lastest full backup of the data. differential backups is faster and can speed up the process
of making frequent backup.
BACKUP DATABASE <db_name> TO DISK = ‘<PATH>’ WITH DIFFERENTIAL
WITH NAME =<name of the file>
–TRANSACTIONAL LOG BACKUP
Before you can create the first log backup, you must create a full backup. Each log backup covers the past 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.
BACKUP LOG <db_name> TO DISK = ‘<path>’
WITH NAME <name of the file>
FILEGROUP BACKUP
BACKUP DATABASE <db_name> FILEGROUP = <filegroup type(Primary/secondary)> TO <path>
WITH NAME = <name of the file>
Restoring the Database
Restore database <db_name> from disk = ‘path’ with norecovery–apply full back up
Restore database <db_name> from disk = ‘path’ with norecovery–apply differentail
Restore log <db_name> from disk = ‘path’ with recovery
Restoring from filegroup
RESTORE DATABASE <db_name>
FILEGROUP = <filegroup name> FROM <path> WITH <recovery/no recovery>
Restore a transaction log to a point in time
RESTORE LOG <db_name> FROM <path>
STOPAT = <date and time to restore to>
Summary
RECOVERY MODEL
It defines how the transactions are logged and whether the backup of transaction log is allowed.
Simple– No log backups.
So changes since last data backups are lost in case of failures.
Full:- Can restore all the data, logs can be backed up immediately after the back up.
Bulk logged- Allows back ups but does logging of bulk operations only.
Bulk operations are select into, bulk insert, truncate. It won’t log delete, insert (row by row), update.
I hope this helps. In the next tutorial, we’ll talk a look at Index Maintenance
