| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2010-07-11 : 15:56:21
|
| Question, my original backup plan was to setup a FULL backup to run every friday @ 12 am. and then have another maintinance plan that runs a differential backup every other day of the week @ mid-night. does that sound like a good idea?does anyone have any better reccommendations on maybe what i should do?the databases i'll be backup up are OLAP type databases, they change once daily at the MOST. thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-11 : 16:43:33
|
| What are your recovery requirements? -being able to restore the data lost :)If you had to restore, how much data can you afford to lose? -like i said, it's low transaction, so a days worth is okay, anything more than a week would be really bad.If you had to restore, how long could you be down? -couple hours would be ideal, no more than a dayAre you using native backups or compressed backups? -i'd like to use compressed, but i do have a crap load of disk space, so i dont have to.What version of SQL Server are you using? -2008What recovery model are you using? -FULLHow big is the database? -1.3TB |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-11 : 16:51:02
|
| If you are using full recovery model, then you need to add transaction log backups. Otherwise you are going to fill up your tlog and cause downtime. A good backup plan is this:full backup every daydifferential backup 12 hours after full backuptlog backups every 15 minutesCompression saves greatly on backup and restore time, so you shouldn't just use native because you have lots of disk space. Are you running Enterprise Edition? If so, I'd highly recommend switching to native compression.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-11 : 17:47:07
|
| do you use the maintenance plans, or just tsql with sql agent?just kinda trying to get a grasp on how the pro's do it... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-11 : 21:59:42
|
| this looks fun, i'm TOTALLY gunna use it i think, do you just call the script with sql agent i assume? also, i got the following errors, should i worry about them? or is it something that needs fixing, if so, any reccommendations?The module 'isp_Backup' depends on the missing object 'master.dbo.xp_backup_database'. The module will still be created; however, it cannot run successfully until the object exists.The module 'isp_Backup' depends on the missing object 'master.dbo.sqlbackup'. The module will still be created; however, it cannot run successfully until the object exists.The module 'isp_Backup' depends on the missing object 'master.dbo.sqlbackup'. The module will still be created; however, it cannot run successfully until the object exists.The module 'isp_Backup' depends on the missing object 'master.dbo.sqlbackup'. The module will still be created; however, it cannot run successfully until the object exists. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-11 : 23:26:38
|
| Yes we call it in SQL Agent. I've got 3-4 backup jobs on each SQL instance:1. Backup system databases (daily or weekly)2. Backup user databases (daily)3. Backup tlogs (every 15 minutes)4. Backup diffs (daily, 12 hours after full backup for user databases)It's #4 that I don't have on every system. Diffs help you with the recovery process as you potentially get to apply fewer tlogs, this means your recovery time is quicker. The messages that you got are warnings, and they are to be expected on systems that do not have Quest's SQL Litespeed or Red Gate's SQL Backup products installed. My isp_backup supports native backups, native compression backups (2008 only), Litespeed backups, or RG's backups. So these aren't errors, but rather SQL Server is letting you know the stored procedure is referring to objects that don't exist. Don't worry though as it'll still run successfully for you. You just can't pass LS or SB to the @bkpSwType input parameter.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-12 : 00:04:01
|
| last question i think... i'm sure you have gobs and gobs of disk space considering the qty of database servers you work with, what do you use for a backup solution?i'm using an iSCSI target w/ 6TB of disk space, so using your method i'll be running out of disk space after only about 2-3 backups... just trying to plan, i guess i thought 6TB was alot, now i'm thinking not so much :( |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-12 : 00:16:35
|
| OH, and one more... any ideas on how i can backup sqlexpress tables (from our website)? no sql-agent so i cant really schedule it... maybe a batch-script, sql-cmd and task scheduleer? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-07-12 : 03:28:26
|
| In the current location I'm working - they are using Tivoli Storage Manager - which manages the scheduling\backup configuration\reporting , moving data to disk pool . From the disk pool , it's then moved to tape - and moved off site. Unless you're already transfering the disk to another site, I'd recommend you move your data off siteJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-12 : 04:10:31
|
| We do FULL backup weekly and DIFF backup daily to cut down on the amount / size of backups. However, beware that the size of the DIFF backups can approach the FULL by the end of the week (you say you have low transaction count, so that may help, but index rebuilds touch lots of pages in the DB and then those pages are included in DIFF backup.)For FULL backups to be run less frequently you need to make sure your Index Rebuilds / Stats Updates run before your Full backup, not just afterwards!I'm surprised on a TB-sized database you are happy to lose a day's transactions (worst case). Our databases are much smaller than that and we couldn't afford to lose anything like that length of time (we'd never be able to recreated it). But I suppose "low volume transactions" is a relative term?Set up TLog backups every 15 minutes, no sense losing more than that time if you don't have to (recovery will depend on still having access to the disks, to get to the Tlog backups, and in a total machine failure you will only be as good as whatever backup copies you have OFF the machine, or in disaster-recovery whatever you have off-site). IME most times the recovery is for human error, OR the disks are still accessible and thus we can restore from the TLog backups, so we have been able to recover with zero data loss |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-12 : 12:50:20
|
quote:
Originally posted by albertkohl
last question i think... i'm sure you have gobs and gobs of disk space considering the qty of database servers you work with, what do you use for a backup solution?i'm using an iSCSI target w/ 6TB of disk space, so using your method i'll be running out of disk space after only about 2-3 backups... just trying to plan, i guess i thought 6TB was alot, now i'm thinking not so much :(
Are you using Enterprise edition? If so, you should use native compression. You will have drastically smaller backup files and will not have disk space issues.For our tape solution, we are using Netbackup.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-12 : 13:47:07
|
| all this information was great guys, i really appreciate it. after compressing the backups and everything it got down to 300GB, so i think i'll still have a good amount of disk space left.i'm excited to have full backups running, less stress for me to deal with.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-14 : 23:02:08
|
| quick question, to test a backup, is there anything i can do aside from just loading it into another, new database? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-07-14 : 23:21:39
|
quote:
Originally posted by albertkohl
quick question, to test a backup, is there anything i can do aside from just loading it into another, new database?
Your backups are no good - unless they can be restored. So, testing a restore on a regular basis is the best way to validate your backups.You can perform a RESTORE VERIFYONLY on the backup file - but, that really isn't good enough to validate your backup. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-15 : 00:01:15
|
| so just restore the data into another database for example?i'm assuming i shouldnt be testing my restores on my production databases right? |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2010-07-15 : 00:10:12
|
| also, i'm assuming that database backups wont really cause any kinda locking will they? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-15 : 02:15:17
|
"so just restore the data into another database for example?"And then do a DBCC CHECKDB on it  Quite handy actually, because the restored DB is identical to the original, so if you do this on the Restored DB you don't need to DBCC CHECKDB the original DB - which is good news if your DB is huge and DBCC takes all day ..."i'm assuming i shouldnt be testing my restores on my production databases right?"If you have disk space enough I don't suppose that matters too much, but my preference is a different machine as it tests that the backup is restoreable on different hardware, and gets the backup file OFF the main machine - an extra benefit."also, i'm assuming that database backups wont really cause any kinda locking will they?"Backups don't lock anything, but they obviously use some resources (CPU/memory/disk), but backup is designed to be low impact. |
|
|
|
Previous Page&nsp;
Next Page
|
Database Backup Differential/Full