SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Backups & Restore
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/26/2013 :  04:02:10  Show Profile  Reply with Quote
Can any one tell me the exact scenarios where and which type of backups & recovery model will be useful...


I need clear description on recovery models & backups? any real-time scenarios

--
Chandu

mdn143
Starting Member

India
4 Posts

Posted - 11/26/2013 :  06:43:21  Show Profile  Reply with Quote
There are 3 types of Recovery model in SQL Server.
1. Simple
2. Full
3. Bulk-logged

Possible Backups for recovery model:
Simple recovery model
-Full
-Differential
Full Recovery model
-Full
-Differential
-Transactional log
Bulk logged Recovery model
-Full
-Differential
-Transaction

Choosing right Recovery model:
If your database is of highly critical and you dont want to lose any data incase of disaster, then you must choose Full recovery model. Point in time recovery is possible only in this model (Data can be recovered till the moment database crashed). Most of the production databases are configured with Full recovery model

If your database is of highly critical and also heavy operations like SELECT INTO, CREATE INDEX, BULK INSERT.,etc are happening, then you can use Bulk-logged recovery model. It is similar to Full recovery model except in 2 things.
1. Point in time recovery is not possible
2. Transactional log will be utilized very minimally as it will log only header information of bulk operations.

If your database is not so critical and no frequent update happens, you can choose SIMPLE recovery model. Point in time recovery is not possible in this model. We can only recover till last full/diff database backup.

The following link will be very useful for knowing about backup operation that happens in each recovery model.
Full - http://technet.microsoft.com/en-us/library/ms190217(v=sql.105).aspx
Bulk logged - http://technet.microsoft.com/en-us/library/ms190692(v=sql.105).aspx
Simple - http://technet.microsoft.com/en-us/library/ms191164(v=sql.105).aspx

I hope the above information will help you.

Regards,
Dhamu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/26/2013 :  06:43:23  Show Profile  Reply with Quote
recovery model choice depends on how you want to restore database in case of failure. Normally you choose the recovery model based on how critical your system is. What the level to which you want data to be recovered (point in time restore) etc. Simple recovery will cause log file to be automatically truncated once it reaches checkpoint. Buk Logged means all details on transactions will be retained on log file except some Bulk logged operations. Full revoery will cause every info to be retained in log file. So for bulk logged and full logged you would need a good backup strategy to prevent log file being filling up disk space.
Backup types available in SQL Server are Full, differential and log backup. Full will have entore info on database and in usual scenarios it would be taken once (or max twice) per week for a medium db. then for every day you'll take diferential backup at end of the day which will contain all changes happened in db since last full backup. finally you'll have log backups running all through the day as per predefined schedule which will have details stored since last differential or log backup.
So any time failure happens you'll just need to apply latest full backup follwed by latest differential backup and keep on applying log backups from that until last avialble to bring your db to stage just before the failure happened. This is known as point in time restoration.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/26/2013 :  23:28:17  Show Profile  Reply with Quote
Both of you thank u so much..
Can you please suggest any real-time practice methodologies for recovery models ?

Any links that explains the practice scenarios to restore DBs using recovery models?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  04:56:39  Show Profile  Reply with Quote
quote:
Originally posted by bandi

Both of you thank u so much..
Can you please suggest any real-time practice methodologies for recovery models ?

Any links that explains the practice scenarios to restore DBs using recovery models?

--
Chandu


What do you mean by practice scenarios?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/27/2013 :  08:43:46  Show Profile  Reply with Quote
For practice purpose i suppose to do crash SQL Server or log-file corruption and then applying the solution/work around to resolve the issues and bringing database to ONLINE mode from SUSPECT/OFFLINE modes...

I'm new to this administration... Next week onwards i should work on agent jobs, back ups & restores and resolving corruption issues (or providing work around fo rjob failures )

Thats why i would like to know the proper workarounds for possible issues

--
Chandu
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/03/2013 :  10:48:29  Show Profile  Reply with Quote
If you want to do a fire-drill personally I would want to be able to do these things:

1. Restore to point-in-time. You have FULL backup from 01:00 today, and log backups taken every hour (let's say).

Experiment with restoring the database to, say, 13:42

2. Data file is corrupted. You could perhaps take the database offline, manually edit the database file with notepad or somesuch and SAVE it. You then need to see if you can make a TAIL LOG BACKUP. Make sure that you make some additional Inserts / Updates before you take the database offline (so that they are in the LOG file) AND make sure that a Log Backup has NOT happened (e.g. as a scheduled task) - i.e. those additional transactions are NOT in a backup.

Then make the TAIL LOG BACKUP.

Restore the database including all Log Backups but NOT including the Tail Backup. Check that your additional Update/Insert is NOT present.

Repeat the restore, this time INCLUDING the Tail Log Backup. Is your Update/Insert present now?

3. Corrupted LOG file.

As before make Update/Insert and make sure that there has not been a log backup to include that.

Take database offline and corrupt the LOG file.

Attempt to create a new log file and see if you can backup and restore the database and whether that includes the additional transaction.

4. Check what will happen if any one of your physical disks fails catastrophically. Don't rely on RAID to save you. So your Data and Log files must be on separate media, and either you backups are on separate media or are cross shared (Log backups on Data volume, Full backups on Log volume). If your application is mission critical then separate disks for TEMPDB, Data files, Log files and Backup files. Also copy you Log Backups to a.n.other server in real time - don't wait for a once-a-day Tape backup (ROBOCOPY is good for that job)

5. Check that your Database Validation (e.g. using CHECKDB) will bring to light any database corruption in a timely fashion so that you can cure the problem (e.g. restoring from backups, but ensuring that you have zero data loss).

6. Check that your backup process is making test restores. e.g. once a week restore the backup to another machine, and validate that (using CHECKDB) to ensure that the restored copy on other machine is safe.

7. Check that you have something in place that tells you if a backup fails. I err on the side of caution, so my system will also tell me if the system that tells me that there is a backup failure ALSO fails - e.g. Email does not get sent.

I expect there are Blogs that discuss this, and have better thought through procedures and suggestions, but those are the ones that occur to me just now.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000