| Author |
Topic |
|
Sharon911
Starting Member
46 Posts |
 Posted - 2010-04-08 : 14:12:00
|
| We just started to deploy sharepoint system, my questions are:1. What is the best practice for doing backup for the system?2. Normally do we backup SQL databases separately? If so, what databases need to be backed up(after installation there are bunch of databases created)?Thanks,Sharon |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-04-08 : 14:46:28
|
| Thanks for the quick reply. Should I backup all databases to one file, or create one file for each database? Which option is better for managing the backups? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-04-08 : 15:17:52
|
| Great, I have also created their own directory for each database.Will test the restore later, and may have more questions here :)Thanks very much, I am new to SQL world and found this forum is very helpful..Sharon |
|
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-04-08 : 15:39:08
|
| One more question regarding log backup,I don't see there is an option to truncate the log after backup when using SSMS maintenance plan to schedule a backup. Is the log being automatically truncated, or we have to make t-sql scripts to truncate the log? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-04-09 : 10:02:26
|
| If backup the log without truncate option, would the log grow huge in the future? Would the entire log be backed up every time, or just part of the log which is not backed up before?Thanks,Sharon |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-09 : 10:10:10
|
quote:
Originally posted by tkizer
1. Full backups daily, tlog backups every 15 minutes, and perhaps diff backup 12 hours after full
Tara, any reason to do a full backup every day, as opposed to say, a full backup once a week and a delta backup every day (or every 12 hours, etc)? On really large databases, and where backups are kept for several weeks, a full backup every day is going to require a huge amount of storage.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-04-09 : 11:39:12
|
| My SQL databases are small so there is no problem to do daily full backup, and I think this will simplify the restore process as well? Especially for newbie like me.I agree for large databases, weekly full backup and daily differential backup would much help to save the storage and time, this is our Oracle databases backup strategies. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-09 : 19:21:32
|
quote:
Originally posted by Sharon911
If backup the log without truncate option, would the log grow huge in the future? Would the entire log be backed up every time, or just part of the log which is not backed up before?Thanks,Sharon
If you backup the transaction log frequently, then the file size is controlled since the backed up portion of the log gets truncated after the backup completes. Any uncommitted transactions in the log at the time of the backup will not get backed up until the transaction completes. With Tlog backups you get the deltas from the last tlog.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-09 : 19:23:57
|
quote:
Originally posted by DBA in the making
quote:
Originally posted by tkizer
1. Full backups daily, tlog backups every 15 minutes, and perhaps diff backup 12 hours after full
Tara, any reason to do a full backup every day, as opposed to say, a full backup once a week and a delta backup every day (or every 12 hours, etc)? On really large databases, and where backups are kept for several weeks, a full backup every day is going to require a huge amount of storage.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.
We don't worry too much about how much disk space we use for backups as backups are so important. In the event of an emergency restore, we need that process to be as fast as possible so a daily full backup helps with fast restores to a point in time.We even do daily full backups on our system that has a 1TB database, but we do use compression (SQL Litespeed on SQL Server 2005). Once we move to SQL Server 2008, we'll switch to the native compression.At my company, we are only able to retain 2-5 days of backups due to contractual reasons with our customers.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-04-19 : 10:38:21
|
| One more question,Do you backup the files to disk, and ship them to tape periodically, or do you backup to tape directly?Sharon |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-19 : 11:25:48
|
quote:
Originally posted by DBA in the makingTara, any reason to do a full backup every day, as opposed to say, a full backup once a week and a delta backup every day (or every 12 hours, etc)? On really large databases, and where backups are kept for several weeks, a full backup every day is going to require a huge amount of storage.
We don't have enough disk space to hold FULL backups online for very long, and restore from tape requires the Tape people to go-get the tape!, hence we want maximum recoverable interval before we even have to ask them go-get-tape ...so, although our databases are not "huge", we have Weekly Full backup and Daily differential backup.Our differential backup gets to within 50% the size of the full backup by the end of the week - so we aren't saving a lot.Of course this depends on what the "churn" of data is - and a database full of images  would have smaller filesize ratio for Full : Diff ... but just thought I'd mention it."Do you backup the files to disk, and ship them to tape periodically, or do you backup to tape directly?"Backup to Disk and then let the file-by-file tape system copy them to tape from there.All too frequently there are questions here from people having difficult getting recovery to work directly from Tape-to-Database.If you backup to Disk first then there is a fair chance that the Backup file you need to restore from is right there, on disk, saving time (usually at a moment that is critical)Then you just have to rely on Tape media for more significant emergencies than just "I accidentally deleted all my records"By the by, set up the Tape software (and any anti-virus) to ignore the MDF / LDF (and NDF if you have them) files used by SQL itself for the databases (or the whole folder if you put them all in one place). They are most unlikely to be recoverable (rely on backup files for that) and the Tape Driver's actions trying to read / lock those files may mess with SQL itself. |
|
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-04-19 : 11:33:30
|
| If the tlog is scheduled to backup every 15 min, how often the tape backup is supposed to be scheduled to copy the files? I was thinking if the storage(both database and backups) fails, and some tlogs have not been copied to tape, how is the database being restored? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-19 : 11:56:55
|
| Most times (but don't rely on it) the BAK files are on the disk and readable, and can be used for recovery.Most (don't rely on that either!) of my restores are for user-error, not hardware failure. Of the rest they are mostly for corruption to the SQL working files caused by I/O failure or RAID5 being completely useless at power-fail-recovery (which I would swear blind it was supposed to solve ... ho!hum!)Furthermore, if MDF and LDF are on different drives, and better still the Backup files are on yet-another drive, then you have a good chance that hardware failure won't take all your options with it in one strike. If the server/building catches fire then your options are limited to the most recent tape backup - and maybe in those options a max 24 hours loss is tolerable.If you only have separate drives for MDF / LDF then consider putting their backups on the opposing spindle - to give you more options.If you just have one RAID array then you are somewhat more exposed. (Multiple spindles for MDF, LDF and Backup files is good for performance too, but you may not have that requirement yet)We copy all BAK files to A.N.Other machine on the LAN (in near-realtime after they are created) to give us a bit more chance of recovery (our servers are in some huge data centre which, theoretically, can't catch fire ...F.L.Ws.)If you can get your BACK files to tape more frequently that once-a-day that's great, but I suspect that would put you in a minority (for a "small shop").You will have to weight up the Risk for your particular circumstances though. |
|
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-05-12 : 14:18:59
|
| Back to an original question:Why is it recommended that tlogs are being backed up every 15 min? Does it depend on the system activities and SLA?What are the risks if it is being backup every 4 hours?Thanks very much.Sharon |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 14:27:50
|
| 15 minutes is a compromise between losing a lot of data (e.g. if backup is only once per 4 hours you stand to lose 3hrs 59min of data entry) and having a huge number of separate TLog backup files to restore (i.e. if backups are every minute or two) which, whilst not impossible, generally increases the amount of time it takes for DBA's to perform a disaster recovery - so there is a trade off between getting the system back, upright, quickly and data entry operators having to repeat data / the company being prepared to lose data.If your data is particularly critical you need a different solution - more frequent TLog backups may be acceptable, if not you need to consider warm, or hot, standby server. |
|
|
|
Sharon911
Starting Member
46 Posts |
Posted - 2010-05-12 : 14:51:09
|
| Do you copy the tlog backups every 15 min to nother server on LAN?Our backups and database are on the same storage,I was just thinking if the whole storage fails, how can we get all backups available. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 15:00:52
|
"Do you copy the tlog backups every 15 min to nother server on LAN?""Yes, that's part of our Backup Process - so as soon as the backupfile is created it is copied to A.N. Other server. (We don't attempt to get them offsite though, just on to a different machine, but getting them offsite is obviously something that should be contemplated, as par of disaster-recovery-planning - I figure that a total machine failure is Probability X%, whereas total building catastrophe is a much lower Y%, but its down to the Bosses to decide what level of risk they are comfortable with.]"Our backups and database are on the same storage,I was just thinking if the whole storage fails, how can we get all backups available."Indeed, and I would speculate that you are the same as many other organisations - except that you are considering the risk, whereas the other organisations will only discover it the first time they have a failure  Having a separate disk "spindle" for Backups is only any use if it also has a separate controller - and even then a RAM fault or somesuch could scramble both at the same time. Probably better to change the backup routine to "copy" to A.N.Other machine before you start to worry about a "different disk spindle" policy.Here's a different line of thought:If your primary SQL box "blew up" what would you do?If you have a machine that could "at a pinch" host the SQL databases then the best place to copy the backups is to that machine (if there is enough disk space). Then you have everything you need, on that machine, to make the Restore and from there get the database "upright" again. You would then have to change the Connection Strings of the APPs to tell them to connect to the failover-machine - so best to store that connection information centrally so it is easy to change in only one place. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 15:03:12
|
quote:
Originally posted by Kristen
"Do you copy the tlog backups every 15 min to nother server on LAN?""Yes, that's part of our Backup Process - so as soon as the backupfile is created it is copied to A.N. Other server. (We don't attempt to get them offsite though, just on to a different machine, but getting them offsite is obviously something that should be contemplated, as part of disaster-recovery-planning - I figure that a total machine failure is Probability X%, whereas total building catastrophe is a much lower Y%, but its down to the Bosses to decide what level of risk they are comfortable with.]"Our backups and database are on the same storage,I was just thinking if the whole storage fails, how can we get all backups available."Indeed, and I would speculate that you are the same as many other organisations - except that you are considering the risk, whereas the other organisations will only discover it the first time they have a failure Having a separate disk "spindle" for Backups is only any use if it also has a separate controller - and even then a RAM fault or somesuch could scramble both at the same time. Probably better to change the backup routine to "copy" to A.N.Other machine before you start to worry about a "different disk spindle" policy.Here's a different line of thought:If your primary SQL box "blew up" what would you do?If you have a machine that could "at a pinch" host the SQL databases then the best place to copy the backups is to that machine (if there is enough disk space). Then you have everything you need, on that machine, to make the Restore and from there get the database "upright" again. You would then have to change the Connection Strings of the APPs to tell them to connect to the failover-machine - so best to store that connection information centrally so it is easy to change in only one place.
|
|
|
|
Next Page
|
Sharepoint system vs SQL backup