|
|
 |
|
|
Sql Server Backup Types by Adam Sutro
|
|
|
Sql Server Backup Types |
|
|
|
|
|
Computers,Science & Technology,Education
|
|
|
Backup is used to restore our database from any type of failure such failure can be of system crash or can be from SQL Server fails etc. In sql there are mainly four types of backup each have some limitations and some features. Here are the names of different backups Full Backup, Differential Backup, Transaction Log Architecture Backup and last one is File and File-groups Backup. Now i will explain all of backup type in below paragraph. First of all i will take Full database backup. It is the most common type of SQL Server backup.In full database backup we will take backup of whole database. Full Database Backups includes part of transaction log so we can recovered all the data from backup. Now we will talk about some good features of full backup. First one is that full backups results in a faster restore. It is very good where data is small. Now comes to some of its limitations We cannot use restore our backup in its earlier version for example we cannot store backup of sql2008 in sql2005. Next one is full database require lot of space as compare to other backup. Another drawback of full database backup is whenever we take a backup operation a entry is taken place in error log and in system event log. So this will increase the size of error logs.This allows for the simplest form of database restoration, since all of the contents are contained in one backup. Second type of backup is Differential Backup as the name suggest here we will take a backups of files that have done changed after the last backup done. And list of files changed since the last back up is recorded in a catalogue file with .bkc extension. If our database is in simple recovery model we can use full and differential backups. If database is in Bulk Logged recovery model we can use differential backups to eliminate the number of transaction logs. Now comes to its advantages first one is it will take less space as compare to full backup. It will also reduce the time to take backup as we have to take less backup then the full backup. Another very good benefit if differential is the restoring of backup is faster because storage requirement is very less. And third backup type is Transaction Log Architecture. As the name suggest transaction log all the traction and all the database modifications done by each transaction are stored and restore is done through this transaction logs. Now i will come to benefits of Transaction Log Architecture Backup. First one is transaction log backup allows us to backup the active part of the transaction log. So after you issue a Full or Differential backup the transaction log backup will have any transactions that were created after those other backups completed. Another good feature of this backup is that we can use the space which is used by transaction log to other process once we issued backup. We can recover individual transaction in case of failures. We can recover incomplete transaction with this backup. Now comes to some limitation of this backup is that when we not transaction log it will continue to grow more which take more space. Now last which i have define here is File and File-groups Backup. We got almost idea from the name of backup. In this backup we will get backup done from individual files or from file-groups. We can use this types of backup where database is too big and we does not have time to take full backup. File and file groups can be restored from a full database backup set. Now comes to benefit of this backup it allows a quicker recovery because only the file and the file groups that are damaged are restored first. To know more about Sql Server Interview Questions, dot net question and sql database backup visit interviewqsn.com
Related Articles -
dot net questions, sql server questions, dot net faq, mobile device programming,
|
Rate This Article  |
|
|
|
|
|
Do you Agree or Disagree? Have a Comment? POST IT!
| Reader Opinions |
|
|
|
|
|
|
|
|
|
| Author Login |
|
|
 |
Advertiser Login
ADVERTISE HERE NOW!
Limited Time $60 Offer!
90 Days-1.5 Million Views
|
|
GENE MYERS
Author of four books and two screenplays; frequent magazine contributor. I have four other books "in...more
|
|
|
|
|
TIM FAY
After 60-plus years of living, I am just trying to pass down some of the information that I have lea...more
|
|
|
|
|
LAURA JEEVES
At LeadGenerators, we specialise in content-led Online Marketing Strategies for our clients in the t...more
|
|
|
|
|
ALEX BELSEY
I am the editor of QUAY Magazine, a B2B publication based in the South West of the UK. I am also the...more
|
|
|
|
|
SUSAN FRIESEN
Located in the lower mainland of B.C., Susan Friesen is a visionary brand strategist, entrepreneur, ...more
|
|
|
|
|
STEPHEN BYE
Steve Bye is currently a fiction writer, who published his first novel, ‘Looking Forward Through the...more
|
|
|
|
|
SHALINI MITTAL
A postgraduate in Fashion Technology. Shalini is a writer at heart! Writing for her is an expression...more
|
|
|
|
|
ADRIAN JOELE
I have been involved in nutrition and weight management for over 12 years and I like to share my kn...more
|
|
|
|
|
JAMES KENNY
James is a Research Enthusiast that focuses on the understanding of how things work and can be impro...more
|
|
|
|
