Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
 Posted - 2013-11-26 : 04:02:10
|
| 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
4 Posts |
Posted - 2013-11-26 : 06:43:21
|
| There are 3 types of Recovery model in SQL Server.1. Simple2. Full3. Bulk-loggedPossible Backups for recovery model:Simple recovery model-Full-DifferentialFull Recovery model-Full-Differential-Transactional logBulk logged Recovery model-Full-Differential-TransactionChoosing 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 modelIf 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 possible2. 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).aspxBulk logged - http://technet.microsoft.com/en-us/library/ms190692(v=sql.105).aspxSimple - http://technet.microsoft.com/en-us/library/ms191164(v=sql.105).aspxI hope the above information will help you.Regards,Dhamu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-26 : 06:43:23
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-11-26 : 23:28:17
|
| 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-27 : 04:56:39
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-11-27 : 08:43:46
|
| 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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-03 : 10:48:29
|
| 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:422. 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. |
|
|
|
|
|
|
|
|
Backups & Restore