FAQ's on Backup and Recovery
What are different types of Backups in SQL Server?
In all versions of SQL Server, below are the type of backups that can be performed.
In all versions of SQL Server, below are the type of backups that can be performed.
FULL Database Backups – A Full database backup contains the whole database at the time the backup finished. These backups contains all the data in the databases and active portion of the transaction log file too.
Differential Backups – Differential Backups contains the data extents which has been changed after a Full Backup.
Transaction Log Backup – Transaction Log backups can only be performed on databases whose recovery model is set to either FULL or BULK-LOGGED. Transaction log backups consists of log records inside a transaction log file(.ldf), since last log backup. This allows a databases to be recovered to a point in time, in case of a failure.
Filegroup Backups – Filegroup Backups in SQL Server allows us to backup one or more FILEGROUPS, which contains one or more database files. By default each database has one FileGroup which is PRIMARY filegroup and we can create additional filegroups and add one or more data files to it. If there are Read_Only filegroups, then one can just backup any Read_Write Filegroups instead of backing up the entire database.
Copy-Only backups – Copy-Only backups are special purpose backups which do not disturb regular backup sequence. Usually performing different types of backup affect the way the database is restored, if you do not want a certain backup to change or disturb a restore sequence, then a Copy-Only backup needs to be performed. A copy-only backup cannot be used as a differential base later. Copy-Only can be used with FULL, Differential and Transaction Log backups.
What are Recovery Models in SQL Server?
Recovery Models are part of database options and determines the allowed possible restore sequence. Recovery model basically dictate SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models as mentioned below.
Recovery Models are part of database options and determines the allowed possible restore sequence. Recovery model basically dictate SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models as mentioned below.
Full Recovery Model in SQL Server – SQL Server FULL recovery is very commonly used recovery model allows SQL Server to store all transactions data in the transaction log until a transaction log backup occurs or the transaction log is truncated. Full recovery model is the most complete recovery model which allows us to recover all of the data to any point in time, as long as all backups are available.
SIMPLE recovery model in SQL Server – SIMPLE recovery model is the most basic recovery model in SQL Server where every transaction is still written to the transaction log, but once the transaction is committed and the data has been written to the data file the space used for that transaction can be reused for new transactions. As the log file is getting reused, we cannot perform log backups and thus cannot recover data to point in time. We can only restore an FULL database in case of disaster and causes loss of data. This recovery model is used for databases which are not critical and where data loss is acceptable.
Bulk-Logged Recovery Model in SQL Server – In Bulk-Logged Recovery Model all transactions are logged similar to FULL recovery model except for transactions which involve bulk operations like BULK INSERT, CREATE INDEX, SELECT INTO, etc. These operations are not fully logged in the transaction log and therefore do not take as much space in the transaction log, but does not allow point-in-time recovery of database during the time when there are bulk transactions. This recovery model is used if there are regular bulk operations which happen on a database.
How to plan a backup strategy in SQL Server?
There are various types of backup options available in SQL Server and as a DBA we need to plan what backups need to be performed and how frequently those backups need to be performed.
There are various types of backup options available in SQL Server and as a DBA we need to plan what backups need to be performed and how frequently those backups need to be performed.
The planning for database backups change based on server to server, or can be different in different environments depending upon Size of database, importance of data, amount of allowed data loss and available storage to store the backups.
Example1:
If the database is small in size and is not very critical and data loss of 24 hours is acceptable, then below would be the backup plan
If the database is small in size and is not very critical and data loss of 24 hours is acceptable, then below would be the backup plan
– Set the database to SIMPLE recover model.
– Perform daily FULL Backups to local disk during the night.
– Copy the daily backups from local disk to Backup Server or Tape.
– At all times, store atleast 3 days of FULL backups on local disk and 1 month of Full backups on Tape.
– Perform daily FULL Backups to local disk during the night.
– Copy the daily backups from local disk to Backup Server or Tape.
– At all times, store atleast 3 days of FULL backups on local disk and 1 month of Full backups on Tape.
Example2:
If the database is large in size(100 GB+) and is very critical and data loss of 10 minutes is only acceptable, then below would be the backup plan
If the database is large in size(100 GB+) and is very critical and data loss of 10 minutes is only acceptable, then below would be the backup plan
– Set the database to FULL recover model.
– Perform weekly FULL Backups to local disk in the night.
– Perform Daily Differential Backup once in a day, except for the day when we are performing the FULL backup.
– Perform Transaction Log backup every 10 minutes.
– Copy the weekly backups from local disk to Backup Server or Tape.
– Copy the daily differential backups from local disk to Backup Server or Tape.
– Copy the transaction log backups from local disk to Backup Server or Tape.
– At all times, store atleast 1 FULL backup on local disk and 1 month of Full backups on Tape.
– At all times, store atleast recent 3 differential backups on local disk and 1 month of differential backups on Tape.
– At all times, store atleast 3 days worth of transaction log backups on local disk and 1 month of transaction log backups on Tape.
– Perform weekly FULL Backups to local disk in the night.
– Perform Daily Differential Backup once in a day, except for the day when we are performing the FULL backup.
– Perform Transaction Log backup every 10 minutes.
– Copy the weekly backups from local disk to Backup Server or Tape.
– Copy the daily differential backups from local disk to Backup Server or Tape.
– Copy the transaction log backups from local disk to Backup Server or Tape.
– At all times, store atleast 1 FULL backup on local disk and 1 month of Full backups on Tape.
– At all times, store atleast recent 3 differential backups on local disk and 1 month of differential backups on Tape.
– At all times, store atleast 3 days worth of transaction log backups on local disk and 1 month of transaction log backups on Tape.
How do you recover a database which was crashed or corrupted using database backups?
This is most important questions and is asked almost in every interview. Failing to answer this question may reduce your chances of getting the job.
This is most important questions and is asked almost in every interview. Failing to answer this question may reduce your chances of getting the job.
Normally, you will be give a backup scenario and will be asked on how you will you recover the database using the available backups. Below is the scenario.
Scenario: There is a backup strategy in place where you have weekly FULL database backup which is performed every Sunday at 12:00 AM. There are daily Differential backup which is performed at 12:00 AM, except on Sundays. There are transaction log backups which are performed every 1 hour. Database crashed on Tuesday at 06:10 AM, now you need to recover as much data as possible, how will you recover the data?
Answer: First, we need to restore FULL database backup which was performed on recent Sunday at 12:00 AM. This needs to be restored with NoRecovery option. Next restore latest differential backup which was performed on Tuesday at 12:00 AM with NoRecovery option. Restore all transaction log backups in sequence taken from Tuesday 12:00 AM till 05:00 AM with NoRecovery option and finally restore the last transaction log file taken at 06:00 AM with Recovery option. This should now bring the database online with all the data till Tuesday 06:00 AM. There will be 10 minutes worth loss of data which was stored between 06:00 AM to 06:10 AM.
How will database recovery model impact database backups?
Recovery model basically tells SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models in SQL Server which are SIMPLE, DIFFERENTIAL and FULL.
Recovery model basically tells SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models in SQL Server which are SIMPLE, DIFFERENTIAL and FULL.
– When a database is set to SIMPLE recovery model, only FULL and DIFFERENTIAL backups are allowed. Transaction Log backups cannot be performed.
– Under BULK-LOGGED or FULL recovery mode, all types of backups are allowed.
– Under BULK-LOGGED or FULL recovery mode, all types of backups are allowed.
Can we perform a SQL Server compressed backup?
Starting SQL Server 2008, we can perform a compressed backups. All types of backups can be performed with compressed option. Prior to SQL Server 2008, backups may need to be compressed using third party tools and but need to regularly test to make sure that the backups are be uncompressed and restored successfully.
Starting SQL Server 2008, we can perform a compressed backups. All types of backups can be performed with compressed option. Prior to SQL Server 2008, backups may need to be compressed using third party tools and but need to regularly test to make sure that the backups are be uncompressed and restored successfully.
How can you automate performing database backups in SQL Server?
We can created maintenance plans and use backup tasks option and schedule the maintenance plan to perform backups automatically. We can also create SQL Server jobs and specify the backups commands or create a Stored procedure to perform backups of databases on certain conditions and create jobs and add job step to to run the stored procedure and then schedule the job. Also, there are many third party tools available to automate performing database backup and restores.
We can created maintenance plans and use backup tasks option and schedule the maintenance plan to perform backups automatically. We can also create SQL Server jobs and specify the backups commands or create a Stored procedure to perform backups of databases on certain conditions and create jobs and add job step to to run the stored procedure and then schedule the job. Also, there are many third party tools available to automate performing database backup and restores.
Recovering the data base
ReplyDeleteAt Genex DBS data loss is only temporary and we prove it every day with the highest data recovery success rate in the industry. Our volume of recovered data speaks for itself.
For over 17 years, Data Storage Solutions has performed professional data recovery on every kind of storage device including Desktop hard drive data recovery, Laptop hard drives data recovery, External / USB hard disc Data Recovery / Hard Disc Recovery, Server and RAID arrays data recovery, NAS, SAN, DAS, SSD, Encryption storage devices data recovery, Database & File Recovery, CCTV Data Recovery, Flash Card Data Recovery. and enterprise-level devices like RAIDs. We have proprietary tools to handle every kind of data loss situation on any server including physical and mechanical failure, backup failure, water and fire damage, data corruption, file deletion, head crash, system failure, and more. We conduct all our data recoveries using software and solutions that won’t further damage your device.Genexdbs Database