| Author |
Topic |
|
Mary Muller
Starting Member
5 Posts |
 Posted - 2007-09-11 : 12:18:35
|
| The consultant hired as our Remote DBA for SQL Server 2005 recently changed the recovery model on our user database from Full to Simple. He explained this was necessary because the transaction log was growing very large and backing up the database when the model was set to Full was not shrinking it. Now, the master database and the msdb database are backed up daily along with our user database.When we need to restore our user database, do we need to restore the master database and the msdb database too? What order should the databases be restored in? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-11 : 12:43:33
|
| Nope - the user databases are self contained.The consultant was correct in that you don't need full recovery (in fact shouldn't) if you aren't backing up tr logs.You should decide whether you need tr log backups though - at the moment you can restore the database to the last recoverable full backup. With tr log backups you can restore to the last tr log backup (and maybe to the point of failure or any point in time) and also have a chance of recovering past a corrupt full backup if you have all the logs.Also consider differential backups.The consultant probably went through this with you when he set up the procedures.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Mary Muller
Starting Member
5 Posts |
Posted - 2007-09-11 : 13:30:49
|
Thanks for your advice. That's a big help.Unfortunately, the consultant did not discuss changing the recovery model on the user database. I discovered it. Initially, the recovery model was set to Full and we had tr log backups. But, the tr log grew too large (20+ Gig) and the consultant was not able to shrink it down. That's why he set the recovery model to Simple. Any ideas regarding restricting the size/growth of the transaction log using the Full recovery model? Thanks.  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-11 : 15:37:26
|
| "That's why he set the recovery model to Simple."Good grief!1) Are you happy to be able to recover ONLY to a Full Backup? (How often is that made, once a day?)If not you need Tlog backups. Note also that TLog backups MAY enable you to recover with zero data loss after a system failure (e.g. your Data and Tlogs are on different Drive Spindles; the data drive gets hosed; but you are still able to make a final TLog backup. Now you can restore from a known-good Full backup, and then all Tlog backups, in turn, after that. I have done this enough times to know it is a life saver!2) If Tlog grows too large:a) Increase the frequency of backup (anything less than every 10 minutes is pointless - you have the opportunity to backup frequently and minimise data loss, so doing backups infrequently would be self defeatingb) Review when the TLog (xxx.LDF) file grows. its most likely in response to some identifiable activity, e.g. Database Maintenance - which can be a heavy user of the Log file. If that's the case there are ways of mitigating the problem.If you have problems trying to clear the log ask here. If your boss wants to get the consultant back ask for "10 good reasons not to get the consultant back" here too!!"Now, the master database and the msdb database are backed up daily along with our user database"How quaint! <Sorry!> Its easy to set up separate maintenance routines for the System and Non-system databases, if that is indeed what you need."When we need to restore our user database, do we need to restore the master database and the msdb database too?"No. Although if they are corrupted, or you had an accidental deletion etc., then you would want to restore them."What order should the databases be restored in?"If your User Databases are self-contained then you only need to restore a single database - the one that is corrupted / there was an accidental deletion, human error, etc.However, IF your databases rely on each other (e.g. you have one database for SALES and another for PURCHASES) then clearly they will only make sense if restored to a specific point-in-time, but this type of database-inter-dependency is very rare IME. You can ONLY do restore these type of inter-dependant-databases to a synchronised point in time if they are in FULL recovery model.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-11 : 23:03:37
|
| Don't shrink db files if you have enough disk space. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 01:57:54
|
| "backing up the database when the model was set to Full was not shrinking it"I should have picked up on this earlier.Backup does NOT shrink the file, it just makes the space available for re-use.So the Tlog file stays allocated at the maximum required size, and is re-used as necessary. If it becomes full it is extended.Therefore you should not shrink it because it takes time & resource to regrow it again.however, if you do some exceptional delete, or had a lousy backup policy, which allowed the Tlog file to grow exceptionally large, then a one-time shrink to get it back to "normal" size would be sensible.See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Database%20Shrinking,Shrinking,ShrinkKristen |
|
|
|
Mary Muller
Starting Member
5 Posts |
Posted - 2007-09-12 : 13:00:44
|
| This database is only used by a 3rd party, proprietary software application. It can only be updated from our in-house systems by exporting the desired data, doing massive deletes in several tables and rebuilding these tables by inserting the export data. This is done on a daily basis and is the main reason for the extremely large size of the transaction log file.In a recent email, the consultant explained that the backup (under Full recovery model) was NOT clearing out the transaction log file the way it is supposed to. What might cause this problem? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 14:09:52
|
| You need to do both a FULL backup and a TRANSACTIOn backup. If you were only doing the Full backup that would not clear the transaction backup.Might that be it?Kristen |
|
|
|
Mary Muller
Starting Member
5 Posts |
Posted - 2007-09-12 : 15:59:19
|
| That may be it. The last Tlog backup is from 7/26/2007. On 8/06/2007, I notified the consultant that the Transaction Log Backup job had been continuously failing. However, the db backup job appeared to be running properly.The consultant wants to keep the recovery model set to Simple. His objectives are to backup the db daily and reduce the size of the Tlog to something manageable. However, he has agreed to do a complete backup of the database twice each day instead of only once. Since he was not hired by the IT Department, this is the "solution" which will be implemented.Kristen, thank you very much for your suggestions. You taught me a lot! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-09-12 : 20:11:37
|
| Hmmm, have you looked into the experience of the consultant?Sounds like you are heading to a solution based on the competence of the people involved rather than what the system requires.Shrinking the data and log files is a fairly trivial matter - building a system that minimises the size of those file isn't so easy.Don't get into a situation where you are shrinking files daily (or weekly) that will be bad for your system.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2007-09-12 : 20:20:42
|
| Is the system of record or is the data imported from another system and this system is used just for reporting / data warehousing?If you system of record is properly backed up and the system your working with is only updated once daily then keeping the database in simple mode and having a full backup is appropriate. No reason to tlog backup a system that only updates once daily and is not the system of record. A full backup right after a successfull update is sufficient.The master database should be backed up it contains information about system configuration, security, and other system objects.The msdb contains information about packages and jobs.Both these should be backed up because changes are made to security, packages, jobs, and other system objects.PS: If anyone is interested in a SQL Job in Connecticut with excellent pay send me your resume to ValterBorges@msn.com. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 05:22:44
|
| "he has agreed to do a complete backup of the database twice each day instead of only once"Is there data in the database OTHER than the imported data?As ValterBorges said: for a system that imports data from somewhere else a Full backup just after the import is sufficient.You could that the view that a full backup after any other change is all that is required - i.e. the most recent backup you need is from whenever the database was last changed, current data will be obtained from the "other system". Most people avoid this solution though because of the risk of forgetting to make the backup when something changes!If only SOME of the data comes from the other system then:A TLog backup will be huge, so SIMPLE Recovery Model may be the answer. However, if the Other Data being entered is critical, and you need to be able to recover without much data loss, you still need FULL Recovery Model and a TLog backup every, say, 10 minutes.This could be achieved by NOT deleted and reimporting all the data each time. Instead, import the data into another database (kept in Simple Recovery model and only a daily Full backup), and then update the main database by Delta - i.e. Delete records which are now stale, Update records ONLY if they have changes, Insert new records. Assuming that MOST of the data does NOT change every day? then this would dramatically reduce the size of the TLog backup. (Bit more complex to set up through)."do a complete backup of the database twice each day instead of only once"Hmmmm. Again, if the data is not changing there is no point, if it is changing then not much use either! Although you now have an average 2 hour / mac 4 hour data loss, rather than average 4 hours / max 8 hour, if you need to restore (assuming data changed only during 8 working hours each day).I would have thought that a Differential backup would be better (assuming the database is in Simple Recovery model, rather than you being able to use Full Recovery Model and thus make TLog backups). That will only back up the parts of the database that have changed, which will a) take less time and b) use a LOT less disk space [provided a FULL backup is made AFTER Data import and BEFORE Differential backup!]. The size of Differential backup files might be small enough that you could consider making them, say, every hour - just average data loss only 30 minutes / max 1 hour.How much data is added OTHER than the Daily Data Import?Kristen |
|
|
|
Mary Muller
Starting Member
5 Posts |
Posted - 2007-09-14 : 14:04:30
|
| This database is used by our Legal Department to track all Legal cases and is updated via a 3rd party, proprietay software application. There is data in the database OTHER than the imported data. The imported data consists of personal information relating to our Members. We have been advised this data needs to reside in the database. Modifying the underlying queries used by the application to display this data from our in-house systems is not an option because the 3rd party software application is proprietary.There may be other (better) ways of updating the database from the exported data. But, we do not have access to the source code of the application used to update this database. It is understood that some data will probably have to be re-entered/re-created if a data restore is needed. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-14 : 21:43:03
|
| Well I reckon your best bet would be:Toggle database to SIMPLE Recovery modelImport data (as currently)Toggle database to FULL Recovery modelMake FULL BackupResume Tlog backups every 10 - 15 minutesJust setting Tlog backups every 10 - 15 minutes should be fine. Whilst the database is in SIMPLE Recovery Model they will fail.Assuming that your data import is in the middle of the night, i.e. when the database is not being used by the users (i.e. its not 24/7) then a failure at that moment in time won't matter - the last TLog backup from, say, 5:30pm will contain the last work done by the users, and you could recover therefore up to the last Tlog backup before the import started.Once the import has started and the Full backup then completes you will again be able to recover to the latest Tlog backup - i.e. about 10 minutes loss maximum.Personally I think it would be much better to have a TLog backup system in place, limiting the amount of possible data loss, rather than relying on all users being able to recreate a data loss of potentially 24 hours or so!Just to clarify:"It can only be updated from our in-house systems by exporting the desired data, doing massive deletes in several tables and rebuilding these tables by inserting the export data."Is this a process that you control, or does the import happen within the 3rd party application?Because if you control it I think you might be able to reduce it so that the logging impact is reduced - possible to a level where you can just continue to make TLog backups 24/7 and not muck about with the recovery model etc.Kristen |
|
|
|
|
Do System Databases Need to be Restored?