Types of SQL backup with details on their procedures
There are several methods by which SQL Backups can be created. These can include a complete set or a few data with some logs created for transaction. SQL backup methods are mostly the area of interest of Database Administrators (DBA) and System Administrators (SA).
SQL Servers are the repository used for storing data needed by several applications. These applications stores data in the repository and sometimes a situation may arise, the data storage may get failed and a backup is required. There are different techniques that SQL offers to create backup. Several methods of back up are described below; these can guide you to create different backups for your system.
To create a backup it is necessary to define a backup plan. A database recovery model is required for the business needs of any system. A recovery model will define the way in which the transaction log data will be used. Basically, there are three (3) recovery models available, these are Full, Simple and Bulk logged.
Simple Recovery Mode– In simple recovery mode only backup is created. Full backup or differential are the only backups that can be created in this mode and the backup for the transaction log in this mode is not created.
Full Recovery Mode– This mode provides back up for the transaction log and database that can provide recovered data from the database whenever required. The data can be recovered at any time.
Bulk Logged Recovery Model– There are limitations in the bulk logged mode that provides back up for the transactions which are stocked up in the transaction log. The index creation and bulk loads are the bulk operations which are not supported in this mode. These are not logged in the bulk logged mode.
Among these methods Simple and Full modes are the most widely used techniques used for backups. It is not mandatory to use the Full recovery mode at all times to protect the data from any disaster. It simply depends on the business decision. The business needs will make it clear whether back up is required at any instance or a full back up is required. The business needs will also clearly specify the type of back up required protecting the data and recovery of data. It will specify the type of backup plan required whether it will be simple, full or bulk mode of recovery. With these two (2) plans: the simple and full recovery models, data can be copied to the backup file without affecting the log; and differential backup can be scheduled or impact recovery can be done at any point in time.
The following will describe on how to run backups. This example will show how to run Simple recovery backups for small and midsized databases. For running a simple backup a simple script named as SampleScript is used in below example. Use the following query.
ALTER DATABASE SampleScript SET RECOVERY SIMPLE
This query will run simple database backup at regular intervals. This will provide a full backup of the database.
BACKUP DATABASE SampleScript
TO DISK = ‘C: Backups SampleScript.BAK’
To create a copy of the backup following query should be run.
BACKUP DATABASE SampleScript
TO DISK = ‘C: Backups SampleScript.bak’
WITH COPY_ONLY;
For differential backups
BACKUP DATABASE SampleScript
TO DISK = ‘C: backups SampleScript.bak’
WITH DIFFERENTIAL;
For Full recovery and Log backups
ALTER DATABASE SampleScript SET RECOVERY FULL
BACKUP LOG SampleScript
TO DISK = ‘C: backups SampleScript _Log.bak’;
